Use SQL Parameters to Overcome Ad Hoc Performance Issues - The Code Project - C# Database

Use SQL Parameters to Overcome Ad Hoc Performance Issues - The Code Project - C# Database


SqlCommand.Prepare()
A member asked weither it would be more efficient to use the Prepare() method of the SqlCommand object. In the context of this article the answer is, no. The Prepare() method calls sp_prepexec, an undocumented stored procedure. Essentially, it creates a temporary stored procedure for the current session which is destroyed when the current connection is closed. This allows the query to be called as if it were a stored procedure until the connection is closed. The benefit of using Prepare() is that it reduces network traffic because the entire command text as defined in the client application is sent across the network only once. The first time the command is executed after calling the Prepare() method, it looks something like this:
declare @p1 int
set @p1=10001
exec sp_prepexec @p1 output,N'SELECT C.Title, C.FirstName, C.LastName,
C.EmailAddress, C.Phone,E.*
FROM Person.Contact C
INNER JOIN HumanResources.Employee E ON E.ContactID = C.ContactID
WHERE C.LastName = @LastName
',@EmployeeID=15073
select @p1

Notice how it declares @p1 and returns it as a scalar value along with the results of the command. The SqlCommand object then can send the following command on subsequent requests:exec sp_execute 10001,@EmployeeID=12991
The reason it is not more efficient to use the Prepare() method in this scenario is because, unless the same connection object is used and remains open, the request sent to Sql Server will always be the call to sp_prepexec. So this adds the overhead of creating a temporary stored procedure along with a handle to that stored procedure, but you don't get the benefit of the reduced traffic. It is unlikely that this overhead will create a large impact on SQL Server however, but I recommend omitting the call to Prepare() unless it's needed.