473,392 Members | 1,387 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,392 software developers and data experts.

Tweaking SQL to optimize the query execution plan

My SQL query is like:

SELECT .....
FROM tblCLAIM C INNER join tblXXX .... INNER join tblYYY ....(5 more
joins)
WHERE C.created_date between @start_date and @end_date
AND C.claimant_id=@claimant_id or @claimant_id=-1
AND (more conditions like the claimant condition)

This query is in a stored proc which takes parameters @start_date,
@end_date, @claimant_id, etc. The params have default values so that
if the parameters are not provided through the UI, they become -1. The
@start_date and @end_date are always provided.

There is an index IDX_Claim_date on the date field and
IDX_Claim_Claimant of the claimant_id field. The query seeks the date
index, but not the claim_id index when I see through the execution
plan. The claim_id index gets seeked in the last stage

I temporarily tried changing the condition to AND
C.claimant_id=@claimant_id. This time it seeked both the indexes in
the first steps and merged the results. This tremendously reduced the
number of reads. But this way I expect the @claimant_id to be always
passed which is not what I want.

Is there a way I can modify the query so that if the @claimant_id is
provide both the indexes are seeked like in the second case, else just
the date index is seeked.

Thanks,
Yash
Aug 23 '08 #1
12 3371
There isn't enough details to give an accurate solution but I suggest you
have a look at this:
http://www.sommarskog.se/dyn-search.html
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Yash" <ya****@gmail.comwrote in message
news:3c**********************************@a2g2000p rm.googlegroups.com...
My SQL query is like:

SELECT .....
FROM tblCLAIM C INNER join tblXXX .... INNER join tblYYY ....(5 more
joins)
WHERE C.created_date between @start_date and @end_date
AND C.claimant_id=@claimant_id or @claimant_id=-1
AND (more conditions like the claimant condition)

This query is in a stored proc which takes parameters @start_date,
@end_date, @claimant_id, etc. The params have default values so that
if the parameters are not provided through the UI, they become -1. The
@start_date and @end_date are always provided.

There is an index IDX_Claim_date on the date field and
IDX_Claim_Claimant of the claimant_id field. The query seeks the date
index, but not the claim_id index when I see through the execution
plan. The claim_id index gets seeked in the last stage

I temporarily tried changing the condition to AND
C.claimant_id=@claimant_id. This time it seeked both the indexes in
the first steps and merged the results. This tremendously reduced the
number of reads. But this way I expect the @claimant_id to be always
passed which is not what I want.

Is there a way I can modify the query so that if the @claimant_id is
provide both the indexes are seeked like in the second case, else just
the date index is seeked.

Thanks,
Yash
Aug 23 '08 #2
Yash (ya****@gmail.com) writes:
My SQL query is like:

SELECT .....
FROM tblCLAIM C INNER join tblXXX .... INNER join tblYYY ....(5 more
joins)
WHERE C.created_date between @start_date and @end_date
AND C.claimant_id=@claimant_id or @claimant_id=-1
AND (more conditions like the claimant condition)

This query is in a stored proc which takes parameters @start_date,
@end_date, @claimant_id, etc. The params have default values so that
if the parameters are not provided through the UI, they become -1. The
@start_date and @end_date are always provided.

There is an index IDX_Claim_date on the date field and
IDX_Claim_Claimant of the claimant_id field. The query seeks the date
index, but not the claim_id index when I see through the execution
plan. The claim_id index gets seeked in the last stage

I temporarily tried changing the condition to AND
C.claimant_id=@claimant_id. This time it seeked both the indexes in
the first steps and merged the results. This tremendously reduced the
number of reads. But this way I expect the @claimant_id to be always
passed which is not what I want.

Is there a way I can modify the query so that if the @claimant_id is
provide both the indexes are seeked like in the second case, else just
the date index is seeked.
Yes, but only if you are on SQL 2008. Else you are likely to be in for a
transition to dynamic SQL. I an article (in two versions) on my web site
devoted to problems of this kind: http://www.sommarskog.se/dyn-search.html.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Aug 23 '08 #3
Apologies for not mentioning earlier that the DB is on SQL Server
2000.

