SQL Server Performance Forum – Threads Archive

Sproc design

I think I have asked this question before but I could not find the thread so I can post additional questions there. So please forgive me if I have to start a new one. The issue is about sproc performance. I asked from that thread about this structure: CREATE PROC mySproc
@intMode int
AS
IF @intMode =1
SELECT * FROM tblX
ELSE
IF @intMode =2
SELECT * FROM tblY
ELSE
SELECT * FROM tblY
(Again) Would this sproc be recompiled everytime the code execution path changes due to changes in @intMode values. That is, say the sproc was compiled for @intMode=1 then compiled again(?) for @intMode=2, does calling mySproc for @intMode=1 causes it to recompile or sql will use the previous execution plan? Does sql creates 3 execution plan for this query? If I’ll create sproc for each of the select statements, would it give me a faster mySproc, since the queries on all path will be compiled and stay compiled even if @intMode changes? I know I can try this myself, and I’m about to do that, but I need your expertise to verify my tests. Thanks in advance!

---

Hi ya, This exact proc would only have one execution plan and it will be efficient for all three cases, no recompiles and no problems with parameter sniffing however if the selects are more complex and in particular if they use any parameter values passed in then it may result in sub-optimal performance. SQL will never create multiple execution plans for different parameters if each if branch contained a significant amount of code then you can get some benefit by splitting it into multiple procs, since the compile time would be less. Also if the code in the if branch was required elsewhere then again factoring it out into its own proc may be useful for reusability… hope that helped? Cheers
Twan

---

If you’re only doing straight SELECTs, for all columns, and with no filtering, then compilation is never an issue. In a more general sense, you should ask yourself why you’re using a generic procedure to retrieve very specific data. Why would this …
DECLARE @MyVar INT
……………………
EXEC MySproc @intmode = @MyVar
… be preferable to …
If @myVar = 1
EXEC MySprocOnTableX
ELSE
EXEC MySprocOnTableY
…?

---

Hi Twan! It’s good to hear that no re-compilation will be done and performance is the same for all paths. I think I have read somewhere that I might get a recompile. So it;s safe to say that even if I have 100 paths (just for the sake of argument) my sproc would still perform as if it have no paths at all? Hi Ariaan! It looked my example was not a very good one. Actually, what I have are similar queries with varying joins and where clause. So now it leads us to the next question (especially w/what Adriaan replied), what would be best if based on @intMode I would need to get 5 cols for a one mode and anothter "diff" set of 5 cols for another mode such that I have this: if @intmode=1
SELECT col1, col2, col3, col4, col5 from tblX join tbly…
else if @intmode=2
SELECT col1, col2, col3, tbly.col1, tbly.col2 from tblX join tbly… To give you an idea why I have such query is that I want the developer using the sproc to just remember one query and it’s parameters. ANd then the sproc will decide which specific queries to execute (or call in the case where each path is another sproc). ?

---

That’s bad idea in my opinion. I believe it is better to have different procedures supporting different functionality with its own set of parameters. Besides performance reasons, I think this is more intuitive clear design. If you name procedures properly and consistently developers may find memorizing and even guessing (deducting from the naming rule) stored procedure name and parameters quite intuitive.

---

Hi ya,<br /><br />I concur that this doesn’t sound like a good way to go… separate procs for separate purposes would seem more sensible for support reasons if nothing else… a bit like suggesting to put all data into a single table, can be done, but not the cleanest way to go <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /><br /><br />If you had a hundred paths then the size of the proc will mean that a single proc for each path would compile more quickly, but it would not result in a recompile when called with different parameters<br /><br />Cheers<br />Twan

---

That’s a really good advice. Until that advice came I was really lost in deciding which way to go best; having a single sproc with different paths or having hundreds of sproc. THe primary reason why I thought of going such way as having a "consolidated" sproc is the number of sprocs that will be created because of an application’s requirement. The application needs quite a lot number of queries. Thanks a lot![<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]

---

]]>