The Wagner Blog
Development Notes, News and Trivia









Subscribe to "The Wagner Blog" in Radio UserLand.

Click to see the XML version of this web page.

Click here to send an email to the editor of this weblog.
 

 

When did Dynamic SQL get such a bad name?

If you have been a business application programmer for longer than 5 years you may have noticed - especially in the Microsoft world of tools - that dynamic SQL statements have garnered somthing of a bad rap. I suppose much of this is due to the support material published by MS through its various channels which overall repeats the same mantra "stored procs are good dynamic SQL is bad". Two questions come to mind:

  1. Is this a true statement?
  2. What might be the reason for this such a mantra?

To answer the first question, I would like to refer you to two resources that I thought were just absolutely right on for this subject matter. Please have a look at Erland Sommerskog's wonderful work on SQL Server. Especially the article dealing with Dynamic SQL. By the way, Erland is a MS SQL Server Most Valuable Professional (MVP), which means that he really knows his stuff.

The second person whose work I admire and who has committed himself to dynamic SQL is Frans Bouma. Some might say that Frans has gone completely over the edge because his wonderful ORM tool LLBL Gen Pro works entirely on the basis of dynamic SQL. Now that's what I call total commitment. But before you start to discount this product, please note that in the world of Java, which I consider something of a cousin to .NET, it is a frequent practice to use dynamic SQL in order to make an object model completely database independant. In my experience many Java practioners pride themselves on their ability to write code that is not dependant on a specific vendor. So if another, better, tool comes along it can be incorporated very easily.

Even if you are not as committed to cutting your strings to server based stored procs, there are plenty of situations in which dynamic SQL can be advantageous. One specific example that comes to mind is the typical reporting interface. Each report needs some sort of criteria definition screen to gather up the users search criteria. These values are then processed to either drive a stored proc or generate dynamic SQL. In any case, the objective is to return a resultset as the basis of a given report.  Oftentimes the criteria screens can become quite large. Users seem to want to slice and dice their data in all sorts of ways. If there is a possible criteria that could even be remotely of some value in a report, leave it to a user to find it. It won't take long.

In past projects I have employed 3 different methods to aid in the processing of report criteria. Recently I decided to see how these methods might rank amongst one another by running a timing benchmark based on some of the examples cited by Frans Bouma .

Here are my findings:

The testbed was once again my trusty old P3 400 Mhz laptop, running Visual Studio 2003 and SQL Server 2000.  Just as I did in my previous tests, I was interested in a relative measurement between different coding approaches. Therefore it didn't matter that my code was not executing at the speed of a 3GHz machine. All that mattered was how much faster one approach executed in comparison to another.

The code itself ran 2 stored procedures and a dynamic SQL statement for 10,000 iteration each, against the Northwind database. The proc consists of a SELECT statement with three possible parameter values in it's WHERE clause. While executing the 10,000 iterations, the testbed code randomly creates values for the parameters, producing SELECT statements with one, two or all three parameters filled as well as cases were all of them are null.

As you can imagine, a proc with 3 params is not nearly as complex as some reporting procs that contain 10, 20 or even more parameters. Frequently the criteria values being searched in such cases are foreign keys to other tables. That has an even greater impact on the performance of the proc since the SQL Query Engine may or may not be able to employ proper indices depending on how the statement was composed.

Since I used Frans' test bed, I also picked his original proc as one of the starting points. The procs were not created dynamically (obviously)  and therefore had to include a mechanism that allowed the code to supply all or just a few of the parameters. The two methods I knew would provide a solution were the use of the COALESCE () function, which is the route that Frans had gone,  or the use of a simple OR clause. The following proc definitions contain examples of each:

COALESCE route:

CREATE PROCEDURE pr_Orders_SelectMultiWCustomerEmployeeShipper
 @sCustomerID nchar(5),
 @iEmployeeID int,
 @iShipperID int
AS
SELECT  *
FROM Orders
WHERE 
 CustomerID = COALESCE(@sCustomerID, CustomerID)
 AND
 EmployeeID = COALESCE(@iEmployeeID, EmployeeID)
 AND
 ShipVia = COALESCE(@iShipperID, ShipVia)

OR clause approach

CREATE PROCEDURE pr_Orders_SelectMultiWCustomerEmployeeShipper
 @sCustomerID nchar(5),
 @iEmployeeID int,
 @iShipperID int
AS
SELECT  *
FROM Orders
WHERE 
       (CustomerID= @sCustomerID OR @sCustomerID IS NULL)
       AND
      (EmployeeID= @iEmployeeID OR @iEmployeeID IS NULL)
       AND
      (ShipVia= @iShipperID OR @iShipperID IS NULL)

Of course the dynamic SQL statement was created on the fly and typically would read something like

SELECT * FROM ORDERS WHERE CustomerID = XYZ and ShipperID = X

So without further delay, here are the results of the test.

Procedure Type
Iterations
Max No Of Records
Execution Time (sec)
Delta (+ / - )
Dynamic SQL
10,000
830
3:41 (221)
NA
SP with COALESCE ( )
10,000
830
4:19 (259)
+ 15%
SP with OR clause
10,000
830
5:29 (329)
+ 33%

As you can see, the dynamic SQL approach is much faster. Considering that my typical report criteria screen can contain way more than 3 parameters, I think its safe to say that there are definetly places where dynamic SQL is still  a hands down winner.

As for my second question, I suppose I can only speculate about the answer. The Product Groups responsible for tools like SQL Server are not responsible for monitoring the content of information released by other areas of Microsoft, like MS Publishing or MSDN for example. Perhaps that can lead to certain biases in the published universe of MS information. Of course if I had a Machiavellian streak in me I would guess that any methodology that doesn't foster database independent design and instead ties large chunks of application logic to SQL Server is only in the best interest of MS. But I wouldn't do that - would I ?

 


Click here to visit the Radio UserLand website. © Copyright 2004 Thomas Wagner.
Last update: 5/2/2004; 7:24:20 PM.
This theme is based on the SoundWaves (blue) Manila theme.