Parameterized Queries

Use parameterized queries wherever you can. They help SQL Server reuse execution plans so that user queries execute faster after the first compile. Parameterized queries can significantly cut CPU cycles, improving overall throughput. For example, the following code suggests “slower” and “faster” options for a simple UPDATE:

 --slower UPDATE Customers.dbo.Orders SET Orders. Customer = 'Shapiro'  WHERE [Orders]. CustomerID = 'A346' --faster UPDATE Customers.dbo.Orders SET Orders. Customer = ? WHERE [Orders]. CustomerID = ?

The second statement will obviously not run. The idea is that if SQL Server can simply store the entire second statement in an execution plan and then simply insert the parameters you send, it will not have to process an execution plan for the entire statement every time you transmit the query If a number of separate queries have the same “shape,” then one plan can be used for all queries that share that shape.

First, you can also use a special system stored procedure that lets you create and pass an entire T-SQL statement as a parameter. The system proc is sp_executesql (see Appendix); it can be used in standard T-SQL scripts and in trigger and stored procedure code. Consider the following code:

 DECLARE @SQL1 NVARCHAR (750) DECLARE @P1 NVARCHAR(10) DECLARE @P2 NVARCHAR (500) SET @SQL1 = N'UPDATE Customers.dbo.Orders SET Orders. Customer = @P2 WHERE [Orders]. CustomerID = @P1' SET @P1 = 'A346' SET @P2 = 'Shapiro' EXEC sp_executesql @SQL1, @P1, @p2

You can take this now a step further and not only used simple stored procedures (see Chapter 14) for all queries, but use the sp_executesql stored procedure inside a stored procedure, as follows:

 USE [Modelize] SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo]. [CopyLeadToLDTable]   @leadTable nvarchar(10),   @OperatorA int,   @ClientIDA int,   @LeadSourceA int,   @LeadStatusA int,   @SrcnotesA varchar (250),   @ClientAgeA int,   @FlagNewRecA int,   @LocationIDA int,   @SalesIDA int,   @MailOnlyA bit,   @FencoCIDA int,   @JobIDA int AS  DECLARE @InsertString NVARCHAR (500) DECLARE @OrderMonth INT -- Build the INSERT statement. SET @InsertString = 'INSERT INTO' + @leadTable + '(operid, clientid, lsource, lstatus, srcnotes, clientage, newrecflag, locid, salesid, mailonly, FencoCID, JobID)' + 'VALUES (@OperatorB, @ClientIDB, @LeadSourceB, @LeadStatusB, @SrcnotesB, @ClientAgeB, @FlagNewRecB, @LocationIDB, @SalesIDB, @MailOnlyB, @FencoCIDB, @JobIDB)' EXEC sp_executesql @InsertString, N' @OperatorB int, @ClientIDB int, @LeadSourceB int, @LeadStatusB int, @SrcnotesB varchar (250), @ClientAgeB int, @FlagNewRecB int, @LocationIDB int, @SalesIDB int, @MailOnlyB bit, @FencoCIDB int, @JobIDB int', @OperatorA, @ClientIDA, @LeadSourceA, @LeadStatusA, @SrcnotesA, @ClientAgeA, @FlagNewRecA, @LocationIDA, @SalesIDA, @MailOnlyA, @FencoCIDA, @JobIDA

Putting all queries into stored procedures helps get code visible to the optimizer in advance of production calls. This does not mean that you need to rush out and convert everything to stored procedures (see Chapter 18, which discussed the Database Tuning Advisor and delves further into this subject).

You can also use the “recompile” hint, but be aware that it is better suited for long queries where the cost of compilation can be justified.