SQL 2005+

Speed Test: Dynamic SQL vs Stored Procedures

by Richard Carr, published at http://www.blackwasp.co.uk/SpeedTestSqlSproc.aspx

When developing software that uses Microsoft SQL Sever 2005 for data storage, there are various manners in which to retrieve information. Two options are dynamically-generate SQL statements and stored procedures. Does either give a performance gain?

Test Description

Purpose

This test was designed to compare the execution time of dynamically generated SQL select statements and the equivalent stored procedures from within a .NET framework application. Single select statements are often used in stored procedures to retrieve data. This article does not test the used of complex stored procedures where multiple statements are executed on the server.

Test Process Functionality

It was essential that the speed test for the two sets of SQL statements provided exactly the same functionality in both cases. It was also important that a variety of statements was used. For each test run, four SQL statements and equivalent stored procedures were executed against the AdventureWorks sample database. The four processes were as follows:

• Select all data from a single table.
• Select a subset of rows from a table using a where clause.
• Select a subset of rows from three tables using two joins and a where clause.
• Select a subset of rows from three tables using a join, a sub-select and a where clause.

The four SQL statements were held in stored procedures with a parameter where required. These were matched identically in the C# code. The statements were as follows (a question mark denotes a parameter or dynamically generated element):

SELECT * FROM Production.Product

SELECT  *
FROM    Production.Product
WHERE   ProductSubcategoryID = ?

SELECT  *
FROM    Production.Product P LEFT JOIN
        Production.ProductSubcategory S
ON      P.ProductSubcategoryID = S.ProductSubcategoryID LEFT JOIN
        Production.ProductCategory C
ON      S.ProductCategoryID = C.ProductCategoryID
WHERE   C.ProductCategoryID = ?

SELECT  *
FROM    Production.Product P LEFT JOIN Production.ProductSubcategory S
ON      P.ProductSubcategoryID = S.ProductSubcategoryID
WHERE   S.ProductCategoryID IN (
    SELECT  ProductCategoryID
    FROM    Production.ProductCategory
    WHERE   ProductCategoryID = ?)

Looping

The speed of execution of a single select statement is too fast to be measured accurately. To avoid inaccuracies and anomalies, a loop was constructed and the test code executed repeatedly. For each test, the loop was executed ten thousand times.

Timing

The timing of the tests was controlled automatically using the Stopwatch class. Each test was performed repeatedly and the mean average of the results calculated.

Test Conditions

Hardware

The test results shown in the table below are those produced using an Athlon64 3200+ with 2GB RAM. These tests are indicative of further relative test results that were carried out on a variety of equipment.

The tests were executed using three operating systems, each with the latest service packs and patches. These were:

• Windows XP
• Windows Server 2003 R2
• Windows Vista Ultimate

In each test, the software was compiled as a .NET framework 3.5 console application in three configurations:

• Compiled using Visual Studio 2008 Professional Edition
• Compiled using Visual C# 2008 Express Edition

Each test was performed against SQL Server 2005.

Results

This table shows the average timings for the loops for each of the four tests. The first two columns show the results for the dynamically created SQL statements and the stored procedures. The third column shows the percentage difference between the tests.

Conclusion

The results show that the difference in speed between dynamically generated SQL and stored procedures is negligible in most cases. The only noticeable difference is for the final test using a join, a sub-select and a where clause. In this case there is a small decrease in performance when using stored procedures.

These results suggest that the commonly held belief that stored procedures are faster than dynamic SQL is incorrect. This belief often stems from the mistaken idea that SQL Server pre-compiles the execution plan for stored procedures. However, the performance alone should not be a basis for rejecting the use of stored procedures as they do provide additional benefits, particularly for complex procedures that are executed entirely at the server.