DBA > Articles

Performance Implications of Parameterized Queries

By: David Berry
To read more DBA articles, visit http://dba.fyicenter.com/article/

In the Oracle community, there is very strong guidance concerning the use of parameterized queries (or ‘bind variables’, as the Oracle community refers to them). Oracle’s Senior Technology Architect, Tom Kyte, states bluntly in his book “If I were to write a book about how to build non-scalable Oracle applications, ‘Don’t Use Bind Variables’ would be the first and last chapter”. Guidance on database application design does not get much clearer than that. As a consequence, the Oracle community has a very strong tradition of encouraging the use of parameterized queries and discouraging the use of dynamic SQL.

What about SQL Server though? The guidance in the SQL Server community is murkier than in Oracle. A handful of articles do encourage the use of parameterized queries. More recently, much has been written on the use of parameterized queries as a way to protect from SQL injection attacks. However, there seem to be very few articles that dig into the performance implications of dynamic SQL versus parameterized queries in a SQL Server environment. Unlike the Oracle community where numerous articles provide a detailed analysis of the performance difference between the two approaches, very few seem to be available on SQL Server.

This article has two purposes. The first is to investigate how significant of a performance difference exists between the use of dynamic SQL and parameterized queries in a SQL Server environment. I will go beyond simply measuring elapsed time to also show the performance difference in the amount of CPU time consumed on the database and the difference in plan cache memory consumed.

The second purpose of this article is to guide the reader through how the analysis was conducted. It will demonstrate some uses of SQL Server dynamic management views (DMVs), and how the information in these views can be used to capture data such that alternative solutions can be compared. By understanding what data is available and how performance can be measured, developers and DBAs can better analyze different choices and understand in detail their performance implications.

What Are Parameterized Queries?
There are two basic ways to write a SQL statement; the first is to explicitly specify the values for each parameter in the WHERE clause, as shown in the statement below:
SELECT ZipCode, Latitude, Longitude, City, State, County
FROM dbo.UsZipCodes
WHERE ZipCode = '54911';
Listing 1: A simple query

As with every statement it processes, SQL Server will create an execution plan and process the statement. The trouble starts when you submit a second, very similar query to the database like the one shown below:
SELECT ZipCode, Latitude, Longitude, City, State, County
FROM dbo.UsZipCodes
WHERE ZipCode = '75243';

Listing 2: Another simple query
Although the two statements above vary only by a value in the WHERE clause, they are not an exact character for character match. This means SQL Server must parse this second statement as well.

The second way to write the SQL statement is to replace the values in the WHERE clause with variable placeholders, as shown below:

SELECT ZipCode, Latitude, Longitude, City, State, County
FROM dbo.UsZipCodes
WHERE ZipCode = @zip_code;
Listing 3: A parameterized query The Simple Parameterization Feature

In cases in which values are specified explicitly, as in Listings 1 and 2, SQL Server invokes a feature known as ‘simple parameterization’. Simple parameterization is designed to reduce the resource cost associated with parsing SQL queries and forming execution plans by automatically parameterizing queries. With simple parameterization, SQL Server actually creates two execution plans for the first query (Listing 1). The first execution plan is a shell plan containing a pointer to the second execution plan. Its primary purpose is to help SQL Server locate the ‘actual’ execution plan, should this exact same query be submitted to the database again. The second execution plan contains the instructions about how to process the query. The following query displays what is contained in SQL Server’s plan cache, and can be used to show how SQL Server has split the execution plan into two pieces.

SELECT
cp.objtype AS PlanType,
cp.refcounts AS ReferenceCounts,
cp.usecounts AS UseCounts,
st. text AS SQLBatch,
cp.plan_handle,
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan (cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text (cp.plan_handle) AS st;
Listing 4: Query to display SQL Server’s plan cache

If we run DBCCC FREEPROCCACHE to clear the plan cache, and then run only the first query (Listing 1) , here are the results for the plan cache query:

Full article...


Other Related Articles

... to read more DBA articles, visit http://dba.fyicenter.com/article/