The SQL Guru Answers your Questions...
Today's question comes from Claude R.:
|
Glad to hear that the SQL Guru is back! This question was bantered around
on ASPFreeForAll for a while, but we couldn't come to a conclusion. So, I
thought that I'd pass it on to you.
The basic question is whether or not a stored procedure will still exhibit
increased performance over regular recordset queries when the stored
procedure relies on parameters that are passed from other stored procedures.
For example, in one of my scripts I need to dynamically build the WHERE
statement of my SQL queries. Currently I use an include file. For
increased performance I'd like to convert the query to a stored procedure
but then I can't use the include file. Using flow/control statements isn't
desirable because this code is used in a number of different queries.
The generally accepted solution was to use a stored procedure to generated
the WHERE statement as a string and then pass that string to the "parent"
stored procedure as a parameter. You would then execute the stored
procedure.
The validity of this approach was then questioned by Chris Morse:
"I have been looking for a good solution to the 'dynamic sp' problem for a
while and am yet to find one I am happy with. Every one suggests doing an
execute on the dynamic SQL statement, but it seems to me that this defeats
the purpose of having a stored procedure in the first place. The database
engine certainly has to do the procedure compilation and optimization each
time the sp is executed, so there goes your performance out the window."
I asked the list to respond to this observation, but nobody did. So, I
thought that the SQL Guru might have an answer.
|
Hmm. This topic usually starts some heated discussions, so let me begin by putting on my asbestos suit.... :)
|
The basic question is whether or not a stored procedure will still exhibit
increased performance over regular recordset queries when the stored
procedure relies on parameters that are passed from other stored procedures.
|
If you use the EXEC command to dynamically build the SQL statment, then SQL Server has to parse and compile the statement. You certainly lose the benefit of a precompiled query plan.
Beginning with SQL 7.0, there is a handy little proc called sp_executeSQL that will accept parameterized queries. It will also attempt to re-use query plans, so if you execute the same statement multiple times, chances are you'll get a reused query plan. Cool, eh?
|
but it seems to me that this defeats
the purpose of having a stored procedure in the first place. The database
engine certainly has to do the procedure compilation and optimization each
time the sp is executed, so there goes your performance out the window.
|
In most cases, parse and compile time is negligible compared to the execution time of the query, and is not the primary reason to use stored procedures. There are so many other reasons:
- Reduces client-server network traffic (
SELECT blah blah blah blah blah blah... vs. EXEC sp_foo)
- helpful for isolating business rules
- helpful for modularizing code and setting security
- helps isolate the application from schema changes (modifying an SP is a WHOLE lot easier than modifying and recompiling and redistributing an application.)
So, when you're making the decision between SP and non SP, remember that there are other reasons besides parse and compile time.
Sean
