This is a hyperthetical question.
If a web site is running slowly then we can create a web farm and spilt the
problem between multiple servers.
However if my database is running slowly, and given that its schema is well
designed and the application that is communicating with it is well designed,
what options do I have to speed it up? Does SQL Server have a concept of
a database farm? Can I throw another server at it?
I understand that I could peel off read-only data to another database but
what would happen if it was all read-write? eg maybe like a banks computer
system or a credit card processing authority?
I could throw more CPU, RAM, hard disk, etc at it but ultimately this only
goes so far.
What databases do the BIG database users use? eg AMEX? Walmart? etc. I
guess they are not on SQL Server?
Regards
Dave ADave
I'd turn on SQL Server Profiler to identify long running queries , stored
procedures and try to imrove them. How to improve, that is probably playing
with indexes and/or if you are in SQL Server 2005 let the 'problematic'
query run throu Tuning wizard to advise you.
> What databases do the BIG database users use? eg AMEX? Walmart? etc. I
> guess they are not on SQL Server?
SQL Server has no problem to keep TERA byte databases , the point is how
well the db is designed?
"Dave A" <dave@.sigmasolutionsdonotspamme.com.au> wrote in message
news:e5kphEFJIHA.1208@.TK2MSFTNGP03.phx.gbl...
> This is a hyperthetical question.
> If a web site is running slowly then we can create a web farm and spilt
> the problem between multiple servers.
> However if my database is running slowly, and given that its schema is
> well designed and the application that is communicating with it is well
> designed, what options do I have to speed it up? Does SQL Server have a
> concept of a database farm? Can I throw another server at it?
> I understand that I could peel off read-only data to another database but
> what would happen if it was all read-write? eg maybe like a banks computer
> system or a credit card processing authority?
> I could throw more CPU, RAM, hard disk, etc at it but ultimately this only
> goes so far.
> What databases do the BIG database users use? eg AMEX? Walmart? etc. I
> guess they are not on SQL Server?
> Regards
> Dave A
>|||Remember, this is a hyperthetical question. There is no need to run Profile
since there is no database to profile.
I have no doubt that SQL Server can handle multiterabyte datbases, my
problem is when there are a million conurrenct users updating and reading
that data, what options does an SQL Server designer have at thier disposal?
(Don't worry, I am an SQL Server zealot. I am just wondering what happens
when we push the limits and how far can it go.)
So, my question remains. Is there a concept of a farm of sql servers? - just
like there is a cluster of sql servers or a farm of web servers.
Regards
Dave A
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ec0KCYFJIHA.5468@.TK2MSFTNGP05.phx.gbl...
> Dave
> I'd turn on SQL Server Profiler to identify long running queries , stored
> procedures and try to imrove them. How to improve, that is probably
> playing with indexes and/or if you are in SQL Server 2005 let the
> 'problematic' query run throu Tuning wizard to advise you.
>> What databases do the BIG database users use? eg AMEX? Walmart? etc. I
>> guess they are not on SQL Server?
>
> SQL Server has no problem to keep TERA byte databases , the point is how
> well the db is designed?
>
>
> "Dave A" <dave@.sigmasolutionsdonotspamme.com.au> wrote in message
> news:e5kphEFJIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> This is a hyperthetical question.
>> If a web site is running slowly then we can create a web farm and spilt
>> the problem between multiple servers.
>> However if my database is running slowly, and given that its schema is
>> well designed and the application that is communicating with it is well
>> designed, what options do I have to speed it up? Does SQL Server have
>> a concept of a database farm? Can I throw another server at it?
>> I understand that I could peel off read-only data to another database but
>> what would happen if it was all read-write? eg maybe like a banks
>> computer system or a credit card processing authority?
>> I could throw more CPU, RAM, hard disk, etc at it but ultimately this
>> only goes so far.
>> What databases do the BIG database users use? eg AMEX? Walmart? etc. I
>> guess they are not on SQL Server?
>> Regards
>> Dave A
>|||When it comes to CPU and RAM, you can only scale up, not scale out. So
"throwing another server at it" will not help.
With the long standing trend of ever faster CPU's and the growing number
of cores per computer this can go a long way.
But even if SQL Server supported scaling out, this would only go "so
far" too. If you have one server and add an identical server, the
performance gain will never be 100%, it will be less. There is a limit
as to how much you can scale out, just as there is a limit to how much
you can scale up.
You should have a look at the TPC numbers show (see www.tpc.org). An
interesting chart is the Top 10 TPC-C by Performance. You will notice
that there is only one clustered configuration in this top 10, which
uses Oracle 10g. All others are nonclustered configuration are
nonclustered, including 4 other Oracle 10g configurations.
I therefore conclude that currently, there is no practical need for a
clustered SQL Server solution.
--
Gert-Jan
Dave A wrote:
> This is a hyperthetical question.
> If a web site is running slowly then we can create a web farm and spilt the
> problem between multiple servers.
> However if my database is running slowly, and given that its schema is well
> designed and the application that is communicating with it is well designed,
> what options do I have to speed it up? Does SQL Server have a concept of
> a database farm? Can I throw another server at it?
> I understand that I could peel off read-only data to another database but
> what would happen if it was all read-write? eg maybe like a banks computer
> system or a credit card processing authority?
> I could throw more CPU, RAM, hard disk, etc at it but ultimately this only
> goes so far.
> What databases do the BIG database users use? eg AMEX? Walmart? etc. I
> guess they are not on SQL Server?
> Regards
> Dave A|||On Sun, 11 Nov 2007 20:54:20 +1000, "Dave A"
<dave@.sigmasolutionsdonotspamme.com.au> wrote:
>This is a hyperthetical question.
>If a web site is running slowly then we can create a web farm and spilt the
>problem between multiple servers.
>However if my database is running slowly, and given that its schema is well
>designed and the application that is communicating with it is well designed,
>what options do I have to speed it up? Does SQL Server have a concept of
>a database farm? Can I throw another server at it?
No. Not automagically. You are of course free to put all the A's on
one server, etc, and replicate data between them. This is often
enough done, but it is a lot of work. SQL Server 2008 looks like it's
aimed at much bigger configs, but I'm not up on the details.
>I could throw more CPU, RAM, hard disk, etc at it but ultimately this only
>goes so far.
It goes pretty far, on a maxed-out Wintel machine, these days, with
SAN storage, etc. But increasingly, you actually have to know what
you're doing, config the hardware right, and be very careful of the
execution plans going sour on you. But do all that, and we're
probably running about 10^6 bigger and faster than SQL 6.5 on a 100mhz
server ten years ago! On a given hardware platform, for an average
transactional mix (whatever that might mean), or for a preplanned OLAP
environment, SQLServer is at least competitive with anyone.
>What databases do the BIG database users use? eg AMEX? Walmart? etc. I
>guess they are not on SQL Server?
Oracle is still your game if you want to throw hardware at the
problem. Teradata is another way to go on humongous databases and
ad-hoc queries. I suppose DB2 fits in there somewhere, but I don't
know quite where.
J.
Showing posts with label performance. Show all posts
Showing posts with label performance. Show all posts
Tuesday, March 27, 2012
General configuration question - maximum performance.
This is a hyperthetical question.
If a web site is running slowly then we can create a web farm and spilt the
problem between multiple servers.
However if my database is running slowly, and given that its schema is well
designed and the application that is communicating with it is well designed,
what options do I have to speed it up? Does SQL Server have a concept of
a database farm? Can I throw another server at it?
I understand that I could peel off read-only data to another database but
what would happen if it was all read-write? eg maybe like a banks computer
system or a credit card processing authority?
I could throw more CPU, RAM, hard disk, etc at it but ultimately this only
goes so far.
What databases do the BIG database users use? eg AMEX? Walmart? etc. I
guess they are not on SQL Server?
Regards
Dave ADave
I'd turn on SQL Server Profiler to identify long running queries , stored
procedures and try to imrove them. How to improve, that is probably playing
with indexes and/or if you are in SQL Server 2005 let the 'problematic'
query run throu Tuning wizard to advise you.
> What databases do the BIG database users use? eg AMEX? Walmart? etc. I
> guess they are not on SQL Server?
SQL Server has no problem to keep TERA byte databases , the point is how
well the db is designed?
"Dave A" <dave@.sigmasolutionsdonotspamme.com.au> wrote in message
news:e5kphEFJIHA.1208@.TK2MSFTNGP03.phx.gbl...
> This is a hyperthetical question.
> If a web site is running slowly then we can create a web farm and spilt
> the problem between multiple servers.
> However if my database is running slowly, and given that its schema is
> well designed and the application that is communicating with it is well
> designed, what options do I have to speed it up? Does SQL Server have a
> concept of a database farm? Can I throw another server at it?
> I understand that I could peel off read-only data to another database but
> what would happen if it was all read-write? eg maybe like a banks computer
> system or a credit card processing authority?
> I could throw more CPU, RAM, hard disk, etc at it but ultimately this only
> goes so far.
> What databases do the BIG database users use? eg AMEX? Walmart? etc. I
> guess they are not on SQL Server?
> Regards
> Dave A
>|||Remember, this is a hyperthetical question. There is no need to run Profile
since there is no database to profile.
I have no doubt that SQL Server can handle multiterabyte datbases, my
problem is when there are a million conurrenct users updating and reading
that data, what options does an SQL Server designer have at thier disposal?
(Don't worry, I am an SQL Server zealot. I am just wondering what happens
when we push the limits and how far can it go.)
So, my question remains. Is there a concept of a farm of sql servers? - just
like there is a cluster of sql servers or a farm of web servers.
Regards
Dave A
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ec0KCYFJIHA.5468@.TK2MSFTNGP05.phx.gbl...
> Dave
> I'd turn on SQL Server Profiler to identify long running queries , stored
> procedures and try to imrove them. How to improve, that is probably
> playing with indexes and/or if you are in SQL Server 2005 let the
> 'problematic' query run throu Tuning wizard to advise you.
>
>
> SQL Server has no problem to keep TERA byte databases , the point is how
> well the db is designed?
>
>
> "Dave A" <dave@.sigmasolutionsdonotspamme.com.au> wrote in message
> news:e5kphEFJIHA.1208@.TK2MSFTNGP03.phx.gbl...
>|||When it comes to CPU and RAM, you can only scale up, not scale out. So
"throwing another server at it" will not help.
With the long standing trend of ever faster CPU's and the growing number
of cores per computer this can go a long way.
But even if SQL Server supported scaling out, this would only go "so
far" too. If you have one server and add an identical server, the
performance gain will never be 100%, it will be less. There is a limit
as to how much you can scale out, just as there is a limit to how much
you can scale up.
You should have a look at the TPC numbers show (see www.tpc.org). An
interesting chart is the Top 10 TPC-C by Performance. You will notice
that there is only one clustered configuration in this top 10, which
uses Oracle 10g. All others are nonclustered configuration are
nonclustered, including 4 other Oracle 10g configurations.
I therefore conclude that currently, there is no practical need for a
clustered SQL Server solution.
Gert-Jan
Dave A wrote:
> This is a hyperthetical question.
> If a web site is running slowly then we can create a web farm and spilt th
e
> problem between multiple servers.
> However if my database is running slowly, and given that its schema is wel
l
> designed and the application that is communicating with it is well designe
d,
> what options do I have to speed it up? Does SQL Server have a concept o
f
> a database farm? Can I throw another server at it?
> I understand that I could peel off read-only data to another database but
> what would happen if it was all read-write? eg maybe like a banks computer
> system or a credit card processing authority?
> I could throw more CPU, RAM, hard disk, etc at it but ultimately this only
> goes so far.
> What databases do the BIG database users use? eg AMEX? Walmart? etc. I
> guess they are not on SQL Server?
> Regards
> Dave A|||On Sun, 11 Nov 2007 20:54:20 +1000, "Dave A"
<dave@.sigmasolutionsdonotspamme.com.au> wrote:
>This is a hyperthetical question.
>If a web site is running slowly then we can create a web farm and spilt the
>problem between multiple servers.
>However if my database is running slowly, and given that its schema is well
>designed and the application that is communicating with it is well designed
,
>what options do I have to speed it up? Does SQL Server have a concept of
>a database farm? Can I throw another server at it?
No. Not automagically. You are of course free to put all the A's on
one server, etc, and replicate data between them. This is often
enough done, but it is a lot of work. SQL Server 2008 looks like it's
aimed at much bigger configs, but I'm not up on the details.
>I could throw more CPU, RAM, hard disk, etc at it but ultimately this only
>goes so far.
It goes pretty far, on a maxed-out Wintel machine, these days, with
SAN storage, etc. But increasingly, you actually have to know what
you're doing, config the hardware right, and be very careful of the
execution plans going sour on you. But do all that, and we're
probably running about 10^6 bigger and faster than SQL 6.5 on a 100mhz
server ten years ago! On a given hardware platform, for an average
transactional mix (whatever that might mean), or for a preplanned OLAP
environment, SQLServer is at least competitive with anyone.
>What databases do the BIG database users use? eg AMEX? Walmart? etc. I
>guess they are not on SQL Server?
Oracle is still your game if you want to throw hardware at the
problem. Teradata is another way to go on humongous databases and
ad-hoc queries. I suppose DB2 fits in there somewhere, but I don't
know quite where.
J.
If a web site is running slowly then we can create a web farm and spilt the
problem between multiple servers.
However if my database is running slowly, and given that its schema is well
designed and the application that is communicating with it is well designed,
what options do I have to speed it up? Does SQL Server have a concept of
a database farm? Can I throw another server at it?
I understand that I could peel off read-only data to another database but
what would happen if it was all read-write? eg maybe like a banks computer
system or a credit card processing authority?
I could throw more CPU, RAM, hard disk, etc at it but ultimately this only
goes so far.
What databases do the BIG database users use? eg AMEX? Walmart? etc. I
guess they are not on SQL Server?
Regards
Dave ADave
I'd turn on SQL Server Profiler to identify long running queries , stored
procedures and try to imrove them. How to improve, that is probably playing
with indexes and/or if you are in SQL Server 2005 let the 'problematic'
query run throu Tuning wizard to advise you.
> What databases do the BIG database users use? eg AMEX? Walmart? etc. I
> guess they are not on SQL Server?
SQL Server has no problem to keep TERA byte databases , the point is how
well the db is designed?
"Dave A" <dave@.sigmasolutionsdonotspamme.com.au> wrote in message
news:e5kphEFJIHA.1208@.TK2MSFTNGP03.phx.gbl...
> This is a hyperthetical question.
> If a web site is running slowly then we can create a web farm and spilt
> the problem between multiple servers.
> However if my database is running slowly, and given that its schema is
> well designed and the application that is communicating with it is well
> designed, what options do I have to speed it up? Does SQL Server have a
> concept of a database farm? Can I throw another server at it?
> I understand that I could peel off read-only data to another database but
> what would happen if it was all read-write? eg maybe like a banks computer
> system or a credit card processing authority?
> I could throw more CPU, RAM, hard disk, etc at it but ultimately this only
> goes so far.
> What databases do the BIG database users use? eg AMEX? Walmart? etc. I
> guess they are not on SQL Server?
> Regards
> Dave A
>|||Remember, this is a hyperthetical question. There is no need to run Profile
since there is no database to profile.
I have no doubt that SQL Server can handle multiterabyte datbases, my
problem is when there are a million conurrenct users updating and reading
that data, what options does an SQL Server designer have at thier disposal?
(Don't worry, I am an SQL Server zealot. I am just wondering what happens
when we push the limits and how far can it go.)
So, my question remains. Is there a concept of a farm of sql servers? - just
like there is a cluster of sql servers or a farm of web servers.
Regards
Dave A
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ec0KCYFJIHA.5468@.TK2MSFTNGP05.phx.gbl...
> Dave
> I'd turn on SQL Server Profiler to identify long running queries , stored
> procedures and try to imrove them. How to improve, that is probably
> playing with indexes and/or if you are in SQL Server 2005 let the
> 'problematic' query run throu Tuning wizard to advise you.
>
>
> SQL Server has no problem to keep TERA byte databases , the point is how
> well the db is designed?
>
>
> "Dave A" <dave@.sigmasolutionsdonotspamme.com.au> wrote in message
> news:e5kphEFJIHA.1208@.TK2MSFTNGP03.phx.gbl...
>|||When it comes to CPU and RAM, you can only scale up, not scale out. So
"throwing another server at it" will not help.
With the long standing trend of ever faster CPU's and the growing number
of cores per computer this can go a long way.
But even if SQL Server supported scaling out, this would only go "so
far" too. If you have one server and add an identical server, the
performance gain will never be 100%, it will be less. There is a limit
as to how much you can scale out, just as there is a limit to how much
you can scale up.
You should have a look at the TPC numbers show (see www.tpc.org). An
interesting chart is the Top 10 TPC-C by Performance. You will notice
that there is only one clustered configuration in this top 10, which
uses Oracle 10g. All others are nonclustered configuration are
nonclustered, including 4 other Oracle 10g configurations.
I therefore conclude that currently, there is no practical need for a
clustered SQL Server solution.
Gert-Jan
Dave A wrote:
> This is a hyperthetical question.
> If a web site is running slowly then we can create a web farm and spilt th
e
> problem between multiple servers.
> However if my database is running slowly, and given that its schema is wel
l
> designed and the application that is communicating with it is well designe
d,
> what options do I have to speed it up? Does SQL Server have a concept o
f
> a database farm? Can I throw another server at it?
> I understand that I could peel off read-only data to another database but
> what would happen if it was all read-write? eg maybe like a banks computer
> system or a credit card processing authority?
> I could throw more CPU, RAM, hard disk, etc at it but ultimately this only
> goes so far.
> What databases do the BIG database users use? eg AMEX? Walmart? etc. I
> guess they are not on SQL Server?
> Regards
> Dave A|||On Sun, 11 Nov 2007 20:54:20 +1000, "Dave A"
<dave@.sigmasolutionsdonotspamme.com.au> wrote:
>This is a hyperthetical question.
>If a web site is running slowly then we can create a web farm and spilt the
>problem between multiple servers.
>However if my database is running slowly, and given that its schema is well
>designed and the application that is communicating with it is well designed
,
>what options do I have to speed it up? Does SQL Server have a concept of
>a database farm? Can I throw another server at it?
No. Not automagically. You are of course free to put all the A's on
one server, etc, and replicate data between them. This is often
enough done, but it is a lot of work. SQL Server 2008 looks like it's
aimed at much bigger configs, but I'm not up on the details.
>I could throw more CPU, RAM, hard disk, etc at it but ultimately this only
>goes so far.
It goes pretty far, on a maxed-out Wintel machine, these days, with
SAN storage, etc. But increasingly, you actually have to know what
you're doing, config the hardware right, and be very careful of the
execution plans going sour on you. But do all that, and we're
probably running about 10^6 bigger and faster than SQL 6.5 on a 100mhz
server ten years ago! On a given hardware platform, for an average
transactional mix (whatever that might mean), or for a preplanned OLAP
environment, SQLServer is at least competitive with anyone.
>What databases do the BIG database users use? eg AMEX? Walmart? etc. I
>guess they are not on SQL Server?
Oracle is still your game if you want to throw hardware at the
problem. Teradata is another way to go on humongous databases and
ad-hoc queries. I suppose DB2 fits in there somewhere, but I don't
know quite where.
J.
Monday, March 26, 2012
General Access Project info
Hi all, we currently have an access databse with linked odbc connections to an SQL server database.
Performance wise I don't think this is great.
Anyone have any general information about doing an upgrade to a micrsoft project database, and what are the pros and cons of access projects compared to Access databases with linked tables.
About 30 -40 users currently hammer the access front end during the day.Access Data Projects are MUCH better than Access MDB databases with linked tables. Off the top of my head I can't think of any advantage to using an MDB with linked tables instead of going with an ADP file.|||OK, one disadvantage: You cannot store ANY tables locally in an Access ADP file, so any setup or configuration data cannot be stored on the user's machine (at least in the database file).
Performance wise I don't think this is great.
Anyone have any general information about doing an upgrade to a micrsoft project database, and what are the pros and cons of access projects compared to Access databases with linked tables.
About 30 -40 users currently hammer the access front end during the day.Access Data Projects are MUCH better than Access MDB databases with linked tables. Off the top of my head I can't think of any advantage to using an MDB with linked tables instead of going with an ADP file.|||OK, one disadvantage: You cannot store ANY tables locally in an Access ADP file, so any setup or configuration data cannot be stored on the user's machine (at least in the database file).
Friday, March 23, 2012
FYI - Performance Tuning Tip for SQL Server 2000 with Federated Model
We just recently encountered this and I thought I'd pass it on.
If you are using SQL parameters for updates, make sure your parameters EXACTLY match the size of VARCHAR fields. If they don't, the procedures will run practically forever.
TIM ELLISON
This does not look right. Can you give an example?
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tim Ellison" <btesubscriptions@.direcway.com> wrote in message
news:OKaxTuHtFHA.3644@.TK2MSFTNGP11.phx.gbl...
We just recently encountered this and I thought I'd pass it on.
If you are using SQL parameters for updates, make sure your parameters
EXACTLY match the size of VARCHAR fields. If they don't, the procedures
will run practically forever.
TIM ELLISON
|||Yes. I'm interested too!. Please post details.
Nik Marshall-Blank MCSD/MCDBA
"Tim Ellison" <btesubscriptions@.direcway.com> wrote in message news:OKaxTuHtFHA.3644@.TK2MSFTNGP11.phx.gbl...
We just recently encountered this and I thought I'd pass it on.
If you are using SQL parameters for updates, make sure your parameters EXACTLY match the size of VARCHAR fields. If they don't, the procedures will run practically forever.
TIM ELLISON
If you are using SQL parameters for updates, make sure your parameters EXACTLY match the size of VARCHAR fields. If they don't, the procedures will run practically forever.
TIM ELLISON
This does not look right. Can you give an example?
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tim Ellison" <btesubscriptions@.direcway.com> wrote in message
news:OKaxTuHtFHA.3644@.TK2MSFTNGP11.phx.gbl...
We just recently encountered this and I thought I'd pass it on.
If you are using SQL parameters for updates, make sure your parameters
EXACTLY match the size of VARCHAR fields. If they don't, the procedures
will run practically forever.
TIM ELLISON
|||Yes. I'm interested too!. Please post details.
Nik Marshall-Blank MCSD/MCDBA
"Tim Ellison" <btesubscriptions@.direcway.com> wrote in message news:OKaxTuHtFHA.3644@.TK2MSFTNGP11.phx.gbl...
We just recently encountered this and I thought I'd pass it on.
If you are using SQL parameters for updates, make sure your parameters EXACTLY match the size of VARCHAR fields. If they don't, the procedures will run practically forever.
TIM ELLISON
FYI - Performance Tuning Tip for SQL Server 2000 with Federated Model
We just recently encountered this and I thought I'd pass it on.
If you are using SQL parameters for updates, make sure your parameters EXACT
LY match the size of VARCHAR fields. If they don't, the procedures will run
practically forever.
--
TIM ELLISONThis does not look right. Can you give an example?
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tim Ellison" <btesubscriptions@.direcway.com> wrote in message
news:OKaxTuHtFHA.3644@.TK2MSFTNGP11.phx.gbl...
We just recently encountered this and I thought I'd pass it on.
If you are using SQL parameters for updates, make sure your parameters
EXACTLY match the size of VARCHAR fields. If they don't, the procedures
will run practically forever.
TIM ELLISON|||Yes. I'm interested too!. Please post details.
--
Nik Marshall-Blank MCSD/MCDBA
"Tim Ellison" <btesubscriptions@.direcway.com> wrote in message news:OKaxTuHt
FHA.3644@.TK2MSFTNGP11.phx.gbl...
We just recently encountered this and I thought I'd pass it on.
If you are using SQL parameters for updates, make sure your parameters EXACT
LY match the size of VARCHAR fields. If they don't, the procedures will run
practically forever.
--
TIM ELLISONsql
If you are using SQL parameters for updates, make sure your parameters EXACT
LY match the size of VARCHAR fields. If they don't, the procedures will run
practically forever.
--
TIM ELLISONThis does not look right. Can you give an example?
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tim Ellison" <btesubscriptions@.direcway.com> wrote in message
news:OKaxTuHtFHA.3644@.TK2MSFTNGP11.phx.gbl...
We just recently encountered this and I thought I'd pass it on.
If you are using SQL parameters for updates, make sure your parameters
EXACTLY match the size of VARCHAR fields. If they don't, the procedures
will run practically forever.
TIM ELLISON|||Yes. I'm interested too!. Please post details.
--
Nik Marshall-Blank MCSD/MCDBA
"Tim Ellison" <btesubscriptions@.direcway.com> wrote in message news:OKaxTuHt
FHA.3644@.TK2MSFTNGP11.phx.gbl...
We just recently encountered this and I thought I'd pass it on.
If you are using SQL parameters for updates, make sure your parameters EXACT
LY match the size of VARCHAR fields. If they don't, the procedures will run
practically forever.
--
TIM ELLISONsql
Wednesday, March 21, 2012
Fuzzy Logic Performance
Hello,
I am just wondering if someone out there has tried some Fuzzy matching on databases of large scale i.e - about 20 million contact records. Suppose I wanted to perform matching/ grouping to 10 000 incoming messages. How fast does this usually take? What is the dependence on the number of fields chosen for the match?
I am just wondering if someone out there has tried some Fuzzy matching on databases of large scale i.e - about 20 million contact records. Suppose I wanted to perform matching/ grouping to 10 000 incoming messages. How fast does this usually take? What is the dependence on the number of fields chosen for the match?
Any insight is greatly appreciated,
Lubomir
Pls take a look at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/FzDTSSQL05.asp
Thanks
Wenyang
|||Thank you Wenyang, great linkLubomir
Monday, March 19, 2012
Functions vs StoredProcedures
From a sp I should call many other sp or functions. There're difference
between SP and Functions about performance? Are functions more performant
then sp.
ThanksProcedures and Functions are not functionaly equivalent. For example,
functions can only perform inserts/updates/deletes on table variables that
it declares. Also, only a function can return a scalar value.
http://msdn.microsoft.com/library/d...edprocedure.asp
http://msdn.microsoft.com/library/d...br />
50mr.asp
"checcouno" <checcouno@.discussions.microsoft.com> wrote in message
news:D8456144-D913-4F39-AF15-F786C3155C18@.microsoft.com...
> From a sp I should call many other sp or functions. There're difference
> between SP and Functions about performance? Are functions more performant
> then sp.
> Thanks|||>> functions can only perform inserts/updates/deletes on table variables
Just to clarify. Functions can be table valued as well. And you can update
permanent tables in a database using a table valued function as well:
CREATE TABLE tbl ( col INT NOT NULL PRIMARY KEY );
GO
CREATE FUNCTION ufn ( @.p INT ) RETURNS TABLE AS
RETURN ( SELECT col FROM tbl WHERE col = @.p )
GO
INSERT ufn(1) SELECT 1 ; SELECT * FROM tbl ;
UPDATE ufn(1) SET col = 2 WHERE col = 1 ; SELECT * FROM tbl ;
DELETE ufn(2) WHERE col = 2 ; SELECT * FROM tbl ;
The restriction is that you cannot perform them on permanent tables from
within the function.
Anith|||FWIW: 'performant' isn't a word.
"checcouno" <checcouno@.discussions.microsoft.com> wrote in message
news:D8456144-D913-4F39-AF15-F786C3155C18@.microsoft.com...
> From a sp I should call many other sp or functions. There're difference
> between SP and Functions about performance? Are functions more performant
> then sp.
> Thanks|||On Wed, 28 Sep 2005 11:26:57 -0500, Anith Sen wrote:
>Just to clarify. Functions can be table valued as well. And you can update
>permanent tables in a database using a table valued function as well:
(snip)
Hi Anith,
After running your code, I can't deny that you _CAN_ do this. But this
is not mentioned anywhere in Books Online (in fact, BOL says that
INSERT, UPDATE, and DELETE all operate on a table, a view, or a
OPENQUERY or OPENROWSET rowset-function). I don't think that anyone
should ever rely on this behaviour!
(If I may speculate - I *think* that the reason for this behaviour is
that inline table-valued functions and views have so much in common that
they re-use lots of the same code, and someone at MS forgot to exclude
UDFs in the re-used code for INSERT, UPDATE and DELETE).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Actually it is a word, but perhaps used out of context:
http://dictionary.reference.com/search?q=performant
"Mark" <a@.b.net> wrote in message
news:ueozaHGxFHA.612@.TK2MSFTNGP10.phx.gbl...
> FWIW: 'performant' isn't a word.
>
> "checcouno" <checcouno@.discussions.microsoft.com> wrote in message
> news:D8456144-D913-4F39-AF15-F786C3155C18@.microsoft.com...
>|||I don't think that english is his mother tongue.
In French "performant" is an adjective that discribes something that has
performs well.
For the life of me I cannot think of an English word that can be used in the
same way.
My French-English translator doesn't even make a suggestion.
"JT" <someone@.microsoft.com> wrote in message
news:OQKvMtRxFHA.3756@.tk2msftngp13.phx.gbl...
> Actually it is a word, but perhaps used out of context:
> http://dictionary.reference.com/search?q=performant
> "Mark" <a@.b.net> wrote in message
> news:ueozaHGxFHA.612@.TK2MSFTNGP10.phx.gbl...
>|||>> After running your code, I can't deny that you _CAN_ do this. But this is
I tend to agree with the lack of sufficient documentation which can cause
some confusion. For instance, add a DISTINCT to the SELECT clause in the UDF
& you'll see the same limitations of updateable views.
Anith
between SP and Functions about performance? Are functions more performant
then sp.
ThanksProcedures and Functions are not functionaly equivalent. For example,
functions can only perform inserts/updates/deletes on table variables that
it declares. Also, only a function can return a scalar value.
http://msdn.microsoft.com/library/d...edprocedure.asp
http://msdn.microsoft.com/library/d...br />
50mr.asp
"checcouno" <checcouno@.discussions.microsoft.com> wrote in message
news:D8456144-D913-4F39-AF15-F786C3155C18@.microsoft.com...
> From a sp I should call many other sp or functions. There're difference
> between SP and Functions about performance? Are functions more performant
> then sp.
> Thanks|||>> functions can only perform inserts/updates/deletes on table variables
Just to clarify. Functions can be table valued as well. And you can update
permanent tables in a database using a table valued function as well:
CREATE TABLE tbl ( col INT NOT NULL PRIMARY KEY );
GO
CREATE FUNCTION ufn ( @.p INT ) RETURNS TABLE AS
RETURN ( SELECT col FROM tbl WHERE col = @.p )
GO
INSERT ufn(1) SELECT 1 ; SELECT * FROM tbl ;
UPDATE ufn(1) SET col = 2 WHERE col = 1 ; SELECT * FROM tbl ;
DELETE ufn(2) WHERE col = 2 ; SELECT * FROM tbl ;
The restriction is that you cannot perform them on permanent tables from
within the function.
Anith|||FWIW: 'performant' isn't a word.
"checcouno" <checcouno@.discussions.microsoft.com> wrote in message
news:D8456144-D913-4F39-AF15-F786C3155C18@.microsoft.com...
> From a sp I should call many other sp or functions. There're difference
> between SP and Functions about performance? Are functions more performant
> then sp.
> Thanks|||On Wed, 28 Sep 2005 11:26:57 -0500, Anith Sen wrote:
>Just to clarify. Functions can be table valued as well. And you can update
>permanent tables in a database using a table valued function as well:
(snip)
Hi Anith,
After running your code, I can't deny that you _CAN_ do this. But this
is not mentioned anywhere in Books Online (in fact, BOL says that
INSERT, UPDATE, and DELETE all operate on a table, a view, or a
OPENQUERY or OPENROWSET rowset-function). I don't think that anyone
should ever rely on this behaviour!
(If I may speculate - I *think* that the reason for this behaviour is
that inline table-valued functions and views have so much in common that
they re-use lots of the same code, and someone at MS forgot to exclude
UDFs in the re-used code for INSERT, UPDATE and DELETE).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Actually it is a word, but perhaps used out of context:
http://dictionary.reference.com/search?q=performant
"Mark" <a@.b.net> wrote in message
news:ueozaHGxFHA.612@.TK2MSFTNGP10.phx.gbl...
> FWIW: 'performant' isn't a word.
>
> "checcouno" <checcouno@.discussions.microsoft.com> wrote in message
> news:D8456144-D913-4F39-AF15-F786C3155C18@.microsoft.com...
>|||I don't think that english is his mother tongue.
In French "performant" is an adjective that discribes something that has
performs well.
For the life of me I cannot think of an English word that can be used in the
same way.
My French-English translator doesn't even make a suggestion.
"JT" <someone@.microsoft.com> wrote in message
news:OQKvMtRxFHA.3756@.tk2msftngp13.phx.gbl...
> Actually it is a word, but perhaps used out of context:
> http://dictionary.reference.com/search?q=performant
> "Mark" <a@.b.net> wrote in message
> news:ueozaHGxFHA.612@.TK2MSFTNGP10.phx.gbl...
>|||>> After running your code, I can't deny that you _CAN_ do this. But this is
I tend to agree with the lack of sufficient documentation which can cause
some confusion. For instance, add a DISTINCT to the SELECT clause in the UDF
& you'll see the same limitations of updateable views.
Anith
functions performance question
I'm sure everybody agrees on the idea that SQL server 2000 functions are not well taken care of as far as performance optimization. I'm not sure if it is because they don't have excution plans? or if they have it but it's not optimized or whatever reason. The fact is they are slow!
My question for experts in that field is if this issue has been addressed in SQL 2005? or we still going to avoid the functions as much as possible?
Thanks,
Robert.
Hi Robert,
Could you please be more specific? In general it should be the other way around, see http://msdn2.microsoft.com/en-us/library/ms191007.aspx
Reasons for slowdown may be numerous and require further investigation.
Thank you,
Boris.
functions performance question
I'm sure everybody agrees on the idea that SQL server 2000 functions are not well taken care of as far as performance optimization. I'm not sure if it is because they don't have excution plans? or if they have it but it's not optimized or whatever reason. The fact is they are slow!
My question for experts in that field is if this issue has been addressed in SQL 2005? or we still going to avoid the functions as much as possible?
Thanks,
Robert.
Hi Robert,
Could you please be more specific? In general it should be the other way around, see http://msdn2.microsoft.com/en-us/library/ms191007.aspx
Reasons for slowdown may be numerous and require further investigation.
Thank you,
Boris.
Functions
Hi all,
I have read of information about how to performance tune stored procedures,
but is there any way of doing the same with functions or just some rules of
thumb that I should adhere to, my code looks like this :-
ALTER FUNCTION fnCalcStartOfPeriod(@.edition AS INT, @.startEdition AS
INT, @.period AS INT) RETURNS INT AS BEGIN
DECLARE @.diff AS INT
DECLARE @.adjust AS INT
SET @.diff = (@.edition /100) * 12 + (@.edition % 100) -
(@.startEdition /100) * 12 - (@.startEdition %
100);
IF (@.diff < 0) BEGIN
SET @.adjust = ((@.diff - @.period + 1) / @.period) * @.period
END ELSE BEGIN
SET @.adjust = (@.diff / @.period) * @.period
END
SET @.adjust = @.adjust + ((@.startEdition % 100)) - 1
IF (@.adjust < 0) BEGIN
SET @.startEdition = (@.startEdition / 100 + (@.adjust -
11) / 12) * 100 + ((@.adjust % 12) + 12) % 12 + 1
END ELSE BEGIN
SET @.startEdition = (@.startEdition / 100 + @.adjust /
12) * 100 + (@.adjust % 12) + 1
END
RETURN @.startEdition
END
Just wondering if there is a better way to write this or hints for
performance.
Thanks in advance
PhilHow about giving us some specs first. ;-)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:B72187B5-3EFD-4941-90BD-09B191338A9F@.microsoft.com...
Hi all,
I have read of information about how to performance tune stored procedures,
but is there any way of doing the same with functions or just some rules of
thumb that I should adhere to, my code looks like this :-
ALTER FUNCTION fnCalcStartOfPeriod(@.edition AS INT, @.startEdition AS
INT, @.period AS INT) RETURNS INT AS BEGIN
DECLARE @.diff AS INT
DECLARE @.adjust AS INT
SET @.diff = (@.edition /100) * 12 + (@.edition % 100) -
(@.startEdition /100) * 12 - (@.startEdition %
100);
IF (@.diff < 0) BEGIN
SET @.adjust = ((@.diff - @.period + 1) / @.period) * @.period
END ELSE BEGIN
SET @.adjust = (@.diff / @.period) * @.period
END
SET @.adjust = @.adjust + ((@.startEdition % 100)) - 1
IF (@.adjust < 0) BEGIN
SET @.startEdition = (@.startEdition / 100 + (@.adjust -
11) / 12) * 100 + ((@.adjust % 12) + 12) % 12 + 1
END ELSE BEGIN
SET @.startEdition = (@.startEdition / 100 + @.adjust /
12) * 100 + (@.adjust % 12) + 1
END
RETURN @.startEdition
END
Just wondering if there is a better way to write this or hints for
performance.
Thanks in advance
Phil|||Phil,
It's my experience that scalar functions that do not access
any tables and that don't have long loops or recursion are not
going to run faster or slower if rewritten - the work they incur
is largely in the passing of parameters and returning of a result.
The only real optimization is to write the function inline. This
might be close:
convert(char(6),
dateadd(
month,
datediff(
month,
cast(right(@.startEdition-100*@.period, 6) + '01' as datetime),
cast(right(@.edition,6)+'01' as datetime)
)/@.period*@.period,
cast(right(@.startEdition-100*@.period, 6) + '01' as datetime)
),
112)
Steve Kass
Drew University
Phil wrote:
>Hi all,
>I have read of information about how to performance tune stored procedures,
>but is there any way of doing the same with functions or just some rules of
>thumb that I should adhere to, my code looks like this :-
>ALTER FUNCTION fnCalcStartOfPeriod(@.edition AS INT, @.startEdition AS
>INT, @.period AS INT) RETURNS INT AS BEGIN
> DECLARE @.diff AS INT
> DECLARE @.adjust AS INT
> SET @.diff = (@.edition /100) * 12 + (@.edition % 100) -
> (@.startEdition /100) * 12 - (@.startEdition %
>100);
> IF (@.diff < 0) BEGIN
> SET @.adjust = ((@.diff - @.period + 1) / @.period) * @.period
> END ELSE BEGIN
> SET @.adjust = (@.diff / @.period) * @.period
> END
> SET @.adjust = @.adjust + ((@.startEdition % 100)) - 1
> IF (@.adjust < 0) BEGIN
> SET @.startEdition = (@.startEdition / 100 + (@.adjust -
>11) / 12) * 100 + ((@.adjust % 12) + 12) % 12 + 1
> END ELSE BEGIN
> SET @.startEdition = (@.startEdition / 100 + @.adjust /
>12) * 100 + (@.adjust % 12) + 1
> END
> RETURN @.startEdition
>END
>
>Just wondering if there is a better way to write this or hints for
>performance.
>Thanks in advance
>Phil
>|||Hi Tom,
Sorry about that, getting a bit late should of really left this till the
morning where I was thinking a little better. Not really sure what you need
to know but I am using sql server 2000, the code is used for determining
contract start dates by passing in e.g. 200503 YYYY/MM dates, my code works
fine just wondering if there are any options that you can either turn on or
off that may affect how the funtion performs. The reason I ask is I know
that you can set things like SET NOCOUNT in stored procedures that does have
some affect on performance just wanting to know if there is anything similar
for funtions, I really should of made this more clear in my first post, sorr
y.
Thanks Phil
"Tom Moreau" wrote:
> How about giving us some specs first. ;-)
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> ..
> "Phil" <Phil@.discussions.microsoft.com> wrote in message
> news:B72187B5-3EFD-4941-90BD-09B191338A9F@.microsoft.com...
> Hi all,
> I have read of information about how to performance tune stored procedures
,
> but is there any way of doing the same with functions or just some rules o
f
> thumb that I should adhere to, my code looks like this :-
> ALTER FUNCTION fnCalcStartOfPeriod(@.edition AS INT, @.startEdition AS
> INT, @.period AS INT) RETURNS INT AS BEGIN
> DECLARE @.diff AS INT
> DECLARE @.adjust AS INT
> SET @.diff = (@.edition /100) * 12 + (@.edition % 100) -
> (@.startEdition /100) * 12 - (@.startEdition %
> 100);
> IF (@.diff < 0) BEGIN
> SET @.adjust = ((@.diff - @.period + 1) / @.period) * @.period
> END ELSE BEGIN
> SET @.adjust = (@.diff / @.period) * @.period
> END
> SET @.adjust = @.adjust + ((@.startEdition % 100)) - 1
> IF (@.adjust < 0) BEGIN
> SET @.startEdition = (@.startEdition / 100 + (@.adjust -
> 11) / 12) * 100 + ((@.adjust % 12) + 12) % 12 + 1
> END ELSE BEGIN
> SET @.startEdition = (@.startEdition / 100 + @.adjust /
> 12) * 100 + (@.adjust % 12) + 1
> END
> RETURN @.startEdition
> END
>
> Just wondering if there is a better way to write this or hints for
> performance.
> Thanks in advance
> Phil
>
I have read of information about how to performance tune stored procedures,
but is there any way of doing the same with functions or just some rules of
thumb that I should adhere to, my code looks like this :-
ALTER FUNCTION fnCalcStartOfPeriod(@.edition AS INT, @.startEdition AS
INT, @.period AS INT) RETURNS INT AS BEGIN
DECLARE @.diff AS INT
DECLARE @.adjust AS INT
SET @.diff = (@.edition /100) * 12 + (@.edition % 100) -
(@.startEdition /100) * 12 - (@.startEdition %
100);
IF (@.diff < 0) BEGIN
SET @.adjust = ((@.diff - @.period + 1) / @.period) * @.period
END ELSE BEGIN
SET @.adjust = (@.diff / @.period) * @.period
END
SET @.adjust = @.adjust + ((@.startEdition % 100)) - 1
IF (@.adjust < 0) BEGIN
SET @.startEdition = (@.startEdition / 100 + (@.adjust -
11) / 12) * 100 + ((@.adjust % 12) + 12) % 12 + 1
END ELSE BEGIN
SET @.startEdition = (@.startEdition / 100 + @.adjust /
12) * 100 + (@.adjust % 12) + 1
END
RETURN @.startEdition
END
Just wondering if there is a better way to write this or hints for
performance.
Thanks in advance
PhilHow about giving us some specs first. ;-)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:B72187B5-3EFD-4941-90BD-09B191338A9F@.microsoft.com...
Hi all,
I have read of information about how to performance tune stored procedures,
but is there any way of doing the same with functions or just some rules of
thumb that I should adhere to, my code looks like this :-
ALTER FUNCTION fnCalcStartOfPeriod(@.edition AS INT, @.startEdition AS
INT, @.period AS INT) RETURNS INT AS BEGIN
DECLARE @.diff AS INT
DECLARE @.adjust AS INT
SET @.diff = (@.edition /100) * 12 + (@.edition % 100) -
(@.startEdition /100) * 12 - (@.startEdition %
100);
IF (@.diff < 0) BEGIN
SET @.adjust = ((@.diff - @.period + 1) / @.period) * @.period
END ELSE BEGIN
SET @.adjust = (@.diff / @.period) * @.period
END
SET @.adjust = @.adjust + ((@.startEdition % 100)) - 1
IF (@.adjust < 0) BEGIN
SET @.startEdition = (@.startEdition / 100 + (@.adjust -
11) / 12) * 100 + ((@.adjust % 12) + 12) % 12 + 1
END ELSE BEGIN
SET @.startEdition = (@.startEdition / 100 + @.adjust /
12) * 100 + (@.adjust % 12) + 1
END
RETURN @.startEdition
END
Just wondering if there is a better way to write this or hints for
performance.
Thanks in advance
Phil|||Phil,
It's my experience that scalar functions that do not access
any tables and that don't have long loops or recursion are not
going to run faster or slower if rewritten - the work they incur
is largely in the passing of parameters and returning of a result.
The only real optimization is to write the function inline. This
might be close:
convert(char(6),
dateadd(
month,
datediff(
month,
cast(right(@.startEdition-100*@.period, 6) + '01' as datetime),
cast(right(@.edition,6)+'01' as datetime)
)/@.period*@.period,
cast(right(@.startEdition-100*@.period, 6) + '01' as datetime)
),
112)
Steve Kass
Drew University
Phil wrote:
>Hi all,
>I have read of information about how to performance tune stored procedures,
>but is there any way of doing the same with functions or just some rules of
>thumb that I should adhere to, my code looks like this :-
>ALTER FUNCTION fnCalcStartOfPeriod(@.edition AS INT, @.startEdition AS
>INT, @.period AS INT) RETURNS INT AS BEGIN
> DECLARE @.diff AS INT
> DECLARE @.adjust AS INT
> SET @.diff = (@.edition /100) * 12 + (@.edition % 100) -
> (@.startEdition /100) * 12 - (@.startEdition %
>100);
> IF (@.diff < 0) BEGIN
> SET @.adjust = ((@.diff - @.period + 1) / @.period) * @.period
> END ELSE BEGIN
> SET @.adjust = (@.diff / @.period) * @.period
> END
> SET @.adjust = @.adjust + ((@.startEdition % 100)) - 1
> IF (@.adjust < 0) BEGIN
> SET @.startEdition = (@.startEdition / 100 + (@.adjust -
>11) / 12) * 100 + ((@.adjust % 12) + 12) % 12 + 1
> END ELSE BEGIN
> SET @.startEdition = (@.startEdition / 100 + @.adjust /
>12) * 100 + (@.adjust % 12) + 1
> END
> RETURN @.startEdition
>END
>
>Just wondering if there is a better way to write this or hints for
>performance.
>Thanks in advance
>Phil
>|||Hi Tom,
Sorry about that, getting a bit late should of really left this till the
morning where I was thinking a little better. Not really sure what you need
to know but I am using sql server 2000, the code is used for determining
contract start dates by passing in e.g. 200503 YYYY/MM dates, my code works
fine just wondering if there are any options that you can either turn on or
off that may affect how the funtion performs. The reason I ask is I know
that you can set things like SET NOCOUNT in stored procedures that does have
some affect on performance just wanting to know if there is anything similar
for funtions, I really should of made this more clear in my first post, sorr
y.
Thanks Phil
"Tom Moreau" wrote:
> How about giving us some specs first. ;-)
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> ..
> "Phil" <Phil@.discussions.microsoft.com> wrote in message
> news:B72187B5-3EFD-4941-90BD-09B191338A9F@.microsoft.com...
> Hi all,
> I have read of information about how to performance tune stored procedures
,
> but is there any way of doing the same with functions or just some rules o
f
> thumb that I should adhere to, my code looks like this :-
> ALTER FUNCTION fnCalcStartOfPeriod(@.edition AS INT, @.startEdition AS
> INT, @.period AS INT) RETURNS INT AS BEGIN
> DECLARE @.diff AS INT
> DECLARE @.adjust AS INT
> SET @.diff = (@.edition /100) * 12 + (@.edition % 100) -
> (@.startEdition /100) * 12 - (@.startEdition %
> 100);
> IF (@.diff < 0) BEGIN
> SET @.adjust = ((@.diff - @.period + 1) / @.period) * @.period
> END ELSE BEGIN
> SET @.adjust = (@.diff / @.period) * @.period
> END
> SET @.adjust = @.adjust + ((@.startEdition % 100)) - 1
> IF (@.adjust < 0) BEGIN
> SET @.startEdition = (@.startEdition / 100 + (@.adjust -
> 11) / 12) * 100 + ((@.adjust % 12) + 12) % 12 + 1
> END ELSE BEGIN
> SET @.startEdition = (@.startEdition / 100 + @.adjust /
> 12) * 100 + (@.adjust % 12) + 1
> END
> RETURN @.startEdition
> END
>
> Just wondering if there is a better way to write this or hints for
> performance.
> Thanks in advance
> Phil
>
Monday, March 12, 2012
function to return table variable
my question has to do with the performance of user-defined function that
returns a table variable vs. traditional temp tables. i have a function
that consists of a select statement that populates and returns a table
variable. the select is fairly complex and takes a siginificant amount of
time to run mainly due to the size of the tables being queried. the
resultset, is not that large (usually around 1000 rows in 10 columns). the
procedure that uses the data in the table variable makes reference to this
table variable in more than one location using something like:
select * from dbo.fTblVar(param1,param2,param3)
does this mean that each time the calling procedure makes reference to the
function, the complex (and long-running) select statement will be executed?
if this is the case, it seems that i would be better off using a traditional
temp table that is created and populated once, then acted on as needed. or
does this so somehow get cached sql server's memory for the duration of the
procedure? what if the parameter values change?Well if you call the UDF every time, then yes, it willrun every time... But
if the contents are not different then just run the UDF Once, outside your
query, and dump the valeusinto a local table variable, and use that table
variable in your query, then it won;t be running everytime
Declare @.T Table (<column Defintions> )
Insert @.T
select * from dbo.fTblVar(param1,param2,param3)
-- Now the @.T variable can be used throughout the rest of your SP Exactly
like a temp table would be used...
Which is better depends on how much data is in it, and what you need to do
with it. You can't put additional indexes (Other than Primary Key COnstrain
t
Index) on table variables, so if you need to really manipulate the data in
the table, a temp table is more flexible, but if all you need is a temporar
y
list of keys, say, for joining in another query, then table variables are
100% in memory, and should be much faster. If you use them, however, keep
them narrow.
"JT" wrote:
> my question has to do with the performance of user-defined function that
> returns a table variable vs. traditional temp tables. i have a function
> that consists of a select statement that populates and returns a table
> variable. the select is fairly complex and takes a siginificant amount of
> time to run mainly due to the size of the tables being queried. the
> resultset, is not that large (usually around 1000 rows in 10 columns). th
e
> procedure that uses the data in the table variable makes reference to this
> table variable in more than one location using something like:
> select * from dbo.fTblVar(param1,param2,param3)
> does this mean that each time the calling procedure makes reference to the
> function, the complex (and long-running) select statement will be executed
?
> if this is the case, it seems that i would be better off using a tradition
al
> temp table that is created and populated once, then acted on as needed. o
r
> does this so somehow get cached sql server's memory for the duration of th
e
> procedure? what if the parameter values change?
>
>
returns a table variable vs. traditional temp tables. i have a function
that consists of a select statement that populates and returns a table
variable. the select is fairly complex and takes a siginificant amount of
time to run mainly due to the size of the tables being queried. the
resultset, is not that large (usually around 1000 rows in 10 columns). the
procedure that uses the data in the table variable makes reference to this
table variable in more than one location using something like:
select * from dbo.fTblVar(param1,param2,param3)
does this mean that each time the calling procedure makes reference to the
function, the complex (and long-running) select statement will be executed?
if this is the case, it seems that i would be better off using a traditional
temp table that is created and populated once, then acted on as needed. or
does this so somehow get cached sql server's memory for the duration of the
procedure? what if the parameter values change?Well if you call the UDF every time, then yes, it willrun every time... But
if the contents are not different then just run the UDF Once, outside your
query, and dump the valeusinto a local table variable, and use that table
variable in your query, then it won;t be running everytime
Declare @.T Table (<column Defintions> )
Insert @.T
select * from dbo.fTblVar(param1,param2,param3)
-- Now the @.T variable can be used throughout the rest of your SP Exactly
like a temp table would be used...
Which is better depends on how much data is in it, and what you need to do
with it. You can't put additional indexes (Other than Primary Key COnstrain
t
Index) on table variables, so if you need to really manipulate the data in
the table, a temp table is more flexible, but if all you need is a temporar
y
list of keys, say, for joining in another query, then table variables are
100% in memory, and should be much faster. If you use them, however, keep
them narrow.
"JT" wrote:
> my question has to do with the performance of user-defined function that
> returns a table variable vs. traditional temp tables. i have a function
> that consists of a select statement that populates and returns a table
> variable. the select is fairly complex and takes a siginificant amount of
> time to run mainly due to the size of the tables being queried. the
> resultset, is not that large (usually around 1000 rows in 10 columns). th
e
> procedure that uses the data in the table variable makes reference to this
> table variable in more than one location using something like:
> select * from dbo.fTblVar(param1,param2,param3)
> does this mean that each time the calling procedure makes reference to the
> function, the complex (and long-running) select statement will be executed
?
> if this is the case, it seems that i would be better off using a tradition
al
> temp table that is created and populated once, then acted on as needed. o
r
> does this so somehow get cached sql server's memory for the duration of th
e
> procedure? what if the parameter values change?
>
>
Friday, March 9, 2012
function performance question
can anyone explain to me why the code excerpt 1 performs 60 reads on my DB, and code excerpt 2 performs 140000 ?
I know that specifically the statements are doing different things but they are both inserting into tables based on input parameters.
All relevant fields are indexed so I wouldn't have thought this was the issue?
Does the number of joins really make such a difference to performance?
code excerpt 1 (60 reads)
INSERT INTO @.table_var
SELECT dbo.Organisation.OrganisationName,
dbo.Organisation.DepartmentName,
dbo.Address.BuildingNumber,
dbo.BuildingName.BuildingName,
dbo.SubBuildingName.SubBuildingName,
Thoroughfare_1.ThoroughfareName AS DependentThoroughfareName,
ThoroughfareDescriptor_1.ThoroughfareDescriptor AS DependentThoroughfareDescriptor,
dbo.Thoroughfare.ThoroughfareName,
dbo.ThoroughfareDescriptor.ThoroughfareDescriptor,
dbo.Locality.DoubleDependentLocality,
dbo.Locality.DependentLocality,
dbo.Locality.PostTown,
dbo.Address.Outcode,
dbo.Address.Incode,
dbo.Address.ConcatenationIndicator
FROM dbo.Address INNER JOIN
dbo.BuildingName ON dbo.Address.BuildingNameKey = dbo.BuildingName.BuildingNameKey INNER JOIN
dbo.Locality ON dbo.Address.LocalityKey = dbo.Locality.LocalityKey INNER JOIN
dbo.Organisation ON dbo.Address.OrganisationKey = dbo.Organisation.OrganisationKey AND
dbo.Address.PostcodeType = dbo.Organisation.PostcodeType INNER JOIN
dbo.SubBuildingName ON dbo.Address.SubBuildingNameKey = dbo.SubBuildingName.SubBuildingNameKey INNER JOIN
dbo.Thoroughfare ON dbo.Address.ThoroughfareKey = dbo.Thoroughfare.ThoroughfareKey INNER JOIN
dbo.ThoroughfareDescriptor ON dbo.Address.ThoroughfareDescriptorKey = dbo.ThoroughfareDescriptor.ThoroughfareDescriptorK ey INNER JOIN
dbo.Thoroughfare Thoroughfare_1 ON dbo.Address.DependentThoroughfareKey = Thoroughfare_1.ThoroughfareKey INNER JOIN
dbo.ThoroughfareDescriptor ThoroughfareDescriptor_1 ON
dbo.Address.DependentThoroughfareDescriptorKey = ThoroughfareDescriptor_1.ThoroughfareDescriptorKey
WHERE (dbo.Address.AddressKey = @.addresskey) AND
(dbo.Address.OrganisationKey = @.organisationkey) AND
(dbo.Address.PostcodeType = @.postcodetype)
code excerpt 2:
INSERT INTO @.table_var_out
SELECT dbo.Organisation.OrganisationName, dbo.Address.OrganisationKey, dbo.Address.AddressKey, dbo.Address.PostcodeType
FROM dbo.Address INNER JOIN
dbo.Organisation ON dbo.Address.OrganisationKey = dbo.Organisation.OrganisationKey AND
dbo.Address.PostcodeType = dbo.Organisation.PostcodeType
WHERE (dbo.Address.Outcode = @.outcode) AND (dbo.Address.Incode = @.incode)Not all indexes are equal. Have you looked at the query plans for the select statements?
I know that specifically the statements are doing different things but they are both inserting into tables based on input parameters.
All relevant fields are indexed so I wouldn't have thought this was the issue?
Does the number of joins really make such a difference to performance?
code excerpt 1 (60 reads)
INSERT INTO @.table_var
SELECT dbo.Organisation.OrganisationName,
dbo.Organisation.DepartmentName,
dbo.Address.BuildingNumber,
dbo.BuildingName.BuildingName,
dbo.SubBuildingName.SubBuildingName,
Thoroughfare_1.ThoroughfareName AS DependentThoroughfareName,
ThoroughfareDescriptor_1.ThoroughfareDescriptor AS DependentThoroughfareDescriptor,
dbo.Thoroughfare.ThoroughfareName,
dbo.ThoroughfareDescriptor.ThoroughfareDescriptor,
dbo.Locality.DoubleDependentLocality,
dbo.Locality.DependentLocality,
dbo.Locality.PostTown,
dbo.Address.Outcode,
dbo.Address.Incode,
dbo.Address.ConcatenationIndicator
FROM dbo.Address INNER JOIN
dbo.BuildingName ON dbo.Address.BuildingNameKey = dbo.BuildingName.BuildingNameKey INNER JOIN
dbo.Locality ON dbo.Address.LocalityKey = dbo.Locality.LocalityKey INNER JOIN
dbo.Organisation ON dbo.Address.OrganisationKey = dbo.Organisation.OrganisationKey AND
dbo.Address.PostcodeType = dbo.Organisation.PostcodeType INNER JOIN
dbo.SubBuildingName ON dbo.Address.SubBuildingNameKey = dbo.SubBuildingName.SubBuildingNameKey INNER JOIN
dbo.Thoroughfare ON dbo.Address.ThoroughfareKey = dbo.Thoroughfare.ThoroughfareKey INNER JOIN
dbo.ThoroughfareDescriptor ON dbo.Address.ThoroughfareDescriptorKey = dbo.ThoroughfareDescriptor.ThoroughfareDescriptorK ey INNER JOIN
dbo.Thoroughfare Thoroughfare_1 ON dbo.Address.DependentThoroughfareKey = Thoroughfare_1.ThoroughfareKey INNER JOIN
dbo.ThoroughfareDescriptor ThoroughfareDescriptor_1 ON
dbo.Address.DependentThoroughfareDescriptorKey = ThoroughfareDescriptor_1.ThoroughfareDescriptorKey
WHERE (dbo.Address.AddressKey = @.addresskey) AND
(dbo.Address.OrganisationKey = @.organisationkey) AND
(dbo.Address.PostcodeType = @.postcodetype)
code excerpt 2:
INSERT INTO @.table_var_out
SELECT dbo.Organisation.OrganisationName, dbo.Address.OrganisationKey, dbo.Address.AddressKey, dbo.Address.PostcodeType
FROM dbo.Address INNER JOIN
dbo.Organisation ON dbo.Address.OrganisationKey = dbo.Organisation.OrganisationKey AND
dbo.Address.PostcodeType = dbo.Organisation.PostcodeType
WHERE (dbo.Address.Outcode = @.outcode) AND (dbo.Address.Incode = @.incode)Not all indexes are equal. Have you looked at the query plans for the select statements?
Function Performance question
I do have one store procedure which does insert into one table
CREATE PROCEDURE StoreProc1
AS
DECLARE testcursor CURSOR FOR
SELECT col1
FROM table
WHERE Id = @.ID
OPEN testcursor
FETCH NEXT FROM cursor INTO @.col1
WHILE @.@.FETCH_STATUS = 0
BEGIN
--Here i have to use cursor because i am doing some calculation
--here based value of co11
--And then insert into one table
INSERT INTO TESTTABLE
(id,transactiondate...) values (@.value1,@.value2......)
FETCH NEXT FROM testcursor INTO @.col1
END
CLOSE testcursor
DEALLOCATE testcursor
This StoreProc1 i am running every night and which insert approx 500,000
records into TESTTABLE..Now I have a very simple function on TESTTABLE
which is as following..which i use in other store procedures...
CREATE FUNCTION TestFunction
(@.ID as INT,@.dt1 datetime,@.dt2 datetime)
returns money
AS
BEGIN
DECLARE @.retmoney money
SELECT @.retmoney = sum(amount)
FROM TESTTABLE
WHERE transactiondate between @.dt1 and @.dt2 and id = @.Id
and categoryid not in ('1','2')
RETURN @.retmoney
END
so what happen after running StoreProc1 every night...(which insert into
500 K records into TESTTABLE.. My function TestFunction becomes so slow.. it
takes 10 second to run and if i run query of that function
SELECT @.retmoney = sum(amount)
FROM TESTTABLE
WHERE createddate between @.dt1 and @.dt2 and id = @.Id
and categoryid not in ('1','2')
it get execute in only o seconds...
so why if i run that function it takes long and if i run that same query it
is fast...
Pls let me know.Hi
You may have perform maintainance on this table to update the indexes or
statistics as they could be fragmented or out of date. See DBCC SHOWCONTIG,
DBCC DBREINDEX and UPDATE STATISTICS in books online
John
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:6360AC44-D1D9-4D05-91B3-201C58E1B543@.microsoft.com...
>I do have one store procedure which does insert into one table
> CREATE PROCEDURE StoreProc1
> AS
> DECLARE testcursor CURSOR FOR
> SELECT col1
> FROM table
> WHERE Id = @.ID
> OPEN testcursor
> FETCH NEXT FROM cursor INTO @.col1
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> --Here i have to use cursor because i am doing some calculation
> --here based value of co11
> --And then insert into one table
> INSERT INTO TESTTABLE
> (id,transactiondate...) values (@.value1,@.value2......)
> FETCH NEXT FROM testcursor INTO @.col1
> END
> CLOSE testcursor
> DEALLOCATE testcursor
> This StoreProc1 i am running every night and which insert approx 500,000
> records into TESTTABLE..Now I have a very simple function on TESTTABLE
> which is as following..which i use in other store procedures...
> CREATE FUNCTION TestFunction
> (@.ID as INT,@.dt1 datetime,@.dt2 datetime)
> returns money
> AS
> BEGIN
> DECLARE @.retmoney money
> SELECT @.retmoney = sum(amount)
> FROM TESTTABLE
> WHERE transactiondate between @.dt1 and @.dt2 and id = @.Id
> and categoryid not in ('1','2')
> RETURN @.retmoney
> END
>
> so what happen after running StoreProc1 every night...(which insert into
> 500 K records into TESTTABLE.. My function TestFunction becomes so slow..
> it
> takes 10 second to run and if i run query of that function
> SELECT @.retmoney = sum(amount)
> FROM TESTTABLE
> WHERE createddate between @.dt1 and @.dt2 and id = @.Id
> and categoryid not in ('1','2')
> it get execute in only o seconds...
> so why if i run that function it takes long and if i run that same query
> it
> is fast...
> Pls let me know.
>
CREATE PROCEDURE StoreProc1
AS
DECLARE testcursor CURSOR FOR
SELECT col1
FROM table
WHERE Id = @.ID
OPEN testcursor
FETCH NEXT FROM cursor INTO @.col1
WHILE @.@.FETCH_STATUS = 0
BEGIN
--Here i have to use cursor because i am doing some calculation
--here based value of co11
--And then insert into one table
INSERT INTO TESTTABLE
(id,transactiondate...) values (@.value1,@.value2......)
FETCH NEXT FROM testcursor INTO @.col1
END
CLOSE testcursor
DEALLOCATE testcursor
This StoreProc1 i am running every night and which insert approx 500,000
records into TESTTABLE..Now I have a very simple function on TESTTABLE
which is as following..which i use in other store procedures...
CREATE FUNCTION TestFunction
(@.ID as INT,@.dt1 datetime,@.dt2 datetime)
returns money
AS
BEGIN
DECLARE @.retmoney money
SELECT @.retmoney = sum(amount)
FROM TESTTABLE
WHERE transactiondate between @.dt1 and @.dt2 and id = @.Id
and categoryid not in ('1','2')
RETURN @.retmoney
END
so what happen after running StoreProc1 every night...(which insert into
500 K records into TESTTABLE.. My function TestFunction becomes so slow.. it
takes 10 second to run and if i run query of that function
SELECT @.retmoney = sum(amount)
FROM TESTTABLE
WHERE createddate between @.dt1 and @.dt2 and id = @.Id
and categoryid not in ('1','2')
it get execute in only o seconds...
so why if i run that function it takes long and if i run that same query it
is fast...
Pls let me know.Hi
You may have perform maintainance on this table to update the indexes or
statistics as they could be fragmented or out of date. See DBCC SHOWCONTIG,
DBCC DBREINDEX and UPDATE STATISTICS in books online
John
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:6360AC44-D1D9-4D05-91B3-201C58E1B543@.microsoft.com...
>I do have one store procedure which does insert into one table
> CREATE PROCEDURE StoreProc1
> AS
> DECLARE testcursor CURSOR FOR
> SELECT col1
> FROM table
> WHERE Id = @.ID
> OPEN testcursor
> FETCH NEXT FROM cursor INTO @.col1
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> --Here i have to use cursor because i am doing some calculation
> --here based value of co11
> --And then insert into one table
> INSERT INTO TESTTABLE
> (id,transactiondate...) values (@.value1,@.value2......)
> FETCH NEXT FROM testcursor INTO @.col1
> END
> CLOSE testcursor
> DEALLOCATE testcursor
> This StoreProc1 i am running every night and which insert approx 500,000
> records into TESTTABLE..Now I have a very simple function on TESTTABLE
> which is as following..which i use in other store procedures...
> CREATE FUNCTION TestFunction
> (@.ID as INT,@.dt1 datetime,@.dt2 datetime)
> returns money
> AS
> BEGIN
> DECLARE @.retmoney money
> SELECT @.retmoney = sum(amount)
> FROM TESTTABLE
> WHERE transactiondate between @.dt1 and @.dt2 and id = @.Id
> and categoryid not in ('1','2')
> RETURN @.retmoney
> END
>
> so what happen after running StoreProc1 every night...(which insert into
> 500 K records into TESTTABLE.. My function TestFunction becomes so slow..
> it
> takes 10 second to run and if i run query of that function
> SELECT @.retmoney = sum(amount)
> FROM TESTTABLE
> WHERE createddate between @.dt1 and @.dt2 and id = @.Id
> and categoryid not in ('1','2')
> it get execute in only o seconds...
> so why if i run that function it takes long and if i run that same query
> it
> is fast...
> Pls let me know.
>
Function is 10 times slower than SP
Hi all,
In order to return a table for a specific input parameter, I am using Function, but the performance is just awful! After I have tried same code as SP, the whole thing is running under 1 sec (like 0.5 sec), while the function is about 10 times slow (4-6 sec). I know in SQL 2000 function is slower than SP, but that cannot be as bad as 10 times slower.
Now, in order to use that table from SP, I have to create a temp table, then insert result into that temp table, before I can direct use any "select" sentence. Any explanation here? Or how to "select" from a SP directly?
Thanks,
Ning
It is very difficult for us to attempt to help you with being able to see the code.
You may wish to consider posting the procedure code and maybe someone here will help create an efficient TVF (table valued function.)
|||As you said you have to insert the sp output to temp table to use in select statement ... you can not use sp in select statement... you are rightly mentioned that function used to be more or less slower than sp... also check whether all the parameters used in sp are used in function also...
Madhu
Friday, February 24, 2012
Full-Text Search Query Question - Performance
I have a table with 3M rows that contains a varchar(2000) field with
various keywords. Here is the table structure:
various keywords. Here is the table structure:
PKColumn
ImageID
FullTextColumn
There is an association table:
ImageID
ContractID
Now, I want to do a query where the ContractID = x and Contains some
word in the FullTextColumn. There is an association table that maps
Images to Contracts - so I can't use the trick of putting the Contract
code in the FullTextColumn.
I'm finding that first the FTS service is performing a search on the
Keyword (which can take a long time if 100K rows are returned) then
joining to the association table for the particular contract.
Is there anyway to make this faster by telling the FTS service, only
search this subset of rows for the keyword based on the contract.
Sorry if this sounds convoluted. Appreciate any help you can suggest.
Thanks!jimdandy@.shaw.ca (Jim Dandy) wrote in message news:<705c8539.0405271024.5ce1d19b@.posting.google.com>...
> I have a table with 3M rows that contains a varchar(2000) field with
> various keywords. Here is the table structure:
> PKColumn
> ImageID
> FullTextColumn
> There is an association table:
> ImageID
> ContractID
> Now, I want to do a query where the ContractID = x and Contains some
> word in the FullTextColumn. There is an association table that maps
> Images to Contracts - so I can't use the trick of putting the Contract
> code in the FullTextColumn.
> I'm finding that first the FTS service is performing a search on the
> Keyword (which can take a long time if 100K rows are returned) then
> joining to the association table for the particular contract.
> Is there anyway to make this faster by telling the FTS service, only
> search this subset of rows for the keyword based on the contract.
> Sorry if this sounds convoluted. Appreciate any help you can suggest.
> Thanks!
You might want to post this in microsoft.public.sqlserver.fulltext to
see if you get a better reply.
Simon
Sunday, February 19, 2012
Full-Text Search - performance cost?
I am conducting performance tuning & optimization of a SQL2K5 database. The server has Full-Text Search Service enabled and running. However, I don't believe the applications are actually using this Service and I am considering disabling it to conserve resources since apps are performing high transactions that are consuming significant resources (memory, CPU).
How much of a performance cost does Full-Text search service impose on the server and is this a recommended way of conserving resource consumption if the Service is not being used or needed by the application?
I haven't found much in Technet, KB or MSDN to address this topic.
Thanks,
Phil
If the FTS is not being used then you must disable it in order to get the system resources back on track.
One thing is sure that FTS has been optimized in SQL 2005 as compared to SQL 2000 version as it refers http://msdn2.microsoft.com/en-us/library/ms345119.aspx here. IMHO you need to disable if you are not using at all on the application.
Subscribe to:
Posts (Atom)