I am working at a client site with an accounting database, and we are dealing with a simple query:
select count(*)
from Contract
inner join Charge on Contract.ContractID = Charge.ContractID
Oddly, when we run this on our production server it takes 60 to 90 seconds to complete, but on our test server it completes in 2 seconds.
The problem becomes worse with more complex procedures, such as our statement run that can exceed seven hours on the production server but completes in 45 minutes on our test server.
Our test server is an exact restore of our production server.
Contract and Charge are both indexed on ContractID, and each has about 500,000 rows.
The execution plans on both servers are identical, the longest step being a hash-match/Inner-join between the two tables (probably a result of the cardinality of ContractID in the Charge table).
Our production server is more powerful than our test server, with quad-processors and 3.5 gigs of RAM, while our test server has only 2 gigs. Both servers are set to dynamically configure memory, and I haven't seen our production server's memory use top 1.8 gigs.
Running trace through query analyzer shows one discrepancy: out test server performs only 24 reads to execute the query, while our productions server requires 220.
The problem occurs on our production server regardless of whether other users are using the system.
I have a couple questions:
1) Any ideas on why the query would run so much slower on our production server?
2) Would running SQL Profiler give me any additional information, and if so what settings should I trace (I've only used profiler once or twice). Also, is there anything I should be concerned about in running profiler on a production system? I've heard that it can have some impact on performance.
3) As part of the execution plan the optimizer performs a step called Bitmap/Bitmap Create prior to the hash. I can't find any documentation on this in Books Online or on Microsquashes website. Anybody know anything about it?
Thanks!
blindmanRunning trace through query analyzer shows one discrepancy: out test server performs only 24 reads to execute the query, while our productions server requires 220.
--------------
Check indexes for fragmentation.|||I tried dropping and recreating the indexes, to no effect.
I also just tried setting ContractID as the clustered index on the Charge table to see if that would change the execution plan, but it had no effect.
I am suspecting that there might be performance or contention issues with the drive, though supposedly there are no other services running on the serve besides MS SQL.
blindman|||Originally posted by blindman
I tried dropping and recreating the indexes, to no effect.
I also just tried setting ContractID as the clustered index on the Charge table to see if that would change the execution plan, but it had no effect.
I am suspecting that there might be performance or contention issues with the drive, though supposedly there are no other services running on the serve besides MS SQL.
blindman
Try to use performance monitor - very useful thing. Compare results for servers.|||uhhh...bounce the box?
This is MS BTW
Can you be down for a couple?
What's in the Error Log?
Did you do DBCC CHECKDB?|||blocking ?
Originally posted by blindman
I am working at a client site with an accounting database, and we are dealing with a simple query:
select count(*)
from Contract
inner join Charge on Contract.ContractID = Charge.ContractID
Oddly, when we run this on our production server it takes 60 to 90 seconds to complete, but on our test server it completes in 2 seconds.
The problem becomes worse with more complex procedures, such as our statement run that can exceed seven hours on the production server but completes in 45 minutes on our test server.
Our test server is an exact restore of our production server.
Contract and Charge are both indexed on ContractID, and each has about 500,000 rows.
The execution plans on both servers are identical, the longest step being a hash-match/Inner-join between the two tables (probably a result of the cardinality of ContractID in the Charge table).
Our production server is more powerful than our test server, with quad-processors and 3.5 gigs of RAM, while our test server has only 2 gigs. Both servers are set to dynamically configure memory, and I haven't seen our production server's memory use top 1.8 gigs.
Running trace through query analyzer shows one discrepancy: out test server performs only 24 reads to execute the query, while our productions server requires 220.
The problem occurs on our production server regardless of whether other users are using the system.
I have a couple questions:
1) Any ideas on why the query would run so much slower on our production server?
2) Would running SQL Profiler give me any additional information, and if so what settings should I trace (I've only used profiler once or twice). Also, is there anything I should be concerned about in running profiler on a production system? I've heard that it can have some impact on performance.
3) As part of the execution plan the optimizer performs a step called Bitmap/Bitmap Create prior to the hash. I can't find any documentation on this in Books Online or on Microsquashes website. Anybody know anything about it?
Thanks!
blindman|||Bouncing the server did speed up the processing the other day, but the performance quickly degraded again.
Problem with buffer pages, perhaps?
Nothing in the Error Log.
DBCC checks done regularly, and databases are spittin' images of eachother.
No blocks detected, and problem occurs regardless of whether other users are logged in.
blindman|||How big are the tranny logs?
How about the allocation to tempdb?
Is it a high level of OLTP?
But..
The execution plans on both servers are identical, the longest step being a hash-match/Inner-join between the two tables (probably a result of the cardinality of ContractID in the Charge table).
Should be an Index Scan...shouldn't it?
And 45 minutes is a long time....(7 hours is an eternity)...
Can it be the network? Are you executing localy on dev and remotley to PROD (even that shouldn't matter)
I'll keep thinking...
You can try the brain trust at:
http://www.sqlteam.com/Default.asp|||I know SQL Server is supposed to be doing this on its own every now and then, but what happens when you run
update statistics table with fullscan
on both tables? Kind of reaching here, because the test database is a restore of the other (and therefore identical). How does the hardware stack up?|||How does the hardware stack up?
...And if I read the REST of the post... Disregard that last bit, Blindman.
Showing posts with label performance. Show all posts
Showing posts with label performance. Show all posts
Friday, March 23, 2012
Query performance with order by clause?
Hi all,
Just wondering if anyone can tell me if an order by clause on a select
query would have any impact on the time it takes to retrieve results?
Essentially I'm selecting Top 1 out of a table via various criteria
and currently getting it back without an order by clause. The order by
would only include the column that has the clustered primary index on
it.
Can anyone tell me if in theory this will slow the query down?
Many thanks in advance!
Much warmth,
MurrauM Wells (planetquirky@.planetthoughtful.org) writes:
> Just wondering if anyone can tell me if an order by clause on a select
> query would have any impact on the time it takes to retrieve results?
> Essentially I'm selecting Top 1 out of a table via various criteria
> and currently getting it back without an order by clause. The order by
> would only include the column that has the clustered primary index on
> it.
> Can anyone tell me if in theory this will slow the query down?
For most situations this is an uninteresting question. TOP 1 with an
ORDER BY means "give me one row, I don't care which", but it's not good
for a random selection.
So if you need your row to be deterministically selected, then you must
have an ORDER BY clause.
The cost for the ORDER BY clause is likely to be marginal, if the order
by columns agrees with the clustered index.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
query performance with large tables
Hi,
I am currently facing two problems: one general and one more specific.
My general issue is improving the performance of queries involving a very
large table. I know that the most efficient optimization is to create indexes
that match the type of queries I run. Are there any other solutions? Can I
gain from splitting the table into several smaller ones?
The more specific problem is related to index creation. I am trying to
create an index that makes sense, but SQL Server 2005 gives a timeout error
after less than a minute. I suppose this is due to the size of the table and
cost of clustering (I set a primary key) but I can't find in BOL how to
increase the timeout value...
Any ideas regarding any of these two questions?
If you have proper indexes you should not need to split the table. You can
partition the table but that will not help you if you still don't have
proper indexes.
Don't use the gui to create the index. Use the query Editor and issue a
CREATE INDEX statement instead. The editor defaults to 0 timeout so it will
stay connected as long as it needs to.
Andrew J. Kelly SQL MVP
"Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
news:2A0250DC-54ED-4FDC-A377-3D5F72BFEB1F@.microsoft.com...
> Hi,
> I am currently facing two problems: one general and one more specific.
> My general issue is improving the performance of queries involving a very
> large table. I know that the most efficient optimization is to create
> indexes
> that match the type of queries I run. Are there any other solutions? Can I
> gain from splitting the table into several smaller ones?
> The more specific problem is related to index creation. I am trying to
> create an index that makes sense, but SQL Server 2005 gives a timeout
> error
> after less than a minute. I suppose this is due to the size of the table
> and
> cost of clustering (I set a primary key) but I can't find in BOL how to
> increase the timeout value...
> Any ideas regarding any of these two questions?
>
I am currently facing two problems: one general and one more specific.
My general issue is improving the performance of queries involving a very
large table. I know that the most efficient optimization is to create indexes
that match the type of queries I run. Are there any other solutions? Can I
gain from splitting the table into several smaller ones?
The more specific problem is related to index creation. I am trying to
create an index that makes sense, but SQL Server 2005 gives a timeout error
after less than a minute. I suppose this is due to the size of the table and
cost of clustering (I set a primary key) but I can't find in BOL how to
increase the timeout value...
Any ideas regarding any of these two questions?
If you have proper indexes you should not need to split the table. You can
partition the table but that will not help you if you still don't have
proper indexes.
Don't use the gui to create the index. Use the query Editor and issue a
CREATE INDEX statement instead. The editor defaults to 0 timeout so it will
stay connected as long as it needs to.
Andrew J. Kelly SQL MVP
"Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
news:2A0250DC-54ED-4FDC-A377-3D5F72BFEB1F@.microsoft.com...
> Hi,
> I am currently facing two problems: one general and one more specific.
> My general issue is improving the performance of queries involving a very
> large table. I know that the most efficient optimization is to create
> indexes
> that match the type of queries I run. Are there any other solutions? Can I
> gain from splitting the table into several smaller ones?
> The more specific problem is related to index creation. I am trying to
> create an index that makes sense, but SQL Server 2005 gives a timeout
> error
> after less than a minute. I suppose this is due to the size of the table
> and
> cost of clustering (I set a primary key) but I can't find in BOL how to
> increase the timeout value...
> Any ideas regarding any of these two questions?
>
query performance with large tables
Hi,
I am currently facing two problems: one general and one more specific.
My general issue is improving the performance of queries involving a very
large table. I know that the most efficient optimization is to create indexe
s
that match the type of queries I run. Are there any other solutions? Can I
gain from splitting the table into several smaller ones?
The more specific problem is related to index creation. I am trying to
create an index that makes sense, but SQL Server 2005 gives a timeout error
after less than a minute. I suppose this is due to the size of the table and
cost of clustering (I set a primary key) but I can't find in BOL how to
increase the timeout value...
Any ideas regarding any of these two questions?If you have proper indexes you should not need to split the table. You can
partition the table but that will not help you if you still don't have
proper indexes.
Don't use the gui to create the index. Use the query Editor and issue a
CREATE INDEX statement instead. The editor defaults to 0 timeout so it will
stay connected as long as it needs to.
Andrew J. Kelly SQL MVP
"Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
news:2A0250DC-54ED-4FDC-A377-3D5F72BFEB1F@.microsoft.com...
> Hi,
> I am currently facing two problems: one general and one more specific.
> My general issue is improving the performance of queries involving a very
> large table. I know that the most efficient optimization is to create
> indexes
> that match the type of queries I run. Are there any other solutions? Can I
> gain from splitting the table into several smaller ones?
> The more specific problem is related to index creation. I am trying to
> create an index that makes sense, but SQL Server 2005 gives a timeout
> error
> after less than a minute. I suppose this is due to the size of the table
> and
> cost of clustering (I set a primary key) but I can't find in BOL how to
> increase the timeout value...
> Any ideas regarding any of these two questions?
>sql
I am currently facing two problems: one general and one more specific.
My general issue is improving the performance of queries involving a very
large table. I know that the most efficient optimization is to create indexe
s
that match the type of queries I run. Are there any other solutions? Can I
gain from splitting the table into several smaller ones?
The more specific problem is related to index creation. I am trying to
create an index that makes sense, but SQL Server 2005 gives a timeout error
after less than a minute. I suppose this is due to the size of the table and
cost of clustering (I set a primary key) but I can't find in BOL how to
increase the timeout value...
Any ideas regarding any of these two questions?If you have proper indexes you should not need to split the table. You can
partition the table but that will not help you if you still don't have
proper indexes.
Don't use the gui to create the index. Use the query Editor and issue a
CREATE INDEX statement instead. The editor defaults to 0 timeout so it will
stay connected as long as it needs to.
Andrew J. Kelly SQL MVP
"Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
news:2A0250DC-54ED-4FDC-A377-3D5F72BFEB1F@.microsoft.com...
> Hi,
> I am currently facing two problems: one general and one more specific.
> My general issue is improving the performance of queries involving a very
> large table. I know that the most efficient optimization is to create
> indexes
> that match the type of queries I run. Are there any other solutions? Can I
> gain from splitting the table into several smaller ones?
> The more specific problem is related to index creation. I am trying to
> create an index that makes sense, but SQL Server 2005 gives a timeout
> error
> after less than a minute. I suppose this is due to the size of the table
> and
> cost of clustering (I set a primary key) but I can't find in BOL how to
> increase the timeout value...
> Any ideas regarding any of these two questions?
>sql
query performance with large tables
Hi,
I am currently facing two problems: one general and one more specific.
My general issue is improving the performance of queries involving a very
large table. I know that the most efficient optimization is to create indexes
that match the type of queries I run. Are there any other solutions? Can I
gain from splitting the table into several smaller ones?
The more specific problem is related to index creation. I am trying to
create an index that makes sense, but SQL Server 2005 gives a timeout error
after less than a minute. I suppose this is due to the size of the table and
cost of clustering (I set a primary key) but I can't find in BOL how to
increase the timeout value...
Any ideas regarding any of these two questions?If you have proper indexes you should not need to split the table. You can
partition the table but that will not help you if you still don't have
proper indexes.
Don't use the gui to create the index. Use the query Editor and issue a
CREATE INDEX statement instead. The editor defaults to 0 timeout so it will
stay connected as long as it needs to.
Andrew J. Kelly SQL MVP
"Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
news:2A0250DC-54ED-4FDC-A377-3D5F72BFEB1F@.microsoft.com...
> Hi,
> I am currently facing two problems: one general and one more specific.
> My general issue is improving the performance of queries involving a very
> large table. I know that the most efficient optimization is to create
> indexes
> that match the type of queries I run. Are there any other solutions? Can I
> gain from splitting the table into several smaller ones?
> The more specific problem is related to index creation. I am trying to
> create an index that makes sense, but SQL Server 2005 gives a timeout
> error
> after less than a minute. I suppose this is due to the size of the table
> and
> cost of clustering (I set a primary key) but I can't find in BOL how to
> increase the timeout value...
> Any ideas regarding any of these two questions?
>
I am currently facing two problems: one general and one more specific.
My general issue is improving the performance of queries involving a very
large table. I know that the most efficient optimization is to create indexes
that match the type of queries I run. Are there any other solutions? Can I
gain from splitting the table into several smaller ones?
The more specific problem is related to index creation. I am trying to
create an index that makes sense, but SQL Server 2005 gives a timeout error
after less than a minute. I suppose this is due to the size of the table and
cost of clustering (I set a primary key) but I can't find in BOL how to
increase the timeout value...
Any ideas regarding any of these two questions?If you have proper indexes you should not need to split the table. You can
partition the table but that will not help you if you still don't have
proper indexes.
Don't use the gui to create the index. Use the query Editor and issue a
CREATE INDEX statement instead. The editor defaults to 0 timeout so it will
stay connected as long as it needs to.
Andrew J. Kelly SQL MVP
"Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
news:2A0250DC-54ED-4FDC-A377-3D5F72BFEB1F@.microsoft.com...
> Hi,
> I am currently facing two problems: one general and one more specific.
> My general issue is improving the performance of queries involving a very
> large table. I know that the most efficient optimization is to create
> indexes
> that match the type of queries I run. Are there any other solutions? Can I
> gain from splitting the table into several smaller ones?
> The more specific problem is related to index creation. I am trying to
> create an index that makes sense, but SQL Server 2005 gives a timeout
> error
> after less than a minute. I suppose this is due to the size of the table
> and
> cost of clustering (I set a primary key) but I can't find in BOL how to
> increase the timeout value...
> Any ideas regarding any of these two questions?
>
query performance tuning
Hi
Where can I find good resources re: peformance tuning of queries / indexes ,
using EXPLAIN etc ?
Thanks
BruceBecause you have EXPLAIN in capitals I am going to presume you either
1. Want the SQL Server equivalent to Oracle's EXPLAIN PLAN
2. Have the wrong Newsgroup
Presuming #1 you can look at.
SET SHOWPLAN_ALL ON
once you have that then you can start to look to articles like this
http://www.sql-server-performance.com/query_execution_plan_analysis.asp
or anything on the subject by Kalen Delaney.
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Bruce Baker" <bruceb@.ardex.com.au> wrote in message
news:eWvSyqJuDHA.2448@.TK2MSFTNGP12.phx.gbl...
> Hi
> Where can I find good resources re: peformance tuning of queries / indexes
,
> using EXPLAIN etc ?
> Thanks
> Bruce
>|||www.sql-server-performance.com will be a good resource.
Suresh
>--Original Message--
>Because you have EXPLAIN in capitals I am going to
presume you either
>1. Want the SQL Server equivalent to Oracle's EXPLAIN
PLAN
>2. Have the wrong Newsgroup
>
>Presuming #1 you can look at.
>SET SHOWPLAN_ALL ON
>once you have that then you can start to look to articles
like this
>http://www.sql-server-
performance.com/query_execution_plan_analysis.asp
>or anything on the subject by Kalen Delaney.
>--
>--
>Allan Mitchell (Microsoft SQL Server MVP)
>MCSE,MCDBA
>www.SQLDTS.com
>I support PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org
>"Bruce Baker" <bruceb@.ardex.com.au> wrote in message
>news:eWvSyqJuDHA.2448@.TK2MSFTNGP12.phx.gbl...
>> Hi
>> Where can I find good resources re: peformance tuning
of queries / indexes
>,
>> using EXPLAIN etc ?
>> Thanks
>> Bruce
>>
>
>.
>
Where can I find good resources re: peformance tuning of queries / indexes ,
using EXPLAIN etc ?
Thanks
BruceBecause you have EXPLAIN in capitals I am going to presume you either
1. Want the SQL Server equivalent to Oracle's EXPLAIN PLAN
2. Have the wrong Newsgroup
Presuming #1 you can look at.
SET SHOWPLAN_ALL ON
once you have that then you can start to look to articles like this
http://www.sql-server-performance.com/query_execution_plan_analysis.asp
or anything on the subject by Kalen Delaney.
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Bruce Baker" <bruceb@.ardex.com.au> wrote in message
news:eWvSyqJuDHA.2448@.TK2MSFTNGP12.phx.gbl...
> Hi
> Where can I find good resources re: peformance tuning of queries / indexes
,
> using EXPLAIN etc ?
> Thanks
> Bruce
>|||www.sql-server-performance.com will be a good resource.
Suresh
>--Original Message--
>Because you have EXPLAIN in capitals I am going to
presume you either
>1. Want the SQL Server equivalent to Oracle's EXPLAIN
PLAN
>2. Have the wrong Newsgroup
>
>Presuming #1 you can look at.
>SET SHOWPLAN_ALL ON
>once you have that then you can start to look to articles
like this
>http://www.sql-server-
performance.com/query_execution_plan_analysis.asp
>or anything on the subject by Kalen Delaney.
>--
>--
>Allan Mitchell (Microsoft SQL Server MVP)
>MCSE,MCDBA
>www.SQLDTS.com
>I support PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org
>"Bruce Baker" <bruceb@.ardex.com.au> wrote in message
>news:eWvSyqJuDHA.2448@.TK2MSFTNGP12.phx.gbl...
>> Hi
>> Where can I find good resources re: peformance tuning
of queries / indexes
>,
>> using EXPLAIN etc ?
>> Thanks
>> Bruce
>>
>
>.
>
Query Performance SQL Server7.0 with SP2
Hi Gurus
I m using SQL Server7.0 with SP2 on Compaq Prolient ML310 PIV 2.2Ghz
Now the problem is when i m executing the "select * statment " on a table with 21000 rows it is taking ard 1min to result for the same.and if i m using the same query without any service pack the result is coming in 4-5 Secs. but with the base system ( without service pack) my system get freeze frequently.
REPLY URGENT
TIASunil
I'd recommend you to apply SP3.
Do you have WHERE clause in your query?
Do you really need all columns from the table (I mean why SELECT * )?
"Sunil Dara" <anonymous@.discussions.microsoft.com> wrote in message
news:9B88D9D5-67A9-467C-BF9F-A7DFDFE32688@.microsoft.com...
> Hi Gurus
> I m using SQL Server7.0 with SP2 on Compaq Prolient ML310 PIV 2.2Ghz
> Now the problem is when i m executing the "select * statment " on a table
with 21000 rows it is taking ard 1min to result for the same.and if i m
using the same query without any service pack the result is coming in 4-5
Secs. but with the base system ( without service pack) my system get freeze
frequently.
> REPLY URGENT
> TIA
I m using SQL Server7.0 with SP2 on Compaq Prolient ML310 PIV 2.2Ghz
Now the problem is when i m executing the "select * statment " on a table with 21000 rows it is taking ard 1min to result for the same.and if i m using the same query without any service pack the result is coming in 4-5 Secs. but with the base system ( without service pack) my system get freeze frequently.
REPLY URGENT
TIASunil
I'd recommend you to apply SP3.
Do you have WHERE clause in your query?
Do you really need all columns from the table (I mean why SELECT * )?
"Sunil Dara" <anonymous@.discussions.microsoft.com> wrote in message
news:9B88D9D5-67A9-467C-BF9F-A7DFDFE32688@.microsoft.com...
> Hi Gurus
> I m using SQL Server7.0 with SP2 on Compaq Prolient ML310 PIV 2.2Ghz
> Now the problem is when i m executing the "select * statment " on a table
with 21000 rows it is taking ard 1min to result for the same.and if i m
using the same query without any service pack the result is coming in 4-5
Secs. but with the base system ( without service pack) my system get freeze
frequently.
> REPLY URGENT
> TIA
Query Performance SQL 7.0 SP2 / SP4
Hi Gurus
I m using SQL Server7.0 with SP2 on Compaq Prolient ML310 PIV 2.2Ghz
Now the problem is when i m executing the "select * statment " on a table with 21000 rows it is taking ard 1min to result for the same.and if i m using the same query without any service pack the result is coming in 4-5 Secs. but with the base system ( without service pack) my system get freeze frequently.
REPLY URGENT
TIAHave you tried downloading the latest service pack? (SP4 for 7.0 I believe)
What changed between having the sp and not having it?|||Originally posted by rhigdon
Have you tried downloading the latest service pack? (SP4 for 7.0 I believe)
What changed between having the sp and not having it?
I have checked with the SP4 even , but the back to square one.sql
I m using SQL Server7.0 with SP2 on Compaq Prolient ML310 PIV 2.2Ghz
Now the problem is when i m executing the "select * statment " on a table with 21000 rows it is taking ard 1min to result for the same.and if i m using the same query without any service pack the result is coming in 4-5 Secs. but with the base system ( without service pack) my system get freeze frequently.
REPLY URGENT
TIAHave you tried downloading the latest service pack? (SP4 for 7.0 I believe)
What changed between having the sp and not having it?|||Originally posted by rhigdon
Have you tried downloading the latest service pack? (SP4 for 7.0 I believe)
What changed between having the sp and not having it?
I have checked with the SP4 even , but the back to square one.sql
query performance question regarding ISNULL
I have a sql statement:
SELECT A1.C_JOBBIDID, A2.ACCOUNTID A2_ACCOUNTID, A1.PROD_PR_GRP_C, A1.WON,
A2.DESCRIPTION A2_DESCRIPTION, A1.OPPORTUNITYID, A3.USERFIELD5 A3_USERFIELD5
,
A2.STATUS A2_STATUS, A1.SPEC_PR_PROD_C, A1.SPR_ITEM_ALLOW_Q, A1.DISTPRICE1ST
,
A1.SUBPRICE1ST, A1.EXPDATE1ST, A1.DISTPRICE2ND, A1.SUBPRICE2ND, A1.EXPDATE2N
D,
A1.DISTPRICE3RD, A1.SUBPRICE3RD, A1.EXPDATE3RD, A1.DISTPRICE4TH, A1.SUBPRICE
4TH,
A1.EXPDATE4TH
FROM C_JOBBID A1 INNER JOIN OPPORTUNITY A2 ON (A1.OPPORTUNITYID = A2.OPPORTU
NITYID)
INNER JOIN C_OPPORTUNITY_EXT A3 ON (A2.OPPORTUNITYID = A3.OPPORTUNITYID)
INNER JOIN C_OPPTOACCOUNT A4 ON (A4.OPPORTUNITYID = A1.OPPORTUNITYID)
WHERE A4.ACCOUNTID = 'A6UJ9A0069NH'
AND A3.METRO IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE USERID = 'ADMIN
')
AND A1.PROD_PR_GRP_C = 'C167'
ORDER BY A1.OPPORTUNITYID ASC, A1.SPEC_PR_PROD_C ASC, A1.EXPDATE1ST ASC
This query runs slower than:
SELECT A1.C_JOBBIDID, A2.ACCOUNTID A2_ACCOUNTID, A1.PROD_PR_GRP_C, A1.WON,
A2.DESCRIPTION A2_DESCRIPTION, A1.OPPORTUNITYID, A3.USERFIELD5 A3_USERFIELD5
,
A2.STATUS A2_STATUS, A1.SPEC_PR_PROD_C, A1.SPR_ITEM_ALLOW_Q, A1.DISTPRICE1ST
,
A1.SUBPRICE1ST, A1.EXPDATE1ST, A1.DISTPRICE2ND, A1.SUBPRICE2ND, A1.EXPDATE2N
D,
A1.DISTPRICE3RD, A1.SUBPRICE3RD, A1.EXPDATE3RD, A1.DISTPRICE4TH, A1.SUBPRICE
4TH,
A1.EXPDATE4TH
FROM C_JOBBID A1 INNER JOIN OPPORTUNITY A2 ON (A1.OPPORTUNITYID = A2.OPPORTU
NITYID)
INNER JOIN C_OPPORTUNITY_EXT A3 ON (A2.OPPORTUNITYID = A3.OPPORTUNITYID)
INNER JOIN C_OPPTOACCOUNT A4 ON (A4.OPPORTUNITYID = A1.OPPORTUNITYID)
WHERE A4.ACCOUNTID = 'A6UJ9A0069NH'
AND ISNULL(A3.METRO, '') IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE
USERID = 'ADMIN')
AND A1.PROD_PR_GRP_C = 'C167'
ORDER BY A1.OPPORTUNITYID ASC, A1.SPEC_PR_PROD_C ASC, A1.EXPDATE1ST ASC
As you can see, the only difference is checking the field for null and conve
rting
it to a blank string. You'd think the second one involving "more work" woul
d
take longer. Can someone explain why this is so?
Thanks in advance.
Jiho Han
jihohan@.yahoo.comWhat do you mean, slower? Considerably, or minimally? Can you check the
plan, or post the plan using SET SHOWPLAN_TEXT ON? It will probably reveal
the reason if it is considerable.
You might also try rewriting
> AND ISNULL(A3.METRO, '') IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE
> USERID = 'ADMIN')
as
AND EXISTS ( SELECT 1
FROM C_USERMETROS
WHERE C_USERMETROS.USERID = 'ADMIN'
AND PRICINGMETRO = A3.METRO)
assuming that there is not a A3.METRO that actually equals ''
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Jiho Han" <jihohan@.yahoo.com> wrote in message
news:a19ab9b65ef08c806917750fbb2@.msnews.microsoft.com...
>I have a sql statement:
> SELECT A1.C_JOBBIDID, A2.ACCOUNTID A2_ACCOUNTID, A1.PROD_PR_GRP_C, A1.WON,
> A2.DESCRIPTION A2_DESCRIPTION, A1.OPPORTUNITYID, A3.USERFIELD5
> A3_USERFIELD5,
> A2.STATUS A2_STATUS, A1.SPEC_PR_PROD_C, A1.SPR_ITEM_ALLOW_Q,
> A1.DISTPRICE1ST,
> A1.SUBPRICE1ST, A1.EXPDATE1ST, A1.DISTPRICE2ND, A1.SUBPRICE2ND,
> A1.EXPDATE2ND,
> A1.DISTPRICE3RD, A1.SUBPRICE3RD, A1.EXPDATE3RD, A1.DISTPRICE4TH,
> A1.SUBPRICE4TH, A1.EXPDATE4TH FROM C_JOBBID A1 INNER JOIN OPPORTUNITY A2
> ON (A1.OPPORTUNITYID = A2.OPPORTUNITYID)
> INNER JOIN C_OPPORTUNITY_EXT A3 ON (A2.OPPORTUNITYID = A3.OPPORTUNITYID)
> INNER JOIN C_OPPTOACCOUNT A4 ON (A4.OPPORTUNITYID = A1.OPPORTUNITYID)
> WHERE A4.ACCOUNTID = 'A6UJ9A0069NH' AND A3.METRO IN (SELECT PRICINGMETRO
> FROM C_USERMETROS WHERE USERID = 'ADMIN')
> AND A1.PROD_PR_GRP_C = 'C167'
> ORDER BY A1.OPPORTUNITYID ASC, A1.SPEC_PR_PROD_C ASC, A1.EXPDATE1ST ASC
> This query runs slower than:
> SELECT A1.C_JOBBIDID, A2.ACCOUNTID A2_ACCOUNTID, A1.PROD_PR_GRP_C, A1.WON,
> A2.DESCRIPTION A2_DESCRIPTION, A1.OPPORTUNITYID, A3.USERFIELD5
> A3_USERFIELD5,
> A2.STATUS A2_STATUS, A1.SPEC_PR_PROD_C, A1.SPR_ITEM_ALLOW_Q,
> A1.DISTPRICE1ST,
> A1.SUBPRICE1ST, A1.EXPDATE1ST, A1.DISTPRICE2ND, A1.SUBPRICE2ND,
> A1.EXPDATE2ND,
> A1.DISTPRICE3RD, A1.SUBPRICE3RD, A1.EXPDATE3RD, A1.DISTPRICE4TH,
> A1.SUBPRICE4TH, A1.EXPDATE4TH FROM C_JOBBID A1 INNER JOIN OPPORTUNITY A2
> ON (A1.OPPORTUNITYID = A2.OPPORTUNITYID)
> INNER JOIN C_OPPORTUNITY_EXT A3 ON (A2.OPPORTUNITYID = A3.OPPORTUNITYID)
> INNER JOIN C_OPPTOACCOUNT A4 ON (A4.OPPORTUNITYID = A1.OPPORTUNITYID)
> WHERE A4.ACCOUNTID = 'A6UJ9A0069NH' AND ISNULL(A3.METRO, '') IN (SELECT
> PRICINGMETRO FROM C_USERMETROS WHERE USERID = 'ADMIN') AND
> A1.PROD_PR_GRP_C = 'C167'
> ORDER BY A1.OPPORTUNITYID ASC, A1.SPEC_PR_PROD_C ASC, A1.EXPDATE1ST ASC
> As you can see, the only difference is checking the field for null and
> converting it to a blank string. You'd think the second one involving
> "more work" would take longer. Can someone explain why this is so?
> Thanks in advance.
> Jiho Han
> jihohan@.yahoo.com
>|||Hello Louis,
I am attaching the plan. It's the first time I've used that option so, I di
dn't know if this will be in the right format.
Also, I've tried the EXISTS approach and the result is same (I mean the perf
ormance). The plain option takes ~150ms(duration) vs ISNULL takes ~30ms. S
o it's not a huge difference but
it's significant enough that it's noticeable.
Thank you
Jiho Han
Senior Software Engineer
Infinity Info Systems
The Sales Technology Experts
Tel: 212.563.4400 x216
Fax: 212.760.0540
jhan@.infinityinfo.com
www.infinityinfo.com
> What do you mean, slower? Considerably, or minimally? Can you check
> the plan, or post the plan using SET SHOWPLAN_TEXT ON? It will
> probably reveal the reason if it is considerable.
>
> You might also try rewriting
>
> as
> AND EXISTS ( SELECT 1
> FROM C_USERMETROS
> WHERE C_USERMETROS.USERID = 'ADMIN'
> AND PRICINGMETRO = A3.METRO)
> assuming that there is not a A3.METRO that actually equals ''
>
> "Jiho Han" <jihohan@.yahoo.com> wrote in message
> news:a19ab9b65ef08c806917750fbb2@.msnews.microsoft.com...
>|||ISNULL (the function) is not equivalent to IS NULL (the syntax construct) in
SQL 2000 or 2005 to its optimizer.
You might try to use the latter since it is more completely supported.
Essentially, we're not reasoning about the output distribution on ISNULL().
That may cause suboptimal plans in complex queries.
Conor Cunningham
SQL Server Query Optimization Development Lead
"Jiho Han" <jihohan@.yahoo.com> wrote in message
news:a19ab9b661698c806aa7485348c@.msnews.microsoft.com...
Hello Louis,
I am attaching the plan. It's the first time I've used that option so, I
didn't know if this will be in the right format.
Also, I've tried the EXISTS approach and the result is same (I mean the
performance). The plain option takes ~150ms(duration) vs ISNULL takes
~30ms. So it's not a huge difference but it's significant enough that it's
noticeable.
Thank you
Jiho Han
Senior Software Engineer
Infinity Info Systems
The Sales Technology Experts
Tel: 212.563.4400 x216
Fax: 212.760.0540
jhan@.infinityinfo.com
www.infinityinfo.com
> What do you mean, slower? Considerably, or minimally? Can you check
> the plan, or post the plan using SET SHOWPLAN_TEXT ON? It will
> probably reveal the reason if it is considerable.
> You might also try rewriting
>
> as
> AND EXISTS ( SELECT 1
> FROM C_USERMETROS
> WHERE C_USERMETROS.USERID = 'ADMIN'
> AND PRICINGMETRO = A3.METRO)
> assuming that there is not a A3.METRO that actually equals ''
> "Jiho Han" <jihohan@.yahoo.com> wrote in message
> news:a19ab9b65ef08c806917750fbb2@.msnews.microsoft.com...
>|||Ok, that one just went over me completely.
I understand that ISNULL and IS NULL may not be equivalent - I would hope
not.
What I wanted to know was why
A3.METRO IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE USERID = 'ADMIN')
runs faster than
ISNULL(A3.METRO, '') IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE USERID
= 'ADMIN')
What I did notice when viewing the execution plan was that while using ISNUL
L
as above caused the query engine to use a hash match, the plain one caused
the engine to use a distinct sort followed by a nested loop.
I am very curious as to why it works this way and to determine whether I
need to employ ISNULL in all my other queries for query performance improvem
ent.
Thanks for your assistance!
Jiho Han
Senior Software Engineer
Infinity Info Systems
The Sales Technology Experts
Tel: 212.563.4400 x216
Fax: 212.760.0540
jhan@.infinityinfo.com
www.infinityinfo.com
> ISNULL (the function) is not equivalent to IS NULL (the syntax
> construct) in SQL 2000 or 2005 to its optimizer.
> You might try to use the latter since it is more completely supported.
> Essentially, we're not reasoning about the output distribution on
> ISNULL(). That may cause suboptimal plans in complex queries.
> Conor Cunningham
> SQL Server Query Optimization Development Lead
> "Jiho Han" <jihohan@.yahoo.com> wrote in message
> news:a19ab9b661698c806aa7485348c@.msnews.microsoft.com... Hello Louis,
> I am attaching the plan. It's the first time I've used that option
> so, I didn't know if this will be in the right format.
> Also, I've tried the EXISTS approach and the result is same (I mean
> the performance). The plain option takes ~150ms(duration) vs ISNULL
> takes ~30ms. So it's not a huge difference but it's significant
> enough that it's noticeable.
> Thank you
> Jiho Han
> Senior Software Engineer
> Infinity Info Systems
> The Sales Technology Experts
> Tel: 212.563.4400 x216
> Fax: 212.760.0540
> jhan@.infinityinfo.com
> www.infinityinfo.com|||It would be interesting to see what the execution plan would by if you
changed that portion of the query to
A3.METRIO IN (SLECT PRICINGMETRO FROM C_USERMETROS WHERE USERID = 'Admin'
AND PRICINGMETRO IS NOT NULL)
This is is just a wild guess, but maybe SQL is optimizing based on the fact
that ISNULL can't return a null result, and therefore can exclude null
results from the sub-query.
> What I wanted to know was why
> A3.METRO IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE USERID = 'ADMIN')
> runs faster than
> ISNULL(A3.METRO, '') IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE USERI
D
> = 'ADMIN')
> What I did notice when viewing the execution plan was that while using ISN
ULL
> as above caused the query engine to use a hash match, the plain one caused
> the engine to use a distinct sort followed by a nested loop.
> I am very curious as to why it works this way and to determine whether I
> need to employ ISNULL in all my other queries for query performance improvement.[/
color]|||Apologies - let me try to rephrase.
The query optimizer uses a tree model of operations that represent your SQL
statement. Additionally, statistical information (such as a histogram of
data for a column) is recorded for base tables and then pushed up through
the tree, being modified during each step. So, if you scan rows from a base
table and then filter, the filter operation would have an estimated output
distribution *after* the filter. This information is used to estimate
cardinality for each operator and to eventually cost various alternatives.
If the cardinality estimate is high, we may pick things like the hash join.
If it's low, we're more likely to pick a nested loops join. So, this
information is very important to picking an efficient plan.
Some constructs do not have full support in the optimizer. When we do not
have that information, we may not be able to come up with a good output
distribution for it, and thus the cardinality and cost may be incorrect.
ISNULL() is such an operator. When this happens, we may pick the loops join
when the hash join would have been better. This is when you see performance
issues.
In this case, you are using it to join with a subquery, and the distribution
is quite important here. I would recommend that you try to avoid this if
you are seeing plan issues. Once you start using the result of the function
is some other operation (a join, a subquery, a filter, etc), then I would
recommend that you consider avoiding it, if possible.
I hope that helps.
Thanks,
Conor
"Jiho Han" <jihohan@.yahoo.com> wrote in message
news:a19ab9b662c18c80742337e046d@.msnews.microsoft.com...
> Ok, that one just went over me completely.
> I understand that ISNULL and IS NULL may not be equivalent - I would hope
> not.
> What I wanted to know was why
> A3.METRO IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE USERID = 'ADMIN')
> runs faster than
> ISNULL(A3.METRO, '') IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE
> USERID = 'ADMIN')
> What I did notice when viewing the execution plan was that while using
> ISNULL as above caused the query engine to use a hash match, the plain one
> caused the engine to use a distinct sort followed by a nested loop.
> I am very curious as to why it works this way and to determine whether I
> need to employ ISNULL in all my other queries for query performance
> improvement.
> Thanks for your assistance!
> Jiho Han
> Senior Software Engineer
> Infinity Info Systems
> The Sales Technology Experts
> Tel: 212.563.4400 x216
> Fax: 212.760.0540
> jhan@.infinityinfo.com
> www.infinityinfo.com
>
>|||The option you specified came back slower actually. ~250 ms.
I've done several variations using IS NULL:
A3.METRO IS NOT NULL AND A3.METRO IN (SELECT PRICINGMETRO FROM C_USERMETROS
WHERE USERID = 'ADMIN')
A3.METRO IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE USERID = 'ADMIN'
AND PRICINGMETRO IS NOT NULL)
A3.METRO IS NOT NULL AND IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE
USERID = 'ADMIN' AND PRICINGMETRO IS NOT NULL)
They are all slower and are around ~250 ms. It seems to me that by adding
IS NOT NULL check, it's actually adding more work.
Nothing beats:
ISNULL(A3.METRO, '') IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE USERID
= 'ADMIN')
which consistently executes around ~30ms.
Jiho Han
Senior Software Engineer
Infinity Info Systems
The Sales Technology Experts
Tel: 212.563.4400 x216
Fax: 212.760.0540
jhan@.infinityinfo.com
www.infinityinfo.com
> It would be interesting to see what the execution plan would by if you
> changed that portion of the query to
> A3.METRIO IN (SLECT PRICINGMETRO FROM C_USERMETROS WHERE USERID =
> 'Admin' AND PRICINGMETRO IS NOT NULL)
> This is is just a wild guess, but maybe SQL is optimizing based on the
> fact that ISNULL can't return a null result, and therefore can exclude
> null results from the sub-query.
>|||Thanks Conor, that was very helpful although I can't say that I can absorb
everything you've said.
Just to be clear, and since I am wondering whether there is a misunderstandi
ng
here, I am reporting that the use of ISNULL() is performing better than the
lack of, or using IS NULL. If you see my other reply, you'll see some numbe
rs
I got trying to use IS NULL instead and they are all slower, even more so
than not using IS NULL.
Also, could you rephrase your last paragraph? Are you saying that I should
avoid using subqueries? or ISNULL()?
Are you suggesting that I use ISNULL() only in SELECT clause in your last
statement?
Thanks
Jiho
> Apologies - let me try to rephrase.
> The query optimizer uses a tree model of operations that represent
> your SQL statement. Additionally, statistical information (such as a
> histogram of data for a column) is recorded for base tables and then
> pushed up through the tree, being modified during each step. So, if
> you scan rows from a base table and then filter, the filter operation
> would have an estimated output distribution *after* the filter. This
> information is used to estimate cardinality for each operator and to
> eventually cost various alternatives. If the cardinality estimate is
> high, we may pick things like the hash join. If it's low, we're more
> likely to pick a nested loops join. So, this information is very
> important to picking an efficient plan.
> Some constructs do not have full support in the optimizer. When we do
> not have that information, we may not be able to come up with a good
> output distribution for it, and thus the cardinality and cost may be
> incorrect. ISNULL() is such an operator. When this happens, we may
> pick the loops join when the hash join would have been better. This
> is when you see performance issues.
> In this case, you are using it to join with a subquery, and the
> distribution is quite important here. I would recommend that you try
> to avoid this if you are seeing plan issues. Once you start using the
> result of the function is some other operation (a join, a subquery, a
> filter, etc), then I would recommend that you consider avoiding it, if
> possible.
> I hope that helps.
> Thanks,
> Conor
> "Jiho Han" <jihohan@.yahoo.com> wrote in message
> news:a19ab9b662c18c80742337e046d@.msnews.microsoft.com...
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:587012
Jiho Han wrote:
> Thanks Conor, that was very helpful although I can't say that I can absorb
> everything you've said.
> Just to be clear, and since I am wondering whether there is a misunderstan
ding
> here, I am reporting that the use of ISNULL() is performing better than th
e
> lack of, or using IS NULL. If you see my other reply, you'll see some num
bers
> I got trying to use IS NULL instead and they are all slower, even more so
> than not using IS NULL.
> Also, could you rephrase your last paragraph? Are you saying that I shoul
d
> avoid using subqueries? or ISNULL()?
> Are you suggesting that I use ISNULL() only in SELECT clause in your last
> statement?
> Thanks
> Jiho
I am not Conor, but I will answer it anyway.
The advice of Conor is to avoid using functions/expressions in
combination with subqueries, because the optimizer will have better
information if you only use the column.
However, if I understand you correctly, in your case, the optimizer is
picking a faster plan for the ISNULL() query. If I undestand you
correctly, the query
AND ISNULL(A3.METRO, '') IN (SELECT PRICINGMETRO FROM C_USERMETROS
WHERE USERID = 'ADMIN')
uses a hash match to finish in ~30ms
and the query
AND A3.METRO IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE USERID =
'ADMIN')
uses a distinct sort and nested loop to finish in ~150ms
So although the optimizer has better information for the second query,
it actually picks a query plan that performs worse. Aparently, in your
situation, the hash match is the fastest solution. Maybe the optimizer
is unable to accurately estimate the output of the subquery (maybe it
returns more distinct values than expected).
You could also try to rewrite it differently, and see if that helps.
For example, you could try this:
AND EXISTS (
SELECT *
FROM C_USERMETROS
WHERE USERID = 'ADMIN'
AND PRICINGMETRO = A3.METRO
)
If the subquery returns unique values for PRICINGMETRO, then you could
also try this:
INNER JOIN C_USERMETROS
ON USERID = 'ADMIN'
AND PRICINGMETRO = A3.METRO
If the optimizer is actually misjudging the result from the subquery,
then it could help if you added an index on C_USERMETROS (USERID,
PRICINGMETRO).
HTH,
Gert-Jan
SELECT A1.C_JOBBIDID, A2.ACCOUNTID A2_ACCOUNTID, A1.PROD_PR_GRP_C, A1.WON,
A2.DESCRIPTION A2_DESCRIPTION, A1.OPPORTUNITYID, A3.USERFIELD5 A3_USERFIELD5
,
A2.STATUS A2_STATUS, A1.SPEC_PR_PROD_C, A1.SPR_ITEM_ALLOW_Q, A1.DISTPRICE1ST
,
A1.SUBPRICE1ST, A1.EXPDATE1ST, A1.DISTPRICE2ND, A1.SUBPRICE2ND, A1.EXPDATE2N
D,
A1.DISTPRICE3RD, A1.SUBPRICE3RD, A1.EXPDATE3RD, A1.DISTPRICE4TH, A1.SUBPRICE
4TH,
A1.EXPDATE4TH
FROM C_JOBBID A1 INNER JOIN OPPORTUNITY A2 ON (A1.OPPORTUNITYID = A2.OPPORTU
NITYID)
INNER JOIN C_OPPORTUNITY_EXT A3 ON (A2.OPPORTUNITYID = A3.OPPORTUNITYID)
INNER JOIN C_OPPTOACCOUNT A4 ON (A4.OPPORTUNITYID = A1.OPPORTUNITYID)
WHERE A4.ACCOUNTID = 'A6UJ9A0069NH'
AND A3.METRO IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE USERID = 'ADMIN
')
AND A1.PROD_PR_GRP_C = 'C167'
ORDER BY A1.OPPORTUNITYID ASC, A1.SPEC_PR_PROD_C ASC, A1.EXPDATE1ST ASC
This query runs slower than:
SELECT A1.C_JOBBIDID, A2.ACCOUNTID A2_ACCOUNTID, A1.PROD_PR_GRP_C, A1.WON,
A2.DESCRIPTION A2_DESCRIPTION, A1.OPPORTUNITYID, A3.USERFIELD5 A3_USERFIELD5
,
A2.STATUS A2_STATUS, A1.SPEC_PR_PROD_C, A1.SPR_ITEM_ALLOW_Q, A1.DISTPRICE1ST
,
A1.SUBPRICE1ST, A1.EXPDATE1ST, A1.DISTPRICE2ND, A1.SUBPRICE2ND, A1.EXPDATE2N
D,
A1.DISTPRICE3RD, A1.SUBPRICE3RD, A1.EXPDATE3RD, A1.DISTPRICE4TH, A1.SUBPRICE
4TH,
A1.EXPDATE4TH
FROM C_JOBBID A1 INNER JOIN OPPORTUNITY A2 ON (A1.OPPORTUNITYID = A2.OPPORTU
NITYID)
INNER JOIN C_OPPORTUNITY_EXT A3 ON (A2.OPPORTUNITYID = A3.OPPORTUNITYID)
INNER JOIN C_OPPTOACCOUNT A4 ON (A4.OPPORTUNITYID = A1.OPPORTUNITYID)
WHERE A4.ACCOUNTID = 'A6UJ9A0069NH'
AND ISNULL(A3.METRO, '') IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE
USERID = 'ADMIN')
AND A1.PROD_PR_GRP_C = 'C167'
ORDER BY A1.OPPORTUNITYID ASC, A1.SPEC_PR_PROD_C ASC, A1.EXPDATE1ST ASC
As you can see, the only difference is checking the field for null and conve
rting
it to a blank string. You'd think the second one involving "more work" woul
d
take longer. Can someone explain why this is so?
Thanks in advance.
Jiho Han
jihohan@.yahoo.comWhat do you mean, slower? Considerably, or minimally? Can you check the
plan, or post the plan using SET SHOWPLAN_TEXT ON? It will probably reveal
the reason if it is considerable.
You might also try rewriting
> AND ISNULL(A3.METRO, '') IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE
> USERID = 'ADMIN')
as
AND EXISTS ( SELECT 1
FROM C_USERMETROS
WHERE C_USERMETROS.USERID = 'ADMIN'
AND PRICINGMETRO = A3.METRO)
assuming that there is not a A3.METRO that actually equals ''
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Jiho Han" <jihohan@.yahoo.com> wrote in message
news:a19ab9b65ef08c806917750fbb2@.msnews.microsoft.com...
>I have a sql statement:
> SELECT A1.C_JOBBIDID, A2.ACCOUNTID A2_ACCOUNTID, A1.PROD_PR_GRP_C, A1.WON,
> A2.DESCRIPTION A2_DESCRIPTION, A1.OPPORTUNITYID, A3.USERFIELD5
> A3_USERFIELD5,
> A2.STATUS A2_STATUS, A1.SPEC_PR_PROD_C, A1.SPR_ITEM_ALLOW_Q,
> A1.DISTPRICE1ST,
> A1.SUBPRICE1ST, A1.EXPDATE1ST, A1.DISTPRICE2ND, A1.SUBPRICE2ND,
> A1.EXPDATE2ND,
> A1.DISTPRICE3RD, A1.SUBPRICE3RD, A1.EXPDATE3RD, A1.DISTPRICE4TH,
> A1.SUBPRICE4TH, A1.EXPDATE4TH FROM C_JOBBID A1 INNER JOIN OPPORTUNITY A2
> ON (A1.OPPORTUNITYID = A2.OPPORTUNITYID)
> INNER JOIN C_OPPORTUNITY_EXT A3 ON (A2.OPPORTUNITYID = A3.OPPORTUNITYID)
> INNER JOIN C_OPPTOACCOUNT A4 ON (A4.OPPORTUNITYID = A1.OPPORTUNITYID)
> WHERE A4.ACCOUNTID = 'A6UJ9A0069NH' AND A3.METRO IN (SELECT PRICINGMETRO
> FROM C_USERMETROS WHERE USERID = 'ADMIN')
> AND A1.PROD_PR_GRP_C = 'C167'
> ORDER BY A1.OPPORTUNITYID ASC, A1.SPEC_PR_PROD_C ASC, A1.EXPDATE1ST ASC
> This query runs slower than:
> SELECT A1.C_JOBBIDID, A2.ACCOUNTID A2_ACCOUNTID, A1.PROD_PR_GRP_C, A1.WON,
> A2.DESCRIPTION A2_DESCRIPTION, A1.OPPORTUNITYID, A3.USERFIELD5
> A3_USERFIELD5,
> A2.STATUS A2_STATUS, A1.SPEC_PR_PROD_C, A1.SPR_ITEM_ALLOW_Q,
> A1.DISTPRICE1ST,
> A1.SUBPRICE1ST, A1.EXPDATE1ST, A1.DISTPRICE2ND, A1.SUBPRICE2ND,
> A1.EXPDATE2ND,
> A1.DISTPRICE3RD, A1.SUBPRICE3RD, A1.EXPDATE3RD, A1.DISTPRICE4TH,
> A1.SUBPRICE4TH, A1.EXPDATE4TH FROM C_JOBBID A1 INNER JOIN OPPORTUNITY A2
> ON (A1.OPPORTUNITYID = A2.OPPORTUNITYID)
> INNER JOIN C_OPPORTUNITY_EXT A3 ON (A2.OPPORTUNITYID = A3.OPPORTUNITYID)
> INNER JOIN C_OPPTOACCOUNT A4 ON (A4.OPPORTUNITYID = A1.OPPORTUNITYID)
> WHERE A4.ACCOUNTID = 'A6UJ9A0069NH' AND ISNULL(A3.METRO, '') IN (SELECT
> PRICINGMETRO FROM C_USERMETROS WHERE USERID = 'ADMIN') AND
> A1.PROD_PR_GRP_C = 'C167'
> ORDER BY A1.OPPORTUNITYID ASC, A1.SPEC_PR_PROD_C ASC, A1.EXPDATE1ST ASC
> As you can see, the only difference is checking the field for null and
> converting it to a blank string. You'd think the second one involving
> "more work" would take longer. Can someone explain why this is so?
> Thanks in advance.
> Jiho Han
> jihohan@.yahoo.com
>|||Hello Louis,
I am attaching the plan. It's the first time I've used that option so, I di
dn't know if this will be in the right format.
Also, I've tried the EXISTS approach and the result is same (I mean the perf
ormance). The plain option takes ~150ms(duration) vs ISNULL takes ~30ms. S
o it's not a huge difference but
it's significant enough that it's noticeable.
Thank you
Jiho Han
Senior Software Engineer
Infinity Info Systems
The Sales Technology Experts
Tel: 212.563.4400 x216
Fax: 212.760.0540
jhan@.infinityinfo.com
www.infinityinfo.com
> What do you mean, slower? Considerably, or minimally? Can you check
> the plan, or post the plan using SET SHOWPLAN_TEXT ON? It will
> probably reveal the reason if it is considerable.
>
> You might also try rewriting
>
> as
> AND EXISTS ( SELECT 1
> FROM C_USERMETROS
> WHERE C_USERMETROS.USERID = 'ADMIN'
> AND PRICINGMETRO = A3.METRO)
> assuming that there is not a A3.METRO that actually equals ''
>
> "Jiho Han" <jihohan@.yahoo.com> wrote in message
> news:a19ab9b65ef08c806917750fbb2@.msnews.microsoft.com...
>|||ISNULL (the function) is not equivalent to IS NULL (the syntax construct) in
SQL 2000 or 2005 to its optimizer.
You might try to use the latter since it is more completely supported.
Essentially, we're not reasoning about the output distribution on ISNULL().
That may cause suboptimal plans in complex queries.
Conor Cunningham
SQL Server Query Optimization Development Lead
"Jiho Han" <jihohan@.yahoo.com> wrote in message
news:a19ab9b661698c806aa7485348c@.msnews.microsoft.com...
Hello Louis,
I am attaching the plan. It's the first time I've used that option so, I
didn't know if this will be in the right format.
Also, I've tried the EXISTS approach and the result is same (I mean the
performance). The plain option takes ~150ms(duration) vs ISNULL takes
~30ms. So it's not a huge difference but it's significant enough that it's
noticeable.
Thank you
Jiho Han
Senior Software Engineer
Infinity Info Systems
The Sales Technology Experts
Tel: 212.563.4400 x216
Fax: 212.760.0540
jhan@.infinityinfo.com
www.infinityinfo.com
> What do you mean, slower? Considerably, or minimally? Can you check
> the plan, or post the plan using SET SHOWPLAN_TEXT ON? It will
> probably reveal the reason if it is considerable.
> You might also try rewriting
>
> as
> AND EXISTS ( SELECT 1
> FROM C_USERMETROS
> WHERE C_USERMETROS.USERID = 'ADMIN'
> AND PRICINGMETRO = A3.METRO)
> assuming that there is not a A3.METRO that actually equals ''
> "Jiho Han" <jihohan@.yahoo.com> wrote in message
> news:a19ab9b65ef08c806917750fbb2@.msnews.microsoft.com...
>|||Ok, that one just went over me completely.
I understand that ISNULL and IS NULL may not be equivalent - I would hope
not.
What I wanted to know was why
A3.METRO IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE USERID = 'ADMIN')
runs faster than
ISNULL(A3.METRO, '') IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE USERID
= 'ADMIN')
What I did notice when viewing the execution plan was that while using ISNUL
L
as above caused the query engine to use a hash match, the plain one caused
the engine to use a distinct sort followed by a nested loop.
I am very curious as to why it works this way and to determine whether I
need to employ ISNULL in all my other queries for query performance improvem
ent.
Thanks for your assistance!
Jiho Han
Senior Software Engineer
Infinity Info Systems
The Sales Technology Experts
Tel: 212.563.4400 x216
Fax: 212.760.0540
jhan@.infinityinfo.com
www.infinityinfo.com
> ISNULL (the function) is not equivalent to IS NULL (the syntax
> construct) in SQL 2000 or 2005 to its optimizer.
> You might try to use the latter since it is more completely supported.
> Essentially, we're not reasoning about the output distribution on
> ISNULL(). That may cause suboptimal plans in complex queries.
> Conor Cunningham
> SQL Server Query Optimization Development Lead
> "Jiho Han" <jihohan@.yahoo.com> wrote in message
> news:a19ab9b661698c806aa7485348c@.msnews.microsoft.com... Hello Louis,
> I am attaching the plan. It's the first time I've used that option
> so, I didn't know if this will be in the right format.
> Also, I've tried the EXISTS approach and the result is same (I mean
> the performance). The plain option takes ~150ms(duration) vs ISNULL
> takes ~30ms. So it's not a huge difference but it's significant
> enough that it's noticeable.
> Thank you
> Jiho Han
> Senior Software Engineer
> Infinity Info Systems
> The Sales Technology Experts
> Tel: 212.563.4400 x216
> Fax: 212.760.0540
> jhan@.infinityinfo.com
> www.infinityinfo.com|||It would be interesting to see what the execution plan would by if you
changed that portion of the query to
A3.METRIO IN (SLECT PRICINGMETRO FROM C_USERMETROS WHERE USERID = 'Admin'
AND PRICINGMETRO IS NOT NULL)
This is is just a wild guess, but maybe SQL is optimizing based on the fact
that ISNULL can't return a null result, and therefore can exclude null
results from the sub-query.
> What I wanted to know was why
> A3.METRO IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE USERID = 'ADMIN')
> runs faster than
> ISNULL(A3.METRO, '') IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE USERI
D
> = 'ADMIN')
> What I did notice when viewing the execution plan was that while using ISN
ULL
> as above caused the query engine to use a hash match, the plain one caused
> the engine to use a distinct sort followed by a nested loop.
> I am very curious as to why it works this way and to determine whether I
> need to employ ISNULL in all my other queries for query performance improvement.[/
color]|||Apologies - let me try to rephrase.
The query optimizer uses a tree model of operations that represent your SQL
statement. Additionally, statistical information (such as a histogram of
data for a column) is recorded for base tables and then pushed up through
the tree, being modified during each step. So, if you scan rows from a base
table and then filter, the filter operation would have an estimated output
distribution *after* the filter. This information is used to estimate
cardinality for each operator and to eventually cost various alternatives.
If the cardinality estimate is high, we may pick things like the hash join.
If it's low, we're more likely to pick a nested loops join. So, this
information is very important to picking an efficient plan.
Some constructs do not have full support in the optimizer. When we do not
have that information, we may not be able to come up with a good output
distribution for it, and thus the cardinality and cost may be incorrect.
ISNULL() is such an operator. When this happens, we may pick the loops join
when the hash join would have been better. This is when you see performance
issues.
In this case, you are using it to join with a subquery, and the distribution
is quite important here. I would recommend that you try to avoid this if
you are seeing plan issues. Once you start using the result of the function
is some other operation (a join, a subquery, a filter, etc), then I would
recommend that you consider avoiding it, if possible.
I hope that helps.
Thanks,
Conor
"Jiho Han" <jihohan@.yahoo.com> wrote in message
news:a19ab9b662c18c80742337e046d@.msnews.microsoft.com...
> Ok, that one just went over me completely.
> I understand that ISNULL and IS NULL may not be equivalent - I would hope
> not.
> What I wanted to know was why
> A3.METRO IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE USERID = 'ADMIN')
> runs faster than
> ISNULL(A3.METRO, '') IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE
> USERID = 'ADMIN')
> What I did notice when viewing the execution plan was that while using
> ISNULL as above caused the query engine to use a hash match, the plain one
> caused the engine to use a distinct sort followed by a nested loop.
> I am very curious as to why it works this way and to determine whether I
> need to employ ISNULL in all my other queries for query performance
> improvement.
> Thanks for your assistance!
> Jiho Han
> Senior Software Engineer
> Infinity Info Systems
> The Sales Technology Experts
> Tel: 212.563.4400 x216
> Fax: 212.760.0540
> jhan@.infinityinfo.com
> www.infinityinfo.com
>
>|||The option you specified came back slower actually. ~250 ms.
I've done several variations using IS NULL:
A3.METRO IS NOT NULL AND A3.METRO IN (SELECT PRICINGMETRO FROM C_USERMETROS
WHERE USERID = 'ADMIN')
A3.METRO IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE USERID = 'ADMIN'
AND PRICINGMETRO IS NOT NULL)
A3.METRO IS NOT NULL AND IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE
USERID = 'ADMIN' AND PRICINGMETRO IS NOT NULL)
They are all slower and are around ~250 ms. It seems to me that by adding
IS NOT NULL check, it's actually adding more work.
Nothing beats:
ISNULL(A3.METRO, '') IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE USERID
= 'ADMIN')
which consistently executes around ~30ms.
Jiho Han
Senior Software Engineer
Infinity Info Systems
The Sales Technology Experts
Tel: 212.563.4400 x216
Fax: 212.760.0540
jhan@.infinityinfo.com
www.infinityinfo.com
> It would be interesting to see what the execution plan would by if you
> changed that portion of the query to
> A3.METRIO IN (SLECT PRICINGMETRO FROM C_USERMETROS WHERE USERID =
> 'Admin' AND PRICINGMETRO IS NOT NULL)
> This is is just a wild guess, but maybe SQL is optimizing based on the
> fact that ISNULL can't return a null result, and therefore can exclude
> null results from the sub-query.
>|||Thanks Conor, that was very helpful although I can't say that I can absorb
everything you've said.
Just to be clear, and since I am wondering whether there is a misunderstandi
ng
here, I am reporting that the use of ISNULL() is performing better than the
lack of, or using IS NULL. If you see my other reply, you'll see some numbe
rs
I got trying to use IS NULL instead and they are all slower, even more so
than not using IS NULL.
Also, could you rephrase your last paragraph? Are you saying that I should
avoid using subqueries? or ISNULL()?
Are you suggesting that I use ISNULL() only in SELECT clause in your last
statement?
Thanks
Jiho
> Apologies - let me try to rephrase.
> The query optimizer uses a tree model of operations that represent
> your SQL statement. Additionally, statistical information (such as a
> histogram of data for a column) is recorded for base tables and then
> pushed up through the tree, being modified during each step. So, if
> you scan rows from a base table and then filter, the filter operation
> would have an estimated output distribution *after* the filter. This
> information is used to estimate cardinality for each operator and to
> eventually cost various alternatives. If the cardinality estimate is
> high, we may pick things like the hash join. If it's low, we're more
> likely to pick a nested loops join. So, this information is very
> important to picking an efficient plan.
> Some constructs do not have full support in the optimizer. When we do
> not have that information, we may not be able to come up with a good
> output distribution for it, and thus the cardinality and cost may be
> incorrect. ISNULL() is such an operator. When this happens, we may
> pick the loops join when the hash join would have been better. This
> is when you see performance issues.
> In this case, you are using it to join with a subquery, and the
> distribution is quite important here. I would recommend that you try
> to avoid this if you are seeing plan issues. Once you start using the
> result of the function is some other operation (a join, a subquery, a
> filter, etc), then I would recommend that you consider avoiding it, if
> possible.
> I hope that helps.
> Thanks,
> Conor
> "Jiho Han" <jihohan@.yahoo.com> wrote in message
> news:a19ab9b662c18c80742337e046d@.msnews.microsoft.com...
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:587012
Jiho Han wrote:
> Thanks Conor, that was very helpful although I can't say that I can absorb
> everything you've said.
> Just to be clear, and since I am wondering whether there is a misunderstan
ding
> here, I am reporting that the use of ISNULL() is performing better than th
e
> lack of, or using IS NULL. If you see my other reply, you'll see some num
bers
> I got trying to use IS NULL instead and they are all slower, even more so
> than not using IS NULL.
> Also, could you rephrase your last paragraph? Are you saying that I shoul
d
> avoid using subqueries? or ISNULL()?
> Are you suggesting that I use ISNULL() only in SELECT clause in your last
> statement?
> Thanks
> Jiho
I am not Conor, but I will answer it anyway.
The advice of Conor is to avoid using functions/expressions in
combination with subqueries, because the optimizer will have better
information if you only use the column.
However, if I understand you correctly, in your case, the optimizer is
picking a faster plan for the ISNULL() query. If I undestand you
correctly, the query
AND ISNULL(A3.METRO, '') IN (SELECT PRICINGMETRO FROM C_USERMETROS
WHERE USERID = 'ADMIN')
uses a hash match to finish in ~30ms
and the query
AND A3.METRO IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE USERID =
'ADMIN')
uses a distinct sort and nested loop to finish in ~150ms
So although the optimizer has better information for the second query,
it actually picks a query plan that performs worse. Aparently, in your
situation, the hash match is the fastest solution. Maybe the optimizer
is unable to accurately estimate the output of the subquery (maybe it
returns more distinct values than expected).
You could also try to rewrite it differently, and see if that helps.
For example, you could try this:
AND EXISTS (
SELECT *
FROM C_USERMETROS
WHERE USERID = 'ADMIN'
AND PRICINGMETRO = A3.METRO
)
If the subquery returns unique values for PRICINGMETRO, then you could
also try this:
INNER JOIN C_USERMETROS
ON USERID = 'ADMIN'
AND PRICINGMETRO = A3.METRO
If the optimizer is actually misjudging the result from the subquery,
then it could help if you added an index on C_USERMETROS (USERID,
PRICINGMETRO).
HTH,
Gert-Jan
Wednesday, March 21, 2012
Query Performance Question
I have a query that takes approximately 2 mins to run. The query is derived from two tables that have a one to many relationship. When I comment out 4 of the fields in the 'select' portion of the query, it speeds up to 18 seconds. I know that I am doin
g a bookmark lookup (this is 60% of the query time) but I am not sure how to avoid this. Any ideas?
Hi,
The covering index is only effective when the query
does a Bookmark lookup. if you have covering index the query will not hit
the source table for data. but , the disadvantage is that a covering index
increases the
number of writes when one or more of the columns in the index is updated.
This might be a issue durng insert./update and delete.
Thanks
Hari
MCDBA
"Daniel Avsec" <Daniel Avsec@.discussions.microsoft.com> wrote in message
news:1611776E-29E8-4F46-BB8F-97A8E6A32C45@.microsoft.com...
> I have a query that takes approximately 2 mins to run. The query is
derived from two tables that have a one to many relationship. When I
comment out 4 of the fields in the 'select' portion of the query, it speeds
up to 18 seconds. I know that I am doing a bookmark lookup (this is 60% of
the query time) but I am not sure how to avoid this. Any ideas?
g a bookmark lookup (this is 60% of the query time) but I am not sure how to avoid this. Any ideas?
Hi,
The covering index is only effective when the query
does a Bookmark lookup. if you have covering index the query will not hit
the source table for data. but , the disadvantage is that a covering index
increases the
number of writes when one or more of the columns in the index is updated.
This might be a issue durng insert./update and delete.
Thanks
Hari
MCDBA
"Daniel Avsec" <Daniel Avsec@.discussions.microsoft.com> wrote in message
news:1611776E-29E8-4F46-BB8F-97A8E6A32C45@.microsoft.com...
> I have a query that takes approximately 2 mins to run. The query is
derived from two tables that have a one to many relationship. When I
comment out 4 of the fields in the 'select' portion of the query, it speeds
up to 18 seconds. I know that I am doing a bookmark lookup (this is 60% of
the query time) but I am not sure how to avoid this. Any ideas?
query performance question
Hello. I have query performance question.
I need to optimize procedure
I need to optimize procedure
CREATE PROCEDURE dbo.SECUQUSRCOMPACCES
@.P1 VARCHAR(50),
@.P2 INTEGER
AS
DECLARE @.IORGANIZATIONID INTEGER
EXECUTE dbo.ORGNQGETORGID @.PORGUNIQUEID = @.IORGANIZATIONID OUTPUT
SELECT TSECCOMP.ID,
CASE TSECPROFILEGRP.ACCESSTYPE
WHEN -1 THEN
CASE TSECCLASS.DEFAULTACCESS
WHEN -1 THEN
CASE TSECGROUPCOMP.DEFAULTACCESS
WHEN -1 THEN
TSECCOMP.DEFAULTACCESS
ELSE
TSECGROUPCOMP.DEFAULTACCESS
END
ELSE
TSECCLASS.DEFAULTACCESS
END
ELSE TSECPROFILEGRP.ACCESSTYPE
END AS EXPR1
FROM TSECCOMP
INNER JOIN ((TSECPROFILE
INNER JOIN (TSECCLASS
INNER JOIN TSECPROFILEGRP
ON TSECCLASS.UNIQUEID = TSECPROFILEGRP.SECURITYGROUPID)
ON TSECPROFILE.UNIQUEID = TSECPROFILEGRP.PROFILEID) INNER JOIN
TSECGROUPCOMP ON TSECCLASS.UNIQUEID = TSECGROUPCOMP.SECURITYGROUPID)
ON TSECCOMP.UNIQUEID = TSECGROUPCOMP.SECCOMPID
WHERE
(
CASE TSECPROFILEGRP.ACCESSTYPE
WHEN -1 THEN
CASE TSECCLASS.DEFAULTACCESS
WHEN -1 THEN
CASE TSECGROUPCOMP.DEFAULTACCESS
WHEN -1 THEN
TSECCOMP.DEFAULTACCESS
ELSE
TSECGROUPCOMP.DEFAULTACCESS
END
ELSE
TSECCLASS.DEFAULTACCESS
END
ELSE TSECPROFILEGRP.ACCESSTYPE
END > 0 ) AND (TSECPROFILE.KEYVALUE=@.P1) AND ( TSECCOMP.TYPE =@.P2)
AND TSECCOMP.ORGANIZATIONID = @.IORGANIZATIONID
GO
Thank you In advance.Make sure you have indexed the join keys. You can try running the Index
Tuning Wizard for advice.
Gert-Jan
inna wrote:
> Hello. I have query performance question.
> I need to optimize procedure
> CREATE PROCEDURE dbo.SECUQUSRCOMPACCES
> @.P1 VARCHAR(50),
> @.P2 INTEGER
> AS
> DECLARE @.IORGANIZATIONID INTEGER
> EXECUTE dbo.ORGNQGETORGID @.PORGUNIQUEID = @.IORGANIZATIONID OUTPUT
> SELECT TSECCOMP.ID,
> CASE TSECPROFILEGRP.ACCESSTYPE
> WHEN -1 THEN
> CASE TSECCLASS.DEFAULTACCESS
> WHEN -1 THEN
> CASE TSECGROUPCOMP.DEFAULTACCESS
> WHEN -1 THEN
> TSECCOMP.DEFAULTACCESS
> ELSE
> TSECGROUPCOMP.DEFAULTACCESS
> END
> ELSE
> TSECCLASS.DEFAULTACCESS
> END
> ELSE TSECPROFILEGRP.ACCESSTYPE
> END AS EXPR1
> FROM TSECCOMP
> INNER JOIN ((TSECPROFILE
> INNER JOIN (TSECCLASS
> INNER JOIN TSECPROFILEGRP
> ON TSECCLASS.UNIQUEID = TSECPROFILEGRP.SECURITYGROUPID)
> ON TSECPROFILE.UNIQUEID = TSECPROFILEGRP.PROFILEID) INNER JOIN
> TSECGROUPCOMP ON TSECCLASS.UNIQUEID = TSECGROUPCOMP.SECURITYGROUPID)
> ON TSECCOMP.UNIQUEID = TSECGROUPCOMP.SECCOMPID
> WHERE
> (
> CASE TSECPROFILEGRP.ACCESSTYPE
> WHEN -1 THEN
> CASE TSECCLASS.DEFAULTACCESS
> WHEN -1 THEN
> CASE TSECGROUPCOMP.DEFAULTACCESS
> WHEN -1 THEN
> TSECCOMP.DEFAULTACCESS
> ELSE
> TSECGROUPCOMP.DEFAULTACCESS
> END
> ELSE
> TSECCLASS.DEFAULTACCESS
> END
> ELSE TSECPROFILEGRP.ACCESSTYPE
> END > 0 ) AND (TSECPROFILE.KEYVALUE=@.P1) AND ( TSECCOMP.TYPE =@.P2)
> AND TSECCOMP.ORGANIZATIONID = @.IORGANIZATIONID
> GO
> Thank you In advance.|||Hi
Check out the query execution plan
http://www.sql-server-performance.c...an_analysis.asp
http://www.sql-server-performance.com/transact_sql.asp
John
"inna" <mednyk@.hotmail.com> wrote in message
news:347a408b.0309131204.19c074e8@.posting.google.c om...
> Hello. I have query performance question.
> I need to optimize procedure
> CREATE PROCEDURE dbo.SECUQUSRCOMPACCES
> @.P1 VARCHAR(50),
> @.P2 INTEGER
> AS
> DECLARE @.IORGANIZATIONID INTEGER
> EXECUTE dbo.ORGNQGETORGID @.PORGUNIQUEID = @.IORGANIZATIONID OUTPUT
> SELECT TSECCOMP.ID,
> CASE TSECPROFILEGRP.ACCESSTYPE
> WHEN -1 THEN
> CASE TSECCLASS.DEFAULTACCESS
> WHEN -1 THEN
> CASE TSECGROUPCOMP.DEFAULTACCESS
> WHEN -1 THEN
> TSECCOMP.DEFAULTACCESS
> ELSE
> TSECGROUPCOMP.DEFAULTACCESS
> END
> ELSE
> TSECCLASS.DEFAULTACCESS
> END
> ELSE TSECPROFILEGRP.ACCESSTYPE
> END AS EXPR1
> FROM TSECCOMP
> INNER JOIN ((TSECPROFILE
> INNER JOIN (TSECCLASS
> INNER JOIN TSECPROFILEGRP
> ON TSECCLASS.UNIQUEID = TSECPROFILEGRP.SECURITYGROUPID)
> ON TSECPROFILE.UNIQUEID = TSECPROFILEGRP.PROFILEID) INNER JOIN
> TSECGROUPCOMP ON TSECCLASS.UNIQUEID = TSECGROUPCOMP.SECURITYGROUPID)
> ON TSECCOMP.UNIQUEID = TSECGROUPCOMP.SECCOMPID
> WHERE
> (
> CASE TSECPROFILEGRP.ACCESSTYPE
> WHEN -1 THEN
> CASE TSECCLASS.DEFAULTACCESS
> WHEN -1 THEN
> CASE TSECGROUPCOMP.DEFAULTACCESS
> WHEN -1 THEN
> TSECCOMP.DEFAULTACCESS
> ELSE
> TSECGROUPCOMP.DEFAULTACCESS
> END
> ELSE
> TSECCLASS.DEFAULTACCESS
> END
> ELSE TSECPROFILEGRP.ACCESSTYPE
> END > 0 ) AND (TSECPROFILE.KEYVALUE=@.P1) AND ( TSECCOMP.TYPE =@.P2)
> AND TSECCOMP.ORGANIZATIONID = @.IORGANIZATIONID
> GO
> Thank you In advance.
Query Performance Question
I have a query that takes approximately 2 mins to run. The query is derived from two tables that have a one to many relationship. When I comment out 4 of the fields in the 'select' portion of the query, it speeds up to 18 seconds. I know that I am doing a bookmark lookup (this is 60% of the query time) but I am not sure how to avoid this. Any ideas?Hi,
The covering index is only effective when the query
does a Bookmark lookup. if you have covering index the query will not hit
the source table for data. but , the disadvantage is that a covering index
increases the
number of writes when one or more of the columns in the index is updated.
This might be a issue durng insert./update and delete.
Thanks
Hari
MCDBA
"Daniel Avsec" <Daniel Avsec@.discussions.microsoft.com> wrote in message
news:1611776E-29E8-4F46-BB8F-97A8E6A32C45@.microsoft.com...
> I have a query that takes approximately 2 mins to run. The query is
derived from two tables that have a one to many relationship. When I
comment out 4 of the fields in the 'select' portion of the query, it speeds
up to 18 seconds. I know that I am doing a bookmark lookup (this is 60% of
the query time) but I am not sure how to avoid this. Any ideas?
The covering index is only effective when the query
does a Bookmark lookup. if you have covering index the query will not hit
the source table for data. but , the disadvantage is that a covering index
increases the
number of writes when one or more of the columns in the index is updated.
This might be a issue durng insert./update and delete.
Thanks
Hari
MCDBA
"Daniel Avsec" <Daniel Avsec@.discussions.microsoft.com> wrote in message
news:1611776E-29E8-4F46-BB8F-97A8E6A32C45@.microsoft.com...
> I have a query that takes approximately 2 mins to run. The query is
derived from two tables that have a one to many relationship. When I
comment out 4 of the fields in the 'select' portion of the query, it speeds
up to 18 seconds. I know that I am doing a bookmark lookup (this is 60% of
the query time) but I am not sure how to avoid this. Any ideas?
Query Performance Question
I have a query that takes approximately 2 mins to run. The query is derived
from two tables that have a one to many relationship. When I comment out 4
of the fields in the 'select' portion of the query, it speeds up to 18 seco
nds. I know that I am doin
g a bookmark lookup (this is 60% of the query time) but I am not sure how to
avoid this. Any ideas?Hi,
The covering index is only effective when the query
does a Bookmark lookup. if you have covering index the query will not hit
the source table for data. but , the disadvantage is that a covering index
increases the
number of writes when one or more of the columns in the index is updated.
This might be a issue durng insert./update and delete.
Thanks
Hari
MCDBA
"Daniel Avsec" <Daniel Avsec@.discussions.microsoft.com> wrote in message
news:1611776E-29E8-4F46-BB8F-97A8E6A32C45@.microsoft.com...
> I have a query that takes approximately 2 mins to run. The query is
derived from two tables that have a one to many relationship. When I
comment out 4 of the fields in the 'select' portion of the query, it speeds
up to 18 seconds. I know that I am doing a bookmark lookup (this is 60% of
the query time) but I am not sure how to avoid this. Any ideas?sql
from two tables that have a one to many relationship. When I comment out 4
of the fields in the 'select' portion of the query, it speeds up to 18 seco
nds. I know that I am doin
g a bookmark lookup (this is 60% of the query time) but I am not sure how to
avoid this. Any ideas?Hi,
The covering index is only effective when the query
does a Bookmark lookup. if you have covering index the query will not hit
the source table for data. but , the disadvantage is that a covering index
increases the
number of writes when one or more of the columns in the index is updated.
This might be a issue durng insert./update and delete.
Thanks
Hari
MCDBA
"Daniel Avsec" <Daniel Avsec@.discussions.microsoft.com> wrote in message
news:1611776E-29E8-4F46-BB8F-97A8E6A32C45@.microsoft.com...
> I have a query that takes approximately 2 mins to run. The query is
derived from two tables that have a one to many relationship. When I
comment out 4 of the fields in the 'select' portion of the query, it speeds
up to 18 seconds. I know that I am doing a bookmark lookup (this is 60% of
the query time) but I am not sure how to avoid this. Any ideas?sql
Query performance problems with join on UDF-based computed column
We have a table with a couple of computed columns. The value of the computed column represents a foreign key reference into another table. We're seeing a major performance problem doing a query joining between the two tables with one of the columns, but not the other. In other words, this kind of query is very fast:
Thanks for the suggestions. I tried changing the UDF to use schema binding, but it didn't seem to effect the query plan at all. Persisting the computed column isn't a possibility here, as its value changes over time.
|||
select * from TheTable A, FKeyTable B
where A.ComputedColumn1 = B.KeyColumn
but this one sends the CPU usage of SQL Server to 99% for a very long time:
select * from TheTable A, FKeyTable B
where A.ComputedColumn2 = B.KeyColumn
The main difference we can see that the computed column that causes problems is based on a UDF, and the other one isn't (but again, both are computed). When I look at the execution plan, the slow query shows a Nested Loop (Inner Join) with a "No Join Predicate" warning, with the estimated # of rows being 70 million (which correponds to the product of 1016 rows in TheTable and 69K rows in FKeyTable). The fast query doesn't have that warning, and shows 1016 rows (the # of rows in TheTable).
Does anyone know why the usage of a UDF would induce this horribly inefficient join behavior? Anything we can do to fix it?
This is SQL Server 2005 SP2, btw.
By default, the engine does not know anything about the udf (i.e. has no statistics). So, bad plan can be generated.
To avoid this, I suggest that you create your udf with schemabinding and persist your computed column.
|||oj,Thanks for the suggestions. I tried changing the UDF to use schema binding, but it didn't seem to effect the query plan at all. Persisting the computed column isn't a possibility here, as its value changes over time.
|||
Scalar udf is executed once per row which tends to be the cause for horrible performance. Now that we know that the values are non-deterministic, performance hit is almost unavoidable.
Can you post some ddl+sample data (insert)+sample code. We might be able to devise a solution.
|||I'll see if I can come up with a simple repro.
What I don't understand is why a regular inline computed column (which is also non-deterministic) performs just fine, but the UDF version performs so horribly. I tried taking the computed column, which is essentially computing a time duration by calling datediff between "now" and a datetime stored in another column of the table, and converting the exact logic into a UDF. Once I do that, it has the same (bad) performance characteristics as my original problem - a Nested Loop with a "No Join Predicate" warning, 70M rows.
BTW, actually querying the column value is quite fast. It's the join that craters the performance.
|||Have you tried explicitly creating a statistic for the column?
e.g.
create statistics _stats on table1(compute_col) with fullscan, norecompute
|||No luck. It complained that it "cannot be used in an index or statistics or as a partition key because it is non-deterministic."|||Kevin, please post the requested info. Don't forget the udf code, too.
Note: you can use either free tool, objectscriptr or qalite from rac4sql.net to generate ddl+sample data.
|||Here's the scripts (as far as I can tell, there's no attachment support on the forums....bummer). This is a seriously simplified version of our tables that reproduce the issue we're having.
The Create script:
Code Snippet
CREATE FUNCTION [dbo].[ComputeFKey](@.startTS datetime)
RETURNS float
with schemabinding
AS
BEGIN
RETURN datediff(minute,@.startTS,getutcdate())/(5)*(5)
END
go
CREATE TABLE [dbo].[JoinTable](
[KeyCol] [int] NOT NULL,
[MoreData] [int] NOT NULL,
CONSTRAINT [PK_JoinTable] PRIMARY KEY CLUSTERED
(
[KeyCol] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
go
CREATE TABLE [dbo].[MainTable](
[KeyCol] [int] IDENTITY(1,1) NOT NULL,
[CompCol] AS ((datediff(minute,[TSCol],getutcdate())/(5))*(5)),
[UdfCompCol] AS ([dbo].[ComputeFKey]([TSCol])),
[TSCol] [datetime] NOT NULL,
CONSTRAINT [PK_MainTable] PRIMARY KEY CLUSTERED
(
[KeyCol] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
go
Script to populate the join table:
Code Snippet
declare @.Start int
declare @.KeyVal int
declare @.RowsToAdd int
declare @.MoreData int
select @.RowsToAdd = 1000
if exists (select * from JoinTable)
select @.Start = max(KeyCol) + 5 from JoinTable
else
select @.Start = 0
select @.KeyVal = @.Start
while (@.KeyVal < (@.Start + @.RowsToAdd * 5))
begin
select @.MoreData = @.KeyVal % 60
insert into JoinTable (KeyCol, MoreData) values (@.KeyVal, @.MoreData)
select @.KeyVal = @.KeyVal + 5
end
Script to populate the main table:
Code Snippet
declare @.Row int
declare @.RowsToAdd int
select @.Row = 0
select @.RowsToAdd = 1000
while (@.Row < @.RowsToAdd)
begin
insert into MainTable (TSCol) values (DateAdd("hh", -1, getutcdate()))
select @.Row = @.Row + 1
end
Queries that demonstrate the issue:
Code Snippet
-- Fast query
select A.KeyCol from MainTable A, JoinTable B where A.CompCol = B.KeyCol
-- Slow query (Nested Loop with table spool)
select A.KeyCol from MainTable A, JoinTable B where A.UdfCompCol = B.KeyCol
The second query joins against the computed column that uses the UDF, and ends up with a Nested Loop with a table spool (a million rows in the Nested loop if you use the default values of the populate scripts - 1000 rows per table).
|||Hi Kevin!
This is probably a bug, because MS SQL 2k behaves fine in this case (the execution plans are identical).
And btw, your function and column have different data types (float and integer).
|||Oops - that was an error in creating my simplified repro case. The actual application schema has a CAST in the computed column. Thanks for catching that (though it doesn't affect the result). Replace the UdfCompCol definition with this:
[UdfCompCol] AS (CONVERT([int],[dbo].[ComputeFKey]([TSCol]),0))
|||
Thank you for posting the requested info. It's helpful to see what you're up against.
Now, to the _bad_ news. This is actually by design. Because you use getutcdate() within your udf, you make it non-deterministic. In sql2k, it is not possible to call getdate or getutcdate in a udf. Sql2k5 has loosen up this restriction but because the udf is marked as non-deterministric, the index seek (on jointable) is not possible. Therefore, the only option left for the engine is do a full scan of the jointable. Thus is the performance difference you see.
Btw, you can see that the perf is exactly identical for inline computed column versus udf computed column in sql2k. You will have to use the getdate_view trick though.
e.g.
Code Snippet
create view _utcdate
as
select getutcdate() [dt]
go
create FUNCTION [dbo].[ComputeFKey](@.startTS datetime)
RETURNS int
--with schemabinding
AS
BEGIN
RETURN (select datediff(minute,@.startTS,dt)/(5)*(5) from _utcdate)
END
go
-- Fast query
select A.KeyCol from MainTable A, JoinTable B where A.CompCol = B.KeyCol
-- Slow query (Nested Loop with table spool)
(1 row(s) affected)
StmtText
--
|--Hash Match(Inner Join, HASH
|--Clustered Index Scan(OBJECT
|--Compute Scalar(DEFINE
|--Clustered Index Scan(OBJECT
(4 row(s) affected)
StmtText
-
select A.KeyCol from MainTable A, JoinTable B where A.UdfCompCol = B.KeyCol
(1 row(s) affected)
StmtText
--
|--Hash Match(Inner Join, HASH
|--Clustered Index Scan(OBJECT
|--Compute Scalar(DEFINE
|--Clustered Index Scan(OBJECT
I kind of figured that would be the answer. However, I still don't understand why the same computation, when done directly in a computed column (but equally non-deterministic), has good performance characteristics. It probably doesn't matter much, since I'm guessing there is no real fix here. I'm just curious.
|||As shown in the query plan for both queries on sql2k, the index/table scan is used. This is the same as in sql2k5 with the udf.
The inline computed column is calculated differently than the udf. Although, it in itself is non-deterministic, the sql2k5 is enhanced (made smarter) to use the available statistics based on the index key to perform an index seek.
In sql2k8, you can force an index seek but it's still not going to be possible for non-deterministic udf.
|||
Hi Oj!
It is not the same as in sql2k5.
I would understand if 2k5 spools the results of the function, but I don't understand why does it spool JoinTable? It does not make any sense...
Query performance problems with join on UDF-based computed column
We have a table with a couple of computed columns. The value of the computed column represents a foreign key reference into another table. We're seeing a major performance problem doing a query joining between the two tables with one of the columns, but not the other. In other words, this kind of query is very fast:
Thanks for the suggestions. I tried changing the UDF to use schema binding, but it didn't seem to effect the query plan at all. Persisting the computed column isn't a possibility here, as its value changes over time.
|||
select * from TheTable A, FKeyTable B
where A.ComputedColumn1 = B.KeyColumn
but this one sends the CPU usage of SQL Server to 99% for a very long time:
select * from TheTable A, FKeyTable B
where A.ComputedColumn2 = B.KeyColumn
The main difference we can see that the computed column that causes problems is based on a UDF, and the other one isn't (but again, both are computed). When I look at the execution plan, the slow query shows a Nested Loop (Inner Join) with a "No Join Predicate" warning, with the estimated # of rows being 70 million (which correponds to the product of 1016 rows in TheTable and 69K rows in FKeyTable). The fast query doesn't have that warning, and shows 1016 rows (the # of rows in TheTable).
Does anyone know why the usage of a UDF would induce this horribly inefficient join behavior? Anything we can do to fix it?
This is SQL Server 2005 SP2, btw.
By default, the engine does not know anything about the udf (i.e. has no statistics). So, bad plan can be generated.
To avoid this, I suggest that you create your udf with schemabinding and persist your computed column.
|||oj,Thanks for the suggestions. I tried changing the UDF to use schema binding, but it didn't seem to effect the query plan at all. Persisting the computed column isn't a possibility here, as its value changes over time.
|||
Scalar udf is executed once per row which tends to be the cause for horrible performance. Now that we know that the values are non-deterministic, performance hit is almost unavoidable.
Can you post some ddl+sample data (insert)+sample code. We might be able to devise a solution.
|||I'll see if I can come up with a simple repro.
What I don't understand is why a regular inline computed column (which is also non-deterministic) performs just fine, but the UDF version performs so horribly. I tried taking the computed column, which is essentially computing a time duration by calling datediff between "now" and a datetime stored in another column of the table, and converting the exact logic into a UDF. Once I do that, it has the same (bad) performance characteristics as my original problem - a Nested Loop with a "No Join Predicate" warning, 70M rows.
BTW, actually querying the column value is quite fast. It's the join that craters the performance.
|||Have you tried explicitly creating a statistic for the column?
e.g.
create statistics _stats on table1(compute_col) with fullscan, norecompute
|||No luck. It complained that it "cannot be used in an index or statistics or as a partition key because it is non-deterministic."|||Kevin, please post the requested info. Don't forget the udf code, too.
Note: you can use either free tool, objectscriptr or qalite from rac4sql.net to generate ddl+sample data.
|||Here's the scripts (as far as I can tell, there's no attachment support on the forums....bummer). This is a seriously simplified version of our tables that reproduce the issue we're having.
The Create script:
Code Snippet
CREATE FUNCTION [dbo].[ComputeFKey](@.startTS datetime)
RETURNS float
with schemabinding
AS
BEGIN
RETURN datediff(minute,@.startTS,getutcdate())/(5)*(5)
END
go
CREATE TABLE [dbo].[JoinTable](
[KeyCol] [int] NOT NULL,
[MoreData] [int] NOT NULL,
CONSTRAINT [PK_JoinTable] PRIMARY KEY CLUSTERED
(
[KeyCol] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
go
CREATE TABLE [dbo].[MainTable](
[KeyCol] [int] IDENTITY(1,1) NOT NULL,
[CompCol] AS ((datediff(minute,[TSCol],getutcdate())/(5))*(5)),
[UdfCompCol] AS ([dbo].[ComputeFKey]([TSCol])),
[TSCol] [datetime] NOT NULL,
CONSTRAINT [PK_MainTable] PRIMARY KEY CLUSTERED
(
[KeyCol] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
go
Script to populate the join table:
Code Snippet
declare @.Start int
declare @.KeyVal int
declare @.RowsToAdd int
declare @.MoreData int
select @.RowsToAdd = 1000
if exists (select * from JoinTable)
select @.Start = max(KeyCol) + 5 from JoinTable
else
select @.Start = 0
select @.KeyVal = @.Start
while (@.KeyVal < (@.Start + @.RowsToAdd * 5))
begin
select @.MoreData = @.KeyVal % 60
insert into JoinTable (KeyCol, MoreData) values (@.KeyVal, @.MoreData)
select @.KeyVal = @.KeyVal + 5
end
Script to populate the main table:
Code Snippet
declare @.Row int
declare @.RowsToAdd int
select @.Row = 0
select @.RowsToAdd = 1000
while (@.Row < @.RowsToAdd)
begin
insert into MainTable (TSCol) values (DateAdd("hh", -1, getutcdate()))
select @.Row = @.Row + 1
end
Queries that demonstrate the issue:
Code Snippet
-- Fast query
select A.KeyCol from MainTable A, JoinTable B where A.CompCol = B.KeyCol
-- Slow query (Nested Loop with table spool)
select A.KeyCol from MainTable A, JoinTable B where A.UdfCompCol = B.KeyCol
The second query joins against the computed column that uses the UDF, and ends up with a Nested Loop with a table spool (a million rows in the Nested loop if you use the default values of the populate scripts - 1000 rows per table).
|||Hi Kevin!
This is probably a bug, because MS SQL 2k behaves fine in this case (the execution plans are identical).
And btw, your function and column have different data types (float and integer).
|||Oops - that was an error in creating my simplified repro case. The actual application schema has a CAST in the computed column. Thanks for catching that (though it doesn't affect the result). Replace the UdfCompCol definition with this:
[UdfCompCol] AS (CONVERT([int],[dbo].[ComputeFKey]([TSCol]),0))
|||
Thank you for posting the requested info. It's helpful to see what you're up against.
Now, to the _bad_ news. This is actually by design. Because you use getutcdate() within your udf, you make it non-deterministic. In sql2k, it is not possible to call getdate or getutcdate in a udf. Sql2k5 has loosen up this restriction but because the udf is marked as non-deterministric, the index seek (on jointable) is not possible. Therefore, the only option left for the engine is do a full scan of the jointable. Thus is the performance difference you see.
Btw, you can see that the perf is exactly identical for inline computed column versus udf computed column in sql2k. You will have to use the getdate_view trick though.
e.g.
Code Snippet
create view _utcdate
as
select getutcdate() [dt]
go
create FUNCTION [dbo].[ComputeFKey](@.startTS datetime)
RETURNS int
--with schemabinding
AS
BEGIN
RETURN (select datediff(minute,@.startTS,dt)/(5)*(5) from _utcdate)
END
go
-- Fast query
select A.KeyCol from MainTable A, JoinTable B where A.CompCol = B.KeyCol
-- Slow query (Nested Loop with table spool)
(1 row(s) affected)
StmtText
--
|--Hash Match(Inner Join, HASH
|--Clustered Index Scan(OBJECT
|--Compute Scalar(DEFINE
|--Clustered Index Scan(OBJECT
(4 row(s) affected)
StmtText
-
select A.KeyCol from MainTable A, JoinTable B where A.UdfCompCol = B.KeyCol
(1 row(s) affected)
StmtText
--
|--Hash Match(Inner Join, HASH
|--Clustered Index Scan(OBJECT
|--Compute Scalar(DEFINE
|--Clustered Index Scan(OBJECT
I kind of figured that would be the answer. However, I still don't understand why the same computation, when done directly in a computed column (but equally non-deterministic), has good performance characteristics. It probably doesn't matter much, since I'm guessing there is no real fix here. I'm just curious.
|||As shown in the query plan for both queries on sql2k, the index/table scan is used. This is the same as in sql2k5 with the udf.
The inline computed column is calculated differently than the udf. Although, it in itself is non-deterministic, the sql2k5 is enhanced (made smarter) to use the available statistics based on the index key to perform an index seek.
In sql2k8, you can force an index seek but it's still not going to be possible for non-deterministic udf.
|||
Hi Oj!
It is not the same as in sql2k5.
I would understand if 2k5 spools the results of the function, but I don't understand why does it spool JoinTable? It does not make any sense...
Query performance problems with join on UDF-based computed column
We have a table with a couple of computed columns. The value of the computed column represents a foreign key reference into another table. We're seeing a major performance problem doing a query joining between the two tables with one of the columns, but not the other. In other words, this kind of query is very fast:
Thanks for the suggestions. I tried changing the UDF to use schema binding, but it didn't seem to effect the query plan at all. Persisting the computed column isn't a possibility here, as its value changes over time.
|||
select * from TheTable A, FKeyTable B
where A.ComputedColumn1 = B.KeyColumn
but this one sends the CPU usage of SQL Server to 99% for a very long time:
select * from TheTable A, FKeyTable B
where A.ComputedColumn2 = B.KeyColumn
The main difference we can see that the computed column that causes problems is based on a UDF, and the other one isn't (but again, both are computed). When I look at the execution plan, the slow query shows a Nested Loop (Inner Join) with a "No Join Predicate" warning, with the estimated # of rows being 70 million (which correponds to the product of 1016 rows in TheTable and 69K rows in FKeyTable). The fast query doesn't have that warning, and shows 1016 rows (the # of rows in TheTable).
Does anyone know why the usage of a UDF would induce this horribly inefficient join behavior? Anything we can do to fix it?
This is SQL Server 2005 SP2, btw.
By default, the engine does not know anything about the udf (i.e. has no statistics). So, bad plan can be generated.
To avoid this, I suggest that you create your udf with schemabinding and persist your computed column.
|||oj,Thanks for the suggestions. I tried changing the UDF to use schema binding, but it didn't seem to effect the query plan at all. Persisting the computed column isn't a possibility here, as its value changes over time.
|||
Scalar udf is executed once per row which tends to be the cause for horrible performance. Now that we know that the values are non-deterministic, performance hit is almost unavoidable.
Can you post some ddl+sample data (insert)+sample code. We might be able to devise a solution.
|||I'll see if I can come up with a simple repro.
What I don't understand is why a regular inline computed column (which is also non-deterministic) performs just fine, but the UDF version performs so horribly. I tried taking the computed column, which is essentially computing a time duration by calling datediff between "now" and a datetime stored in another column of the table, and converting the exact logic into a UDF. Once I do that, it has the same (bad) performance characteristics as my original problem - a Nested Loop with a "No Join Predicate" warning, 70M rows.
BTW, actually querying the column value is quite fast. It's the join that craters the performance.
|||Have you tried explicitly creating a statistic for the column?
e.g.
create statistics _stats on table1(compute_col) with fullscan, norecompute
|||No luck. It complained that it "cannot be used in an index or statistics or as a partition key because it is non-deterministic."|||Kevin, please post the requested info. Don't forget the udf code, too.
Note: you can use either free tool, objectscriptr or qalite from rac4sql.net to generate ddl+sample data.
|||Here's the scripts (as far as I can tell, there's no attachment support on the forums....bummer). This is a seriously simplified version of our tables that reproduce the issue we're having.
The Create script:
Code Snippet
CREATE FUNCTION [dbo].[ComputeFKey](@.startTS datetime)
RETURNS float
with schemabinding
AS
BEGIN
RETURN datediff(minute,@.startTS,getutcdate())/(5)*(5)
END
go
CREATE TABLE [dbo].[JoinTable](
[KeyCol] [int] NOT NULL,
[MoreData] [int] NOT NULL,
CONSTRAINT [PK_JoinTable] PRIMARY KEY CLUSTERED
(
[KeyCol] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
go
CREATE TABLE [dbo].[MainTable](
[KeyCol] [int] IDENTITY(1,1) NOT NULL,
[CompCol] AS ((datediff(minute,[TSCol],getutcdate())/(5))*(5)),
[UdfCompCol] AS ([dbo].[ComputeFKey]([TSCol])),
[TSCol] [datetime] NOT NULL,
CONSTRAINT [PK_MainTable] PRIMARY KEY CLUSTERED
(
[KeyCol] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
go
Script to populate the join table:
Code Snippet
declare @.Start int
declare @.KeyVal int
declare @.RowsToAdd int
declare @.MoreData int
select @.RowsToAdd = 1000
if exists (select * from JoinTable)
select @.Start = max(KeyCol) + 5 from JoinTable
else
select @.Start = 0
select @.KeyVal = @.Start
while (@.KeyVal < (@.Start + @.RowsToAdd * 5))
begin
select @.MoreData = @.KeyVal % 60
insert into JoinTable (KeyCol, MoreData) values (@.KeyVal, @.MoreData)
select @.KeyVal = @.KeyVal + 5
end
Script to populate the main table:
Code Snippet
declare @.Row int
declare @.RowsToAdd int
select @.Row = 0
select @.RowsToAdd = 1000
while (@.Row < @.RowsToAdd)
begin
insert into MainTable (TSCol) values (DateAdd("hh", -1, getutcdate()))
select @.Row = @.Row + 1
end
Queries that demonstrate the issue:
Code Snippet
-- Fast query
select A.KeyCol from MainTable A, JoinTable B where A.CompCol = B.KeyCol
-- Slow query (Nested Loop with table spool)
select A.KeyCol from MainTable A, JoinTable B where A.UdfCompCol = B.KeyCol
The second query joins against the computed column that uses the UDF, and ends up with a Nested Loop with a table spool (a million rows in the Nested loop if you use the default values of the populate scripts - 1000 rows per table).
|||Hi Kevin!
This is probably a bug, because MS SQL 2k behaves fine in this case (the execution plans are identical).
And btw, your function and column have different data types (float and integer).
|||Oops - that was an error in creating my simplified repro case. The actual application schema has a CAST in the computed column. Thanks for catching that (though it doesn't affect the result). Replace the UdfCompCol definition with this:
[UdfCompCol] AS (CONVERT([int],[dbo].[ComputeFKey]([TSCol]),0))
|||
Thank you for posting the requested info. It's helpful to see what you're up against.
Now, to the _bad_ news. This is actually by design. Because you use getutcdate() within your udf, you make it non-deterministic. In sql2k, it is not possible to call getdate or getutcdate in a udf. Sql2k5 has loosen up this restriction but because the udf is marked as non-deterministric, the index seek (on jointable) is not possible. Therefore, the only option left for the engine is do a full scan of the jointable. Thus is the performance difference you see.
Btw, you can see that the perf is exactly identical for inline computed column versus udf computed column in sql2k. You will have to use the getdate_view trick though.
e.g.
Code Snippet
create view _utcdate
as
select getutcdate() [dt]
go
create FUNCTION [dbo].[ComputeFKey](@.startTS datetime)
RETURNS int
--with schemabinding
AS
BEGIN
RETURN (select datediff(minute,@.startTS,dt)/(5)*(5) from _utcdate)
END
go
-- Fast query
select A.KeyCol from MainTable A, JoinTable B where A.CompCol = B.KeyCol
-- Slow query (Nested Loop with table spool)
(1 row(s) affected)
StmtText
--
|--Hash Match(Inner Join, HASH
|--Clustered Index Scan(OBJECT
|--Compute Scalar(DEFINE
|--Clustered Index Scan(OBJECT
(4 row(s) affected)
StmtText
-
select A.KeyCol from MainTable A, JoinTable B where A.UdfCompCol = B.KeyCol
(1 row(s) affected)
StmtText
--
|--Hash Match(Inner Join, HASH
|--Clustered Index Scan(OBJECT
|--Compute Scalar(DEFINE
|--Clustered Index Scan(OBJECT
I kind of figured that would be the answer. However, I still don't understand why the same computation, when done directly in a computed column (but equally non-deterministic), has good performance characteristics. It probably doesn't matter much, since I'm guessing there is no real fix here. I'm just curious.
|||As shown in the query plan for both queries on sql2k, the index/table scan is used. This is the same as in sql2k5 with the udf.
The inline computed column is calculated differently than the udf. Although, it in itself is non-deterministic, the sql2k5 is enhanced (made smarter) to use the available statistics based on the index key to perform an index seek.
In sql2k8, you can force an index seek but it's still not going to be possible for non-deterministic udf.
|||
Hi Oj!
It is not the same as in sql2k5.
I would understand if 2k5 spools the results of the function, but I don't understand why does it spool JoinTable? It does not make any sense...
Subscribe to:
Posts (Atom)