Tweaking SQL to optimize the query execution plan

**Andrew J. Kelly** · Aug 23 '08, 03:15 PM

Re: Tweaking SQL to optimize the query execution plan

There isn't enough details to give an accurate solution but I suggest you
have a look at this:

Dynamic Search Conditions in T-SQL

http://www.sommarskog.se/dyn-search.html

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors

"Yash" <yashgt@gmail.c omwrote in message
news:3c073f88-58cd-4836-9683-121512798d81@a2 g2000prm.google groups.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_Claim ant 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

**Erland Sommarskog** · Aug 23 '08, 03:15 PM

Re: Tweaking SQL to optimize the query execution plan

Yash (yashgt@gmail.c om) 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_Claim ant 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, esquel@sommarsk og.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

**Yash** · Aug 23 '08, 03:15 PM

Re: Tweaking SQL to optimize the query execution plan

Apologies for not mentioning earlier that the DB is on SQL Server
2000.

-Yash

Erland Sommarskog wrote:

Yash (yashgt@gmail.c om) 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_Claim ant 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, esquel@sommarsk og.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

**TheSQLGuru** · Aug 23 '08, 03:25 PM

Re: Tweaking SQL to optimize the query execution plan

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" <yashgt@gmail.c omwrote in message
news:e02df559-04b3-41cb-9a14-9263f9e7b49f@v1 g2000pra.google groups.com...

Apologies for not mentioning earlier that the DB is on SQL Server
2000.
>
-Yash
>
Erland Sommarskog wrote:

>Yash (yashgt@gmail.c om) 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_Claim ant 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, esquel@sommarsk og.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

**Erland Sommarskog** · Aug 23 '08, 03:55 PM

Re: Tweaking SQL to optimize the query execution plan

Yash (yashgt@gmail.c om) 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, esquel@sommarsk og.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

**Yash** · Aug 24 '08, 09:25 AM

Re: Tweaking SQL to optimize the query execution plan

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

**Smokey Gindel** · Aug 24 '08, 10:55 AM

Re: Tweaking SQL to optimize the query execution plan

what's different in SQL 2008 that helps?

"Erland Sommarskog" <esquel@sommars kog.sewrote in message
news:Xns9B03AF2 F5C77AYazorman@ 127.0.0.1...

Yash (yashgt@gmail.c om) 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_Clai mant 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, esquel@sommarsk og.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/prodinfo/previousversions/books.mspx

>

**Erland Sommarskog** · Aug 24 '08, 08:05 PM

Re: Tweaking SQL to optimize the query execution plan

Yash (yashgt@gmail.c om) 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, esquel@sommarsk og.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

**Erland Sommarskog** · Aug 24 '08, 08:15 PM

Re: Tweaking SQL to optimize the query execution plan

Smokey Gindel (nospam@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, esquel@sommarsk og.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

**Yash** · Aug 25 '08, 08:35 AM

Re: Tweaking SQL to optimize the query execution plan

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=38 1 OR C.SI_OWNERID=38 5 OR C.SI_OWNERID=38 4
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='3 81,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='3 81,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

**Erland Sommarskog** · Aug 25 '08, 10:55 AM

Re: Tweaking SQL to optimize the query execution plan

Yash (yashgt@gmail.c om) 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=38 1 OR C.SI_OWNERID=38 5 OR C.SI_OWNERID=38 4
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='3 81,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='3 81,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-parameterisatio n. If you look at the query plan,
and see that the values are replaced by @1 etc, you have
autoparameteris ation.

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, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

**TheSQLGuru** · Aug 27 '08, 03:15 PM

Re: Tweaking SQL to optimize the query execution plan

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" <esquel@sommars kog.sewrote in message
news:Xns9B04E12 B6E6EDYazorman@ 127.0.0.1...

Smokey Gindel (nospam@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, esquel@sommarsk og.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/prodinfo/previousversions/books.mspx

>

Tweaking SQL to optimize the query execution plan

Tweaking SQL to optimize the query execution plan

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment