SQL Server Performance Forum – Threads Archive

Large SP vs Small SP

I have a quick and general question for everyone: If many small SPs were combined into a large SP, how does it affect performance if not all the functionalities of that large SP is used all the time, but just parts of it?

---

Yes performance will be affected and its suggested to perform the activity in transaction mode which is easier to manage. For information refer to this linkhttp://www.sql-server-performance.com/stored_procedures.asp Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

True. Better performance using small than large. More in your case.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

---

When a fornt end application to contact the back end db, it is always a fairly heavu operation.
It is a general rule of thumb to keep the trips to the db low.
Therefore, sometimes when there are a bunch of small SPs being accessed regularly, throw them into one large SP. On the application side, create a single object receptacle for the SP. So, I think that is where the performance gain will some.

Sunny Kanaparthy is a software developer with the University of New Orleans, LA, USA.

---

Alternatively you can write a controlling stored procedure which calls the appropriate set of lower level procedures, and get you application to call the controlling one. Cheers
Twan

---

Thanks for the info! <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />

---

Size of stored procedures is not a matter at all, you should pin point how you query the database to reduce locks if its caused by the SP. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

But having one stored procedure which is calling other smaller stored procedure would be better from caching point of view

---

yep and may be marginally worse because of multiple procedure calls I’d say the performance difference is likely to be negligeble, unless we are talking about a huge procedure which has large parts not often used, or loads of sub-procedure calls Cheers
Twan PS I’d go for keeping the procedures small and modular, with a wrapper procedure to make the application simple, and reduce network traffic

---

I probably did not explain myself well, Twan. I meant just the same. wrapper procedure with calls to smaller procedures inside <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />

---

great minds think alike <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /><br /><br />Twan

---

]]>