June 11, 2014 at 2:24 pm
I have a user-defined function that returns all records or selected records from a table based on passed argument called @ContextID that can be null if I want all records to be returned.
SELECT RowID, GroupID, EntityID
FROM Table
WHERE RowID = @ContextID OR GroupID = @ContextID OR EntityID = @ContextID OR @ContextID IS NULL
RowID, GroupID and EntityID are both indexed columns. My problem is that the fact I am testing the nullity of @ContextID makes my function slower even if @ContextID is equal to RowID, GroupID or EntityID. If I rewrite my WHERE clause so it becomes:
WHERE RowID = @ContextID OR GroupID = @ContextID OR EntityID = @ContextID OR 'ABC' IS NULL
then the execution speeds up by a factor of ten. It is like SQL Server is unable to realize that @ContextID is actually a constant and will therefore perform a complete table scan instead of solely relying on an indexed column.
Does someone see a solution to this problem?
June 11, 2014 at 3:47 pm
Check out this article. It explains this type of query and a few ways to make it faster.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply