Dynamic SQL being sent to an SQL Server database isn’t necessarily a bad thing. In fact, it need not even be slower than using a stored procedure. Moreover, it need not be susceptible to SQL Injection, where users coopt your SQL Statements to execute their own queries. This capability is easiest to explain using ADO.Net.
To use dynamic SQL safely, you can create a query much the same as if you are writing a stored procedure.
Here is an example statement you could create:
SELECT po.PurchaseOrderId, po.PurchaseOrderDate, pod.Row, pod.ItemId,
i.ItemDescription, pod.ItemQuantity, pod.Cost,
pod.ItemQuantity * pod.Cost as ExtendedCost
FROM PurchaseOrders po
JOIN PurchaseOrderDetails pod on po.PurchaseOrderId = pod.PurchaseOrderId
JOIN Items I on pod.ItemId = i.Itemid
WHERE po.CustomerId = @CustomerId
AND po.PurchaseOrderDate BETWEEN @StartDate AND @EndDate
Using ADO you create a command object:
- Set the CommandText to this query
- Set the CommandType to text
- Create a parameter object in the command parameters for @CustomerId and set it to the desired value
- Create a parameter object in the command parameters for @StartDate and set it to the desired value
- Create a parameter object in the command parameters for @EndDate and set it to the desired value
Now you can execute the ADO command using ExecuteQuery just as if this was a stored procedure. Because you used parameters. To your ADO code, it works just like you were using a stored procedure. And you have closed the loop for hackers to inject SQL hacks against your database.
So, how does SQL Server use this technique to provide performance? SQL Server takes the entire query and parses it. It wraps it in an SP_EXECSQL command, provides the parameter definitions (just like an SP), and executes the query just like an SP. Because it is encapsulated in an SP_EXECSQL call, the compiler keeps the query execution plan, similar to SPs, but in a different cache, so that when it sees the same command again, it does not have to recompile or determine a query execution plan. It uses the plan already stored in cache.
So, here is the big thing. One reason we like to use these dynamic queries is because they allow us to do things we cannot easily do in a stored procedure. For example, if you wanted to use different sorting criteria for the query above, you could dynamically add an ORDER BY clause, and then call the database. Perhaps you want, instead, to return a dynamic number of rows. So, instead of using a parameter for the top number of rows, you alter the SELECT to SELECT TOP 30, or SELECT top 10. As soon as you modify anything in the query definition, you now have a new query, and it has to be recompiled every time it is even one character different.
Comparing a stored procedure call to one already in cache simply needs to compare against the schema name and the procedure name. So, finding a procedure already in cache takes little text comparison. Since a dynamic query can be quite long, in order to determine if it is already in cache, the complete dynamic query string has to be compared. Clearly, this can be a much bigger job of comparison. Based on simple computer cycles, the stored procedure could be faster in determining if a query plan already exists. With the speed of computers, it’s going to be incredibly difficult to have the difference in speed make an impact on your database performance in many common database environments.
So, if you use dynamic SQL in your database follow these two principles:
- Use parameters for all values that may change in your query for reasons of security and performance.
- Never change the text of your query in a minor way such that you will get hundreds or thousands of different query plans
Cheers,
Ben