I'm running SQL Server 2008. I've built a large search query (contained in a user defined function) with many optional parameters. A simplified version of the result is something like:
我正在运行SQL Server 2008.我已经构建了一个包含许多可选参数的大型搜索查询(包含在用户定义的函数中)。结果的简化版本如下:
Declare @optionalSubTableParameter as userDefinedTableType READONLY
select id
from table t
--here is optional parameter 1 (there are quite a few of these)
outer apply(
select top (1) st.item
from subTable st
inner join @optionalSubTableParameter ostp
on (ostp.value = st.item or ostp.value is null)
where st.index = t.index
and ostp.value is not null
-- also tried: (select top(1) * from @optionalSubTableParameter) is not null
)someParam
where (someParam.item is not null
or (select top(1) * from @optionalSubTableParameter) is null)
So, the problem lies in the execution plan, I seem to be spending time on:
所以,问题在于执行计划,我似乎花时间在:
clustered index seek (clustered)
聚集索引查找(聚集)
[subTable].[IX_subTableIndex..
Cost:8%
I know 8% isn't much, but this gets repeated 6 times (and soon to be a few more), so its already 48% of my execution time.
我知道8%并不多,但这会重复6次(很快就会多一些),所以它已占我执行时间的48%。
I thought by having the check of (@optionalSubTableParameter is not null) within the outer apply, I would avoid computations like the clustered index seek on an unneeded table (when there is no parameter specified). If anyone can help explain if there is a way for me to avoid this computation, that would be great!
我想通过在外部应用中检查(@optionalSubTableParameter不为空),我会避免像不需要的表上的聚集索引搜索一样的计算(当没有指定参数时)。如果有人可以帮助解释我是否有办法避免这种计算,那就太棒了!
Thanks in advance, and let me know if I can clarify anything (this is a grossly simplified version of the query that I'm actually running).
在此先感谢,如果我能澄清任何内容,请告诉我(这是我实际运行的查询的简化版本)。
I apologize if there are any duplicate posts, but I had no luck finding an answer on my own.
如果有任何重复的帖子我很抱歉,但我自己没有找到答案。
1 个解决方案
#1
3
First, this is only 8% of your execution cost. If you have performance issues, keep looking because this won't be the fix.
首先,这只是执行成本的8%。如果您遇到性能问题,请继续关注,因为这不是解决方法。
Second, you are still doing the index seek because of this line:
其次,由于这一行,你仍在进行索引搜索:
where st.index = t.index
其中st.index = t.index
You MAY be able to eliminate it by switching the order of the WHERE clause in that outer apply but I wouldn't count on it.
您可以通过在外部应用中切换WHERE子句的顺序来消除它,但我不会指望它。
Since it's an AND I think it may evaluate both components. Someone else could probably address if that short circuits or not.
因为它是一个AND我认为它可以评估两个组件。如果短路与否,其他人可能会解决。
#1
3
First, this is only 8% of your execution cost. If you have performance issues, keep looking because this won't be the fix.
首先,这只是执行成本的8%。如果您遇到性能问题,请继续关注,因为这不是解决方法。
Second, you are still doing the index seek because of this line:
其次,由于这一行,你仍在进行索引搜索:
where st.index = t.index
其中st.index = t.index
You MAY be able to eliminate it by switching the order of the WHERE clause in that outer apply but I wouldn't count on it.
您可以通过在外部应用中切换WHERE子句的顺序来消除它,但我不会指望它。
Since it's an AND I think it may evaluate both components. Someone else could probably address if that short circuits or not.
因为它是一个AND我认为它可以评估两个组件。如果短路与否,其他人可能会解决。