Wednesday, 29 January 2014

Dynamic SQL Performance comparison in SQL Server

Dynamic SQL statements

A dynamic SQL statement is constructed at execution time, for which different conditions generate different SQL statements. It can be useful to construct these statements dynamically when you need to decide at run time what fields to bring back from SELECT statements; the different criteria for your queries; and perhaps different tables to query based on different conditions.

These SQL strings are not parsed for errors because they are generated at execution time, and they may introduce security vulnerabilities into your database. Also, SQL strings can be a nightmare to debug, which is why I have never been a big fan of dynamically built SQL statements; however, sometimes they are perfect for certain scenarios.

NOTE: Most importantly, the Dynamic SQL Queries in a variable are not compiled, parsed, checked for errors until they are executed.

Declare a string variable, something like:

declare @sql varchar(5000)
Set that variable to be the completed SQL string you want (as a string, and not actually querying... so you embed the row-name you want using string concatenation).

Then call: exec(@sql)
There have been a lot of discussions on the usage of dynamic SQL in SQL server. Most of them were stressing on SQL injection. Will dynamic SQL cause any performance difference? Interesting question!! Shall we check with an example?

Following are two simple procedures created in Northwnd database to fetch company names. One using dynamic SQL and the other one without dynamic SQL.

The following SQL Server script that could be useful to collect the measures-

Does it mean that dynamic SQL is something that should be avoided ? Never meant that !! The tests were not exhaustive enough to prove that, and sp_executesql is not at all counted in these tests. The only point to emphasize here is that there could be performance implications while using dynamic SQL, and this should be known and counted during application development.

No comments:

Post a Comment