To read the article online, visit http://www.4GuysFromRolla.com/webtech/sqlguru/q120899-2.shtml

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.

For More Information on Dynamic Queries in Stored Procedures...
Interested in learning more about dynamic SQL statements within stored procedures? If so, be sure to read: Using Dynamic SQL Statements in Stored Procedures!

Sean


Article Information
Article Title: SQL Guru: The Speed of Dynamic Queries
Article Author: Scott Mitchell
Article URL: http://www.4GuysFromRolla.com/webtech/sqlguru/q120899-2.shtml


Copyright 2017 QuinStreet Inc. All Rights Reserved.
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers