Stored Procedures vs SQL Select Statement.

Question

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

Exec up_GetAccountName

Exec up_GetAccountHolderDetails

.....

or

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.

 

Thanks!

Answer 1

For 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.

Answer 2

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!

Answer 3

There 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.

Answer 4

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.