-Yash

Erland Sommarskog wrote:
Yash (ya****@gmail.com) writes:
My SQL query is like:

SELECT .....
FROM tblCLAIM C INNER join tblXXX .... INNER join tblYYY ....(5 more
joins)
WHERE C.created_date between @start_date and @end_date
AND C.claimant_id=@claimant_id or @claimant_id=-1
AND (more conditions like the claimant condition)

This query is in a stored proc which takes parameters @start_date,
@end_date, @claimant_id, etc. The params have default values so that
if the parameters are not provided through the UI, they become -1. The
@start_date and @end_date are always provided.

There is an index IDX_Claim_date on the date field and
IDX_Claim_Claimant of the claimant_id field. The query seeks the date
index, but not the claim_id index when I see through the execution
plan. The claim_id index gets seeked in the last stage

I temporarily tried changing the condition to AND
C.claimant_id=@claimant_id. This time it seeked both the indexes in
the first steps and merged the results. This tremendously reduced the
number of reads. But this way I expect the @claimant_id to be always
passed which is not what I want.

Is there a way I can modify the query so that if the @claimant_id is
provide both the indexes are seeked like in the second case, else just
the date index is seeked.

Yes, but only if you are on SQL 2008. Else you are likely to be in for a
transition to dynamic SQL. I an article (in two versions) on my web site
devoted to problems of this kind: http://www.sommarskog.se/dyn-search.html.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx
Aug 23 '08 #4
Dynamic SQL is indeed likely your best solution here. I have several
clients that 'tried' your method of coding (actually their OR condition was
with NULLs such as OR @myvar IS NULL). Several orders of magnitude
improvement in average query performance.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Yash" <ya****@gmail.comwrote in message
news:e0**********************************@v1g2000p ra.googlegroups.com...
Apologies for not mentioning earlier that the DB is on SQL Server
2000.

-Yash

Erland Sommarskog wrote:
>Yash (ya****@gmail.com) writes:
My SQL query is like:

SELECT .....
FROM tblCLAIM C INNER join tblXXX .... INNER join tblYYY ....(5 more
joins)
WHERE C.created_date between @start_date and @end_date
AND C.claimant_id=@claimant_id or @claimant_id=-1
AND (more conditions like the claimant condition)

This query is in a stored proc which takes parameters @start_date,
@end_date, @claimant_id, etc. The params have default values so that
if the parameters are not provided through the UI, they become -1. The
@start_date and @end_date are always provided.

There is an index IDX_Claim_date on the date field and
IDX_Claim_Claimant of the claimant_id field. The query seeks the date
index, but not the claim_id index when I see through the execution
plan. The claim_id index gets seeked in the last stage

I temporarily tried changing the condition to AND
C.claimant_id=@claimant_id. This time it seeked both the indexes in
the first steps and merged the results. This tremendously reduced the
number of reads. But this way I expect the @claimant_id to be always
passed which is not what I want.

Is there a way I can modify the query so that if the @claimant_id is
provide both the indexes are seeked like in the second case, else just
the date index is seeked.

Yes, but only if you are on SQL 2008. Else you are likely to be in for a
transition to dynamic SQL. I an article (in two versions) on my web site
devoted to problems of this kind:
http://www.sommarskog.se/dyn-search.html.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000:
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Aug 23 '08 #5
Yash (ya****@gmail.com) writes:
Apologies for not mentioning earlier that the DB is on SQL Server
2000.
Well, one of the versions of my article covers SQL 2000, and as Kevin
says, dynamic SQL is likely to be your best bet. If dynamic SQL is out
of reach for because of security reasons, the article dicsusses some
tricks you can try.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Aug 23 '08 #6
I just read the article at http://www.sommarskog.se/dyn-search-2005.html.
I have one question about dynamic SQL using sp_executesql.

