Donnerstag, 1. März 2007 22:22
I have a question related to performance. Which would be better performance wise.
Lets say I have a Stored Procedure which does a process, and within the stored procedure I need to collect information as it relates to the process. So this may require me to obtain data from several other tables in order to complete the process. Is it better performance wise to execute another stored procedure to collect the data I need (may be one or more then one stored procedure) or am I better performance wise to just embbed the SQL statements within the single Stored Procedures.
Ie Stored Procedure up_ProcessAccount
Stored Procedure up_ProcessAccount
Select @ACCOUNT_NAME = NAME FROM ACCOUNT_NAME_MASTER WHERE X=@X
Select @DETAIL_1 = DETAIL_1 .... FROM ACCOUNT_HOLDER_DETAILS WHERE X = @X
I hope I am explaining my question specific enough.
Donnerstag, 1. März 2007 23:01For what its worth....If I had a question about performance I would test the two options in each case and not rely on a general assumption. If the code was complex and is also be used in other procedures I would tend toward writing the code once.
Donnerstag, 1. März 2007 23:25
I would agree. I am just trying to eek out as much performance as possible as this is a central stored procedure that will be called many times at once. I was hoping that someone might offer some experience from maybe looking at this particular question before.
Thanks for the response!
Freitag, 2. März 2007 01:57There is some overhead involved with executing a stored procedure -- It's very unlikely to improve performance. If you don't have some other need for using a stored proc (common code, etc.) I wouldn't bother.
Freitag, 2. März 2007 06:53Moderator
Typically, you would only create additional Stored Procedures if the code would be re-used from other entry points, that is, called elsewhere in the application. The primary benefits for Stored Procedures is compiled and stored execution plans, and re-usability.
If the code is only called in one place, put it all in that one place -especially when it is only single table queries as in your example.