Optimizing Parameterization for Query Plan Reuse

By far the most common cause of avoidable compilation is lack of parameterization. Consider the following SQL statement:

SELECT C.cid, SUM(O.order_amount) 
  FROM Customers C, Orders O WHERE C.cid = 
  O.cid AND C.cid = 1234 GROUP BY C.cid

This statement computes the total number (amount) of orders for a given customer, who we identify by restricting the cid column to a particular value. In the preceding SELECT statement, that value is the embedded constant 1234. If this statement appears frequently in this application, you might see several similar statements being executed, differing from one another only in the value of the embedded constant. For example, another statement may look like

SELECT C.cid, SUM(O.order_amount) 
  FROM Customers C, Orders O WHERE C.cid = 
  O.cid AND C.cid = 5678 GROUP BY C.cid

These two statements would benefit from reusing the same query plan; however, because the statements’ text is different, SQL Server doesn’t consider the statements to be the same and doesn’t reuse the plan. In such a case, if you examine the usecounts column of the master..syscacheobjects table, you’ll find many plans with a usecount value of 1, indicating lack of plan reuse.

Several techniques let you write applications in a way that lets the query optimizer reuse the same plan for similar statements. By definition, stored procedures and their query plans are reusable. So, if you can create a stored procedure that uses parameters in place of the variable input, SQL Server can reuse the plan for that stored procedure. For peak performance, your application should invoke stored procedures by using a Remote Procedure Call (RPC) request. RPC requests bypass much of the statement parsing and parameter processing in SQL Server and are faster than using the T-SQL EXECUTE statement. You can find the RPC call syntax in SQL Server 2000 Books Online (BOL). Or, as Listing A shows, you can use sp_executesql, which lets SQL Server reuse the same plan for the two EXEC statements by separating out the constants from the text of the statement. Alternatively, you can improve plan reuse by using a prepare/execute model provided by data access APIs such as ODBC or OLE DB. You can find overviews of the appropriate APIs for ODBC in the Microsoft article “How to Prepare and Execute a Statement (ODBC),” and for OLE DB in the MSDN OLE DB Programmer’s Reference.