Database Reference
In-Depth Information
deqs.query_hash,
deqs.query_plan_hash
FROM sys.dm_exec_cached_plans AS decp
CROSS APPLY sys.dm_exec_sql_text(decp.plan_handle) AS dest
JOIN sys.dm_exec_query_stats AS deqs
ON decp.plan_handle = deqs.plan_handle
WHERE dest.text LIKE '(@CustomerID INT, @ProductID INT)%' ;
Figure
15-21
shows the results from this query.
Figure 15-21.
Additional output from sys.dm_exec_query_stats
The output from
sys.dm_exec_query_stats
shows that the two versions of the query have different
creation_time
values. More interestingly, they have identical
query_hash
s but different
query_plan_hash
s (more on the hash values
in that section later). All this shows that changing the case resulted in differing execution plans being stored in cache.
In general, use
sp_executesql
to explicitly parameterize queries to make their execution plans reusable when
the queries are resubmitted with different values for the variable parts. This provides the performance benefit of
reusable plans without the overhead of managing any persistent object as required for stored procedures. This
feature is exposed by both ODBC and OLEDB through
SOLExecDirect
and
ICommandWithParameters
, respectively.
Like .NET developers or users of
ADO.NET
(ADO 2.7 or newer), you can submit the preceding
SELECT
statement
using ADO
Command
and
Parameters
. If you set the ADO
Command Prepared
property to
FALSE
and use ADO
Command ('SELECT * FROM "Order Details" d, Orders o WHERE d.OrderID=o.OrderID and d.ProductID=?')
with ADO
Parameters
,
ADO.NET
will send the
SELECT
statement using
sp_executesql
. Most object-to-relational
mapping tools, such as nHibernate or Entity Framework, also have mechanisms to allow for preparing statements
and using parameters.
Along with the parameters,
sp_executesql
sends the entire query string across the network every time the query
is reexecuted. You can avoid this by using the prepare/execute model of ODBC and OLEDB (or OLEDB .NET).
Prepare/Execute Model
ODBC and OLEDB provide a prepare/execute model to submit queries as a prepared workload. Like
sp_executesql
,
this model allows the variable parts of the queries to be parameterized explicitly. The prepare phase allows SQL
Server to generate the execution plan for the query and return a handle of the execution plan to the application. This
execution plan handle is used by the execute phase to execute the query with different parameter values. This model
can be used only to submit queries through ODBC or OLEDB, and it can't be used within SQL Server itself—queries
within stored procedures can't be executed using this model.
The SQL Server ODBC driver provides the
SOLPrepare
and
SOLExecute
APIs to support the prepare/execute
model. The SQL Server OLEDB provider exposes this model through the
ICommandPrepare
interface. The OLEDB
.NET provider of
ADO.NET
behaves similarly.
■
For a detailed description of how to use the prepare/execute model in a database application, please refer to
Note
