May 14, 2013 at 6:50 am
Hi
I have a question regarding the performance gains (if any) of splitting an SP into smaller SP's
The procedure in question has some logic resembling the following:
DECLARE
@Input INT
,@Param NVARCHAR(64)
IF @Input = 1
SELECT
A,B,C,D,E
FROM
SomeTable
WHERE
A = @Param
ELSE IF @Input BETWEEN 2 AND 4
SELECT
A,B,C,D,E
FROM
SomeTable
WHERE
CASE @InputWHEN 2 THEN B
WHEN 3 THEN C
WHEN 4 THEN D
= @Param
So basically depending on what the value on @Input is the SP will be filtering on different columns.
My question is this in terms of performance and indexing ect will I get any performance gain from splitting this
SP up essentially creating a separate SP for 2 - 4:
DECLARE
@Input INT
,@Param NVARCHAR(64)
IF @Input = 1
SELECT
A,B,C,D,E
FROM
SomeTable
WHERE
A = @Param
ELSE IF @Input = 2
SELECT
A,B,C,D,E
FROM
SomeTable
WHERE
B = @Param
ELSE IF @Input = 3
SELECT
A,B,C,D,E
FROM
SomeTable
WHERE
C = @Param
ELSE IF @Input = 4
SELECT
A,B,C,D,E
FROM
SomeTable
WHERE
D = @Param
And then using a separate SP to call the one that needs to be used:
IF @Input = 1
EXEC SP_1
ELSE IF @Input = 2
EXEC SP_2
ELSE IF @Input = 3
EXEC SP_3
Ect....
I would be interested in peoples views and experience?
Disclaimer the code above is obviously very much oversimplified however I'm after advice on the concept rather than the actual query tuning. 😀
Cheers
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
May 14, 2013 at 7:07 am
that classical question was answered long ago ;-):
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
May 14, 2013 at 7:27 am
Yes, you probably do want to split that up.
This blog post's probably closer than the catch-all one
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
May 14, 2013 at 7:29 am
Eugene Elutin (5/14/2013)
that classical question was answered long ago ;-):http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Hi Eugene
Thanks for taking the time to have a look.
I may be being a little obtuse here so my apologies if this is the case however Gail's article with reference to catch all queries seems to be quite different to the situation above i.e in the article the where clause is looking at:
WHERE ProductID = CASE WHEN @product IS NULL THEN ProductID ELSE @product
where as my SP is actually looking at different columns based on the input, if @Input = 2 then filter on column B, If @Input = 3 then filter on column C, its not actually doing a catch all at the end?
Also the article doesn't mention the option of splitting up the SP in to multiple SP's and calling a tailored SP depending on the variable..:ermm:
Edit:
Just seen Gail's post now, thanks Gail I'll take a look at that now 🙂
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
May 14, 2013 at 7:38 am
Gail, Thanks you were spot on with that excellent article.
Bookmarked for future reference 😀
Cheers
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
May 14, 2013 at 8:26 am
GilaMonster (5/14/2013)
Yes, you probably do want to split that up.This blog post's probably closer than the catch-all one
http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/
Yeah, my wrong. Clicked the wrong link and didn't bother to check...
Thank You for clarifying it.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply