July 19, 2012 at 9:40 am
I've been looking around for various ways to do SELECTS in a stored procedure with optional parameters. I found repeated cases, in many different SQL forums of people suggesting this approach:
Create PROCEDURE srchakc.spAkcesIDSrch
@AkcesitPred varchar(2) = '',
@Akcesit int = 0,
@Rok smallint = 0
...
SELECT AkcesAutoID, Rok, AkcesitPred, Akcesit
FROM dbo.Akces
WHERE (@AkcesitPred = '' or AkcesitPred = @AkcesitPred)
AND (@Akcesit = 0 or Akcesit = @Akcesit)
AND (@Rok = 0 or Rok = @Rok)
It works (and quite quickly on my machine, but it's very lightly loaded), and I have to admit, from a coding standpoint it looks slick and tidy. But the query plan always shows a table scan, even though there are indexes that can help. When I write the query out 'longhand', like this:
SELECT AkcesAutoID, Rok, AkcesitPred, Akcesit
FROM dbo.Akces
WHERE Akcesit = @Akcesit
simply omitting the missing parameter(s), it does use an index and the estimated number of rows goes from over 50,000 (the entire table) to three (in my test case), which is, in fact, the actual number of rows returned. Response is pretty much instant in both cases, but as time goes on, I expect this machine to be under greater load, and in any case, I don't want to write crappy code that just happens to run quickly now, when I can write good code that will always run quickly. The other method that seems to be clean is to write a separate SELECT statement for each combination of present or missing parameters, i.e., 2^(number of parameters) SELECT statements, and code to execute the correct one, depending on which parameters are present.
It's more work to write and more work to maintain, and doesn't lend itself to any sort of code reuse. But according to Grant Fritchey in his SQL in the City presentations, code reuse in SQL Server is kind of a lost cause anyway - difficult to do well and often carrying a performance hit.
It would also be possible (and maybe fastest in execution) to write a separate stored procedure for each combination of parameters, but that approach would make the number of procedures balloon insanely.
Anybody have any thoughts on this?
July 19, 2012 at 9:46 am
You could also use dynamic sql to build the query that needs to be executed based on the parameters passed to the stored procedure.
July 19, 2012 at 9:52 am
Lynn Pettis (7/19/2012)
You could also use dynamic sql to build the query that needs to be executed based on the parameters passed to the stored procedure.
Yes, I've done that in a number of cases so far, and gotten my hand smacked for it. It works quite nicely, but many people have told me to use dynamic SQL only as a last resort, so I'm trying to learn other ways to do things. I come from a developer background, so assembling strings is almost second nature, but it seems to be frowned upon in the SQL Server community.
July 19, 2012 at 9:53 am
Check out Gail's blog post on catch all queries. This explains a couple of ways to go about this.
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/
July 19, 2012 at 9:59 am
Dynamic SQL or OPTION RECOMPILE (if you're on 2008 SP2 or above). Test the recompile, make sure it really does help.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 19, 2012 at 10:01 am
pdanes (7/19/2012)
Lynn Pettis (7/19/2012)
You could also use dynamic sql to build the query that needs to be executed based on the parameters passed to the stored procedure.Yes, I've done that in a number of cases so far, and gotten my hand smacked for it. It works quite nicely, but many people have told me to use dynamic SQL only as a last resort, so I'm trying to learn other ways to do things. I come from a developer background, so assembling strings is almost second nature, but it seems to be frowned upon in the SQL Server community.
The reason it's usually smacked down is over-usage and a failure to protect from SQL Injection. However, in the case of 'catch-all search queries', it's basically an optimization requirement these days.
Check out the following blog post from Gail Shaw and it'll walk you through the process:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
In particular, note the use of sp_executesql with parameters to make sure injection cannot occur.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 19, 2012 at 10:02 am
pdanes (7/19/2012)
Lynn Pettis (7/19/2012)
You could also use dynamic sql to build the query that needs to be executed based on the parameters passed to the stored procedure.Yes, I've done that in a number of cases so far, and gotten my hand smacked for it. It works quite nicely, but many people have told me to use dynamic SQL only as a last resort, so I'm trying to learn other ways to do things. I come from a developer background, so assembling strings is almost second nature, but it seems to be frowned upon in the SQL Server community.
It comes down to an "it depends" with me. I think the reason many frown on it is that many who use it don't code defensively and the dynamic code has SQL Injection issues. If written properly, I don't see why it couldn't be used.
Edit: Bit by the quote bug.
July 19, 2012 at 10:21 am
I've just assembled this:
alter PROCEDURE [srchAkc].spAkcesIDSrch
-- Add the parameters for the stored procedure here
@AkcesitPred varchar(2) = '',
@Akcesit int = 0,
@Rok smallint = 0
AS
BEGIN
SET NOCOUNT ON
-- Direction variable
declare @tw tinyint
set @tw = 0
if LEN(@AkcesitPred) > 0 -- AkcesitPred?
set @tw = 1
if @Akcesit > 0 -- Akcesit?
if @Rok > 0 -- Rok?
-- Execute the proper statement, according to which parameters were passed
if @tw = 1
SELECT AkcesAutoID, Rok, AkcesitPred, Akcesit
FROM dbo.Akces
WHERE AkcesitPred = @AkcesitPred
else
if @tw = 2
SELECT AkcesAutoID, Rok, AkcesitPred, Akcesit
FROM dbo.Akces
WHERE Akcesit = @Akcesit
else
if @tw = 3
SELECT AkcesAutoID, Rok, AkcesitPred, Akcesit
FROM dbo.Akces
WHERE AkcesitPred = @AkcesitPred
AND Akcesit = @Akcesit
else
if @tw = 4
SELECT AkcesAutoID, Rok, AkcesitPred, Akcesit
FROM dbo.Akces
WHERE Rok = @Rok
else
if @tw = 5
SELECT AkcesAutoID, Rok, AkcesitPred, Akcesit
FROM dbo.Akces
WHERE AkcesitPred = @AkcesitPred
AND Rok = @Rok
else
if @tw = 6
SELECT AkcesAutoID, Rok, AkcesitPred, Akcesit
FROM dbo.Akces
WHERE Akcesit = @Akcesit
AND Rok = @Rok
else
if @tw = 7
SELECT AkcesAutoID, Rok, AkcesitPred, Akcesit
FROM dbo.Akces
WHERE AkcesitPred = @AkcesitPred
AND Akcesit = @Akcesit
AND Rok = @Rok
END
With only three parameters, it's not that messy. It seems quite readable to me, even though there is rather a lot of duplication. Is the RECOMPILE or dynamic a better option in general than this, or would I have to pick through the details of the query plans in all the different cases to answer that?
July 19, 2012 at 10:23 am
For a 'final answer', you'd have to pick through the query plans. However, the reason in general that the dynamic/recompile options are preferred is because of how often you typically would need to repeat the logic for an if-switchable choice like above. The maintenance becomes unbearable.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 19, 2012 at 10:24 am
OPTION RECOMPILE is your friend in this case, if you can use it. or read up on parameterization.
Alternatively, I believe if you declare a new set of variables and set the value to those of the passed variables it has the same effect but please test (make sure you make sure you remove the sp from the cache first - DBCC freeproccache)
i.e.
Create PROCEDURE srchakc.spAkcesIDSrch
@AkcesitPred varchar(2) = '',
@Akcesit int = 0,
@Rok smallint = 0
DECLARE @AkcesitPred_2 varchar(2), @Akcesit_2 int, @Rok_2 smallint
SET @AkcesitPred_2 = @AkcesitPred
SET @Akcesit_2 = @Akcesit
SET @Rok_2 = @Rok
SELECT AkcesAutoID, Rok, AkcesitPred, Akcesit
FROM dbo.Akces
WHERE (@AkcesitPred_2 = '' or AkcesitPred = @AkcesitPred_2)
AND (@Akcesit_2 = 0 or Akcesit = @Akcesit_2)
AND (@Rok_2 = 0 or Rok = @Rok_2)
SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
July 19, 2012 at 10:26 am
Sean Lange (7/19/2012)
Check out Gail's blog post on catch all queries. This explains a couple of ways to go about this.http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]
Thanks, that also helped clear up some confusion about injection for me. I have been assembling query strings without really understanding how to proof them against injection - I sort of thought an assembled string was always vulnerable to it, and you would guard against it by controlling permissions on the procedure. This makes it a lot clearer.
July 19, 2012 at 10:28 am
pdanes (7/19/2012)
Sean Lange (7/19/2012)
Check out Gail's blog post on catch all queries. This explains a couple of ways to go about this.http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]
Thanks, that also helped clear up some confusion about injection for me. I have been assembling query strings without really understanding how to proof them against injection - I sort of thought an assembled string was always vulnerable to it, and you would guard against it by controlling permissions on the procedure. This makes it a lot clearer.
You are welcome but honestly the credit goes to Gail (aka GilaMonster) who posted in this thread shortly after I did. 😛 All I did was point to the great resource.
_______________________________________________________________
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/
July 19, 2012 at 10:33 am
pdanes (7/19/2012)
I've just assembled this:
I would recommend not doing that. If you want to go that way, create multiple procs and call them from within the IFs. The selects within IFs are going to give you some fun parameter sniffing problems (and not in a good way)
http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 19, 2012 at 10:37 am
pdanes (7/19/2012)
Thanks, that also helped clear up some confusion about injection for me. I have been assembling query strings without really understanding how to proof them against injection - I sort of thought an assembled string was always vulnerable to it, and you would guard against it by controlling permissions on the procedure. This makes it a lot clearer.
This might help even more:
http://sqlinthewild.co.za/index.php/2009/04/03/dynamic-sql-and-sql-injection/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 19, 2012 at 10:38 am
I just tested my original version (with all parameters and the AND/OR constructs) with the RECOMPILE instruction and it uses the indexes, just as when I coded the individual statements manually. That would seem to be my best bet - simplest, cleanest and correct execution plans. Thank you everyone for the insights and links, I believe I can take it from here.
Pete
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply