Performance test: dynamic generation of SQL statements vs Stored Procedure

When software development uses Microsoft sql2005 to store data, there are multiple methods to retrieve information. There are dynamically generated SQL statements and stored procedures. But is their performance the same?

Download the test program and source code

Test Description:

The purpose of this test is to compare whether the execution time of dynamic SQL query statements and stored procedures in. NET applications is equal.

Functions during testing

In both cases, speed testing provides identical functions for the two sets of SQL statements, which is crucial. It is equally important to use various statements. For each test run, take the adventureworks sample database as an example. Four SQL statements are equivalent to a stored procedure.

The four processes are as follows:

Query all data in a single table;

Use the where condition to query a row in a table

Use two joins and the where condition in the three tables to query certain data.

Use join, subquery, and where statements in three tables to query certain data.

Loop

It is too fast to execute a single SELECT statement and must be accurately measured. To avoid errors and exceptions, build the loop and repeat the test code. Each test is executed 10 thousand times. The time of the time test is automatically controlled by the stopwatch. Each test has multiple and average calculation results.

Test conditions

Hardware

The test results are shown in the following table, which are the athlon64 3200 + 2 GB memory used in production. These tests show that the relative further test results are performed on different devices. The test is executed in three operating systems, each of which is the latest service pack and patch. These are:

Windows XP Windows Server 2003 R2 Windows Vista Ultimate

In each test, the software is compiled into a. NET Framework 3.5 console application.

Compiled using Visual Studio 2008 Professional Edition

Compiled using Visual C #2008 express Edition

Result

The following table shows the average time of loop statements in four experiments. The first two columns indicate the dynamic generation of SQL statements and stored procedures, and the third column indicates the different percentages of the two:

Dynamic SQL stored procedure difference

Simple select 21.83 s 21.82 s 0.05%

Select with WHERE clause 2.51 s 2.49 s 0.80%

Select with joins 25.08 s 25.22-0.56%

Select with sub-select 24.68 s 26.47-7.25%

Conclusion

In most cases, the difference between dynamically generated SQL statements and stored procedures cannot be ignored. The obvious difference is that join, subquery, and where statements are used at the end. In this case, the performance of stored procedures is decreased slightly. These results indicate that the speed of a stored procedure is incorrect because it is faster than that of dynamic SQL statements. This idea often stems from the wrong idea that SQL Server executes the stored procedure in advance. However, performance alone should not be the basis for rejecting stored procedures, because they do provide additional benefits.