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:
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.
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!
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.
First, this is only 8% of your execution cost. If you have performance issues, keep looking because this won't be the fix.
Second, you are still doing the index seek because of this line:
where 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.
Since it's an AND I think it may evaluate both components. Someone else could probably address if that short circuits or not.