调整SQL以优化查询执行计划 [英] Tweaking SQL to optimize the query execution plan
问题描述
我的SQL查询如下：
SELECT .....
FROM tblCLAIM C INNER join tblXXX .... INNER join tblYYY .. ..（5更多
加入）
在@start_date和@end_date之间的C.created_date
AND C.claimant_id=@claimant_id或@ claimant_id = -1
AND（更多条件，如索赔人条件）
此查询位于存储过程中，该过程采用参数@start_date，
@ end_date，@ claimant_id等。参数具有默认值，因此如果没有通过UI提供参数，则它们变为-1。总是提供
@start_date和@end_date。
日期字段上有一个索引IDX_Claim_date和
IDX_Claim_Claimant of claimant_id字段。当我查看执行
计划时，查询会查找日期
索引，但不会搜索claim_id索引。在最后阶段寻找claim_id索引
我暂时尝试将条件更改为AND
C.claimant_id=@claimant_id。这次它在第一步中寻找了两个指数，并合并了结果。这极大地减少了读取数量。但是这种方式我希望@claimant_id总是通过
，这不是我想要的。
有没有办法可以修改查询，以便如果@claimant_id是
提供两个索引，就像在第二种情况下一样，否则只需要
寻找日期索引。
谢谢，
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
推荐答案
没有足够的细节来提供准确的解决方案，但我建议你好了b $ b看看这个：
http://www.sommarskog.se/dyn-search.html
-
Andrew J. Kelly SQL MVP
固体质量导师
" Yash" < ya **** @ gmail.comwrote in message
news：3c ************************** ******** @ a2g2000p rm.googlegroups.com ...
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...
我的SQL查询类似于：
SELECT .....
FROM tblCLAIM C INNER join tblXXX .... INNER join tblYYY ....（5更多
加入）
WHERE C.created_date介于@start_date和@end_date之间
AND C.claimant_id=@claimant_id或@ claimant_id = -1
AND（更多条件类似于索赔人条件）
此查询位于存储过程中，该过程采用参数@start_date，
@end_date，@ claimant_id等。参数具有默认值，以便
如果没有通过UI提供参数，它们将变为-1。总是提供
@start_date和@end_date。
日期字段上有一个索引IDX_Claim_date和
IDX_Claim_Claimant of claimant_id字段。当我查看执行
计划时，查询会查找日期
索引，但不会搜索claim_id索引。在最后阶段寻找claim_id索引
我暂时尝试将条件更改为AND
C.claimant_id=@claimant_id。这次它在第一步中寻找了两个指数，并合并了结果。这极大地减少了读取数量。但是这种方式我希望@claimant_id总是通过
，这不是我想要的。
有没有办法可以修改查询，以便如果@claimant_id是
提供两个索引，就像在第二种情况下一样，否则只需要
寻找日期索引。
谢谢，
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
Yash（ya****@gmail.com）写道：
Yash (ya****@gmail.com) writes:
我的SQL查询类似于：
SELECT .....
FROM tblCLAIM C INNER join tblXXX .... INNER join tblYYY ....（另外5个
加入）
在@start_date和@end_date之间的C.created_date
AND C.claimant_id=@claimant_id或@ claimant_id = -1
AND（更多条件，如索赔人条件）
此查询位于存储过程中它接受参数@start_date，
@end_date，@ claimant_id等。参数有默认值，所以如果参数是
s不是通过UI提供的，它们变为-1。总是提供
@start_date和@end_date。
日期字段上有一个索引IDX_Claim_date和
IDX_Claim_Claimant of claimant_id字段。当我查看执行
计划时，查询会查找日期
索引，但不会搜索claim_id索引。在最后阶段寻找claim_id索引
我暂时尝试将条件更改为AND
C.claimant_id=@claimant_id。这次它在第一步中寻找了两个指数，并合并了结果。这极大地减少了读取数量。但是这种方式我希望@claimant_id总是通过
，这不是我想要的。
有没有办法可以修改查询，以便如果@claimant_id是
提供两个索引，就像在第二种情况下一样，否则只需要
寻找日期索引。
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.
是的，但仅限于你使用的是SQL 2008.否则你很可能会进入动态SQL转换。我在我的网站上发表了一篇文章（两个版本）
专门讨论这类问题： http://www.sommarskog.se/dyn-search.html 。
-
Erland Sommarskog，SQL Server MVP， es****@sommarskog.se
SQL Server联机丛书的链接：
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
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
道歉之前没有提到DB在SQL Server上
2000.
-Yash
Erland Sommarskog写道：
Apologies for not mentioning earlier that the DB is on SQL Server
2000.
-Yash
Erland Sommarskog wrote:
Yash（ya****@gmail.com）写道：
Yash (ya****@gmail.com) writes:
我的SQL查询是喜欢：
SELECT .....
FROM tblCLAIM C INNER join tblXXX .... INNER join tblYYY ....（5更多
加入）
在@start_date和@end_date之间的C.created_date
AND C.claimant_id=@claimant_id或@ claimant_id = -1
AND（更多条件，如索赔人条件）
此查询位于存储过程中，该过程采用参数@start_date，
@end_date， @claimant_id等。参数具有默认值，因此如果没有通过UI提供参数，则它们变为-1。总是提供
@start_date和@end_date。
日期字段上有一个索引IDX_Claim_date和
IDX_Claim_Claimant of claimant_id字段。当我查看执行
计划时，查询会查找日期
索引，但不会搜索claim_id索引。在最后阶段寻找claim_id索引
我暂时尝试将条件更改为AND
C.claimant_id=@claimant_id。这次它在第一步中寻找了两个指数，并合并了结果。这极大地减少了读取数量。但是这种方式我希望@claimant_id总是通过
，这不是我想要的。
有没有办法可以修改查询，以便如果@claimant_id是
提供两个索引，就像在第二种情况下一样，否则只需要
寻找日期索引。
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.
是的，但前提是您使用的是SQL 2008.否则，您可能需要进行动态SQL转换。我在我的网站上发表了一篇文章（两个版本）
专门讨论这类问题： http://www.sommarskog.se/dyn-search.html 。
-
Erland Sommarskog，SQL Server MVP， es****@sommarskog.se
SQL Server联机丛书的链接：
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
这篇关于调整SQL以优化查询执行计划的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