EXEC search_orders @product_id=591
and EXEC search_orders @order_id=8000

Assuming search_orders builds the WHERE part of the query dynamically
and uses 2-part table naming notation, would these two queries create
2 separate plans of which one uses the index on product_id and the
other on order_id?
Would the query plans used for these 2 statements be cached for reuse
when invoked again possibly with different values?

Thanks,
Yash

Aug 24 '08 #7
what's different in SQL 2008 that helps?
"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...
Yash (ya****@gmail.com) writes:
>My SQL query is like:

SELECT .....
FROM tblCLAIM C INNER join tblXXX .... INNER join tblYYY ....(5 more
joins)
WHERE C.created_date between @start_date and @end_date
AND C.claimant_id=@claimant_id or @claimant_id=-1
AND (more conditions like the claimant condition)

This query is in a stored proc which takes parameters @start_date,
@end_date, @claimant_id, etc. The params have default values so that
if the parameters are not provided through the UI, they become -1. The
@start_date and @end_date are always provided.

There is an index IDX_Claim_date on the date field and
IDX_Claim_Claimant of the claimant_id field. The query seeks the date
index, but not the claim_id index when I see through the execution
plan. The claim_id index gets seeked in the last stage

I temporarily tried changing the condition to AND
C.claimant_id=@claimant_id. This time it seeked both the indexes in
the first steps and merged the results. This tremendously reduced the
number of reads. But this way I expect the @claimant_id to be always
passed which is not what I want.

Is there a way I can modify the query so that if the @claimant_id is
provide both the indexes are seeked like in the second case, else just
the date index is seeked.

Yes, but only if you are on SQL 2008. Else you are likely to be in for a
transition to dynamic SQL. I an article (in two versions) on my web site
devoted to problems of this kind:
http://www.sommarskog.se/dyn-search.html.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000:
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Aug 24 '08 #8
Yash (ya****@gmail.com) writes:
I just read the article at http://www.sommarskog.se/dyn-search-2005.html.
I have one question about dynamic SQL using sp_executesql.

EXEC search_orders @product_id=591
and EXEC search_orders @order_id=8000

Assuming search_orders builds the WHERE part of the query dynamically
and uses 2-part table naming notation, would these two queries create
2 separate plans of which one uses the index on product_id and the
other on order_id?
Yes.
Would the query plans used for these 2 statements be cached for reuse
when invoked again possibly with different values?
Yes.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Aug 24 '08 #9
Smokey Gindel (no****@nospam.com) writes:
what's different in SQL 2008 that helps?
In SQL 2008 you can stack a lot of

WHERE (x = @x OR @x IS NULL)
AND (y = @y OR @y IS NULL)

and get away with it, provided that you add

OPTION (RECOMPILE)

to force recompilation of the query each time. This hint was added already
in SQL 2005, but in SQL 2005 it does not work properly. The query is
optimised as the variables are parameters that could change value
during execution, which of course cannot happen. In SQL 2008, the query
is optimized as the variables are constants, and you are likely to get a
plan that fits the current search terms optimally.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Aug 24 '08 #10
Thanks Erland, dynamic SQL with sp_executesql works for me. It now
uses the best possible indexes as I put only the relevant conditions
in the WHERE clause. There is something I noticed.
The proc takes company_csv as a parameter. The comman separated
company_ids from this string are blown up into a condition like:
AND ( C.SI_OWNERID=381 OR C.SI_OWNERID=385 OR C.SI_OWNERID=384
OR ...)

These go in the SQL string and cannot be passed as parameters to the
SQL string as we do not know how many values are separated by comma.

I invoked the proc with @company_csv='381,385,384,383,382,386'. It
recompiled the query and returned the results. I executed the proc
again with the same value. As expected it did not recompile again as
the SQL string was the same.
Then when I changed the CSV to @company_csv='381,385,384,383,382,387',
I was expecting the query to recompile, but it did'nt. It should have,
as the SQL string for the WHERE clause which does not have paarmeters,
is being changed.
Can we explain this behavior?

