General Stored Procedure Query Plan Question

Question

General Stored Procedure Query Plan Question

William Plourde

SSCrazy

Points: 2035
More actions
December 16, 2011 at 6:52 am

#252162

I have not been able to find an answer for this question. When creating a stored procedure with multiple IF THEN branches where each branch has a different query.such as the examples below is Option B preferable to Option A from a query plan perspective ?
I prefer Option B because I can track the changes to the stored procedure scripts individually in our source control system, plus a change to one query does not effect the other stored procedures. Also, the stored procedures tend to be a little simpler, easier to maintain, and can be called individually in other contexts if necessary.
The only disadvantage that I can see is that Option A only requires one procedure and Option B requires 4.
My primary concern is that SQL Server may generate a query plan for the stored procedure in Option A based upon one the query in one of the branches, but when called with parameters such that it executes a different branch query it may not use an accurate query plan.
Does anybody know how SQL Server behaves in this scenario ? Is this something that is a legit concern or is SQL Server smart enough to manage the query plans for the the individual queries within the IF branches in Option A ?
Also, what do you commonly do in your development environments ?
Thanks,
Bill, Charlotte NC
Option A - using one procedure with multiple queries
IF <condition> BEGIN
SELECT...[small query with simple plan here]
ELSE <condition> BEGIN
SELECT..[medium query where with different query plan]
ELSE
SELECT..[large query with different query plan
END
Option B - using a main driver procedure with an individual procedure for each query.
IF <condition> BEGIN
EXEC usp_Query1
ELSE <condition> BEGIN
EXEC usp_Query2
ELSE
EXEC usp_Query3
END

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jim Mackenzie Hall of Fame Points: 3728 More actions · Answer 1

With Option B you will have to deal with SQL Server compiling the procedure every time you execute it as there may have been changes to the SPs within the if statement.

Can you test both and compare results?

SQL SERVER Central Forum Etiquette[/url]

William Plourde SSCrazy Points: 2035 More actions · Answer 2

This is for a commercial software install so the stored procedures will not be changing except for system upgrades. So if the SPs do not change after the initial install I don't think recompilations are a problem.

I would like to test it but I have no idea how. I can mock up some tables, data, and different queries but I'm not sure of what metrics to measure.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Option B is your best bet. The only issue with it is that you can eventually end up with 128 subversions of that search proc. Assuming it stops way before that it's a great way to go.

The other option : http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries

William Plourde SSCrazy Points: 2035 More actions · Answer 4

thanks and btw this is not for searching. Is for support of our software application and is all internal stuff. So there are only 4 branches/queries in the IF stmt, no dynamic sql, and the queries are not too dissimilar so this is a fairly simple instance of the general issue of how best to organize stored procedures.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Ninja's_RGR'us

SSC Guru

Points: 294069

December 16, 2011 at 8:15 am

#1422544

ok, option b is my preffered.

Grant Fritchey SSC Guru Points: 398881 More actions · Answer 6

I'd go for Option B too.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

brendan woulfe Hall of Fame Points: 3888 More actions · Answer 7

I've used option A before with pretty good success. But my situation looked a little different. I had a search parameter for a stored procedure that the developers didn't anticipate being used too often.

So instead of joining on the search table and doing where search_field = @search or @search is null for every call to that stored procedure I looked to see if @search was null and changed the select based on the results. Worked really well....sounds like that's similar to what you were going for.

I never tried option b for that scenario but I did try my hand at a couple other ways of doing it and option a was the best. I'll have to try option b if that ever comes up again.

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 8

Are you sure the queries in Option A have significant differences in execution plans (thus performance)? Also, if it is, can't they be rewritten for better performance?

If the performance difference is negligible, I would prefer Option A.