Tweaking SQL to optimize the query execution plan

[Yash]

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

[Andrew J. Kelly]

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

[Erland Sommarskog]

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

[Yash]

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

[TheSQLGuru]

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

[Erland Sommarskog]

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

[Yash]

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]

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

[Erland Sommarskog]

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

[Erland Sommarskog]

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

[Yash]

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

[Erland Sommarskog]

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

[TheSQLGuru]

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