Essential Database Practices: Parameterized queries

When you access a relational database the old-fashioned way (not ORM's and LINQ), you generally have three options; you can call stored procedures, you can send queries manually as a concatenated text block or you can send queries manually as a parameterized query.

Option one and three are both valid ways of doing it, but if you ever create queries as concatenated text block WITHOUT parameters you need to stop. I'll explain why by explaining why parameterized queries are good for you.

You should use parameterized queries for three reasons:

• SQL injection
• Query plan caching
• Query plan cache size

 

SQL injection

This is the most obvious. It is hopefully known to all, as much focus has been put on it in recent years. If not, have a look in wikipedia.

 

Query plan caching

Every time a query is executed in SQL Server, it goes through two main steps: parsing/compilation and execution. During the parsing/compilation step, SQL Server creates a plan for how it will run the query. Once this is completed, the query is put in the query plan cache and executed.

In the parsing/compilation step, before doing anything, SQL Server will look in the cache to see if the query plan has already been created. If it has, there is no need to create the plan again. Depending on the query and how many times the query is run, you can save a significant amount of time if the query is cached.

The cache check is done by a look up in a hash table where the form of entry is the entire SQL query, formatting included. The reason I listed query plan caching for parameterized queries, is that the contents of the parameters does not matter, thus running the same query with a number of different parameters will result in using the same plan each time. If you concatenate your queries manually, a new plan will be made for each call. Note that this is important to remember also for the queries you create in your stored procedures. Use sp_executesql instead of just using exec to execute your queries, then you can also add parameters to your dynamic SQL.

 

Query plan cache size

For each query plan created the cache increases in size. The queries go out of the cache when they are not reused, but this can take some time on a busy server. I don't think this usually has a big impact, but creating a large amount of query plans does fill up the cache size. That means they use up an unneccessary amount of your server's ram. Shame on you.

 

If you want to have a look at how much time the parsing/compilation step takes for a given query, run SET STATISTICS TIME ON before running the query. The actual cache can be accessed via the sys.dm_exec_cached_plans view.