Thanks,
Yash
Aug 25 '08 #11
Yash (ya****@gmail.com) writes:
Thanks Erland, dynamic SQL with sp_executesql works for me. It now
uses the best possible indexes as I put only the relevant conditions
in the WHERE clause. There is something I noticed.
The proc takes company_csv as a parameter. The comman separated
company_ids from this string are blown up into a condition like:
AND ( C.SI_OWNERID=381 OR C.SI_OWNERID=385 OR C.SI_OWNERID=384
OR ...)

These go in the SQL string and cannot be passed as parameters to the
SQL string as we do not know how many values are separated by comma.

I invoked the proc with @company_csv='381,385,384,383,382,386'. It
recompiled the query and returned the results. I executed the proc
again with the same value. As expected it did not recompile again as
the SQL string was the same.
Then when I changed the CSV to @company_csv='381,385,384,383,382,387',
I was expecting the query to recompile, but it did'nt. It should have,
as the SQL string for the WHERE clause which does not have paarmeters,
is being changed.
Can we explain this behavior?
It could be a case of auto-parameterisation. If you look at the query plan,
and see that the values are replaced by @1 etc, you have
autoparameterisation.

Another way to handle the list is to use a function that unpacks the list
into a table. I have an article that covers many such methods on
http://www.sommarskog.se/arrays-in-sql.html.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Aug 25 '08 #12
Oooh, I wasn't aware of the flaw in 2k5 nor the fix in 2k8. Thanks Erland!!

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...
Smokey Gindel (no****@nospam.com) writes:
>what's different in SQL 2008 that helps?

In SQL 2008 you can stack a lot of

WHERE (x = @x OR @x IS NULL)
AND (y = @y OR @y IS NULL)

and get away with it, provided that you add

OPTION (RECOMPILE)

to force recompilation of the query each time. This hint was added already
in SQL 2005, but in SQL 2005 it does not work properly. The query is
optimised as the variables are parameters that could change value
during execution, which of course cannot happen. In SQL 2008, the query
is optimized as the variables are constants, and you are likely to get a
plan that fits the current search terms optimally.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000:
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Aug 27 '08 #13

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Mahesh Hardikar | last post by:
Hi , Oracle 8.1.7.0.0 on HP-UX 11.0 I am a newbie as far as PL-SQL is concerned . I have Sybase/MSSQL T-SQL background. We have a report which uses a select statement . This select...
3
by: Paul Mateer | last post by:
Hi, I have been running some queries against a table in a my database and have noted an odd (at least it seems odd to me) performance issue. The table has approximately 5 million rows and...
6
by: Muharram Mansoorizadeh | last post by:
Hi there, I've a table with 18 millions of recordes shaped like this : Code nvarchar(80) , State int , school int , class int , Term nvarchar(80) The following query takes too long to run ( more...
3
by: Will Atkinson | last post by:
Hi All, I'm a relative newbie to SQL Server, so please forgive me if this is a daft question... When I set "Show Execution Plan" on in Query Analyzer, and execute a (fairly complex) sproc, I...
4
by: mjuricek | last post by:
I'm having some problems to optimize my stored procedure (select statement with joins) What I'm trying to do is calculate total work. My situation: I have 3 tables I'm using -Input (char...
6
by: Ryan | last post by:
I came across a situation that I've been unable to explain and was hoping somebody had an answer: I had written an update query which was taking about 8 seconds to run and considered it too...
7
by: stig | last post by:
hi. coming from postgresql, i am used to textual references to most of the things i do with the database. i feel a little lost with all the graphical. i have few questions regarding MS SQL 2000...
5
by: mathieu.page | last post by:
Hi, I often have recursives queries in my applications, like in this simplified example : req1 : SELECT EmployeNo, EmployeName, EmployePhone FROM Employe; req2 :
5
by: sqlgirl | last post by:
Hi, We are trying to solve a real puzzle. We have a stored procedure that exhibits *drastically* different execution times depending on how its executed. When run from QA, it can take as...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.