March 8, 2013 at 1:08 pm
I am creating a query within a stored procedure and I am trying to create a parameter where I can pass a value to the query, but I want the parameter to be SARGable.
The field is TIMES_TESTED SMALLINT and is either a 1 (first-time test taker) or NULL (retest). I don't have any control of the data in the table, but I have complete control over how the stored procedure works.
So the lazy way to do this would be to accept a parameter for the stored procedure:
... , @firstTime smallint = NULL -- NULL any record, 0 retests, 1 first-time
And in my query's WHERE clause, use this abomination:
AND (@firstTime IS NULL OR ISNULL(TIMES_TESTED, 0)=@firstTime)
So how could I improve this?
March 8, 2013 at 1:13 pm
This sounds a lot like a "catch all" query. check out Gail's blog post about this type of query here. 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/
March 8, 2013 at 1:23 pm
Dynamic SQL, what a drag.
Thanks for the link!
March 8, 2013 at 3:17 pm
If that's the only parameter just break your procedure into two queries.
IF (@firstTime IS NULL or ISNULL(TIMES_TESTED,0)=@firstTime)
BEGIN
...FIRST SARGable QUERY
END
ELSE
BEGIN
...SECOND SARGable QUERY
END[/CODE]
This structure is really only application if you have a very small decision tree but we use this structure at work to make sure we get seeks instead of scans.
March 8, 2013 at 10:03 pm
ryan.mcatee (3/8/2013)
Dynamic SQL, what a drag.Thanks for the link!
Dynamic SQL is the usually the best way to go when you need to do catch-all searches with multiple optional parameters.
Don't underestimate the performance impact of badly done catch-all stored procedures. It is fairly typical (in my experience) for a small number of catch-all procedures to be using most of the CPU and IO resources that SQL Server is using. I was looking at a system this week where one procedure was using 60% of the total CPU used and another was using 30%.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy