SQLServer 参数化查询经验分享(2)-木庄网络博客

　　让我们看看这个代码：

　DBCC FREEPROCCACHE
　　GO
　　SELECT SUM(LineTotal) AS LineTotal
　　FROM AdventureWorks.Sales.SalesOrderHeader H
　　JOIN AdventureWorks.Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderID
　　WHERE H.SalesOrderID = 56000
　　GO
　　SELECT SUM(LineTotal) AS LineTotal
　　FROM AdventureWorks.Sales.SalesOrderHeader H
　　JOIN AdventureWorks.Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderID
　　WHERE H.SalesOrderID = 56001
　　GO
　　SELECT stats.execution_count AS cnt,
　　p.size_in_bytes AS [size],
　　LEFT([sql].[text], 200) AS [plan_text]
　　FROM sys.dm_exec_cached_plans p
　　OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql
　　JOIN sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handle;
　　GO

在这里，我释放了过程缓存，然后运行这两个包含一个JOIN的、不同的非简单的T-SQL语句。然后我将检查缓存计划。这是这个使用DMV 的SELECT语句的输出(注意，输出被重新格式化了，以便它更易读)：

cnt size plan_text
　　--- ----------- -------------------------------------------------------------------------------
　　1 49152 SELECT SUM(LineTotal) AS LineTotal
　　FROM AdventureWorks.Sales.SalesOrderHeader H
　　JOIN AdventureWorks.Sales.SalesOrderDetail D
　　ON D.SalesOrderID = H.SalesOrderID
　　WHERE H.SalesOrderID = 56001
　　1 49152 SELECT SUM(LineTotal) AS LineTotal
　　FROM AdventureWorks.Sales.SalesOrderHeader H
　　JOIN AdventureWorks.Sales.SalesOrderDetail D
　　ON D.SalesOrderID = H.SalesOrderID
　　WHERE H.SalesOrderID = 56000

正如你从这个输出看到的，这两个SELECT语句没有被查询优化器参数化。优化器创建了两个不同的缓存执行计划，每一个都只被执行了一次。我们可以通过使用sp_executesql系统存储过程来帮助优化器为这两个不同的SELECT语句创建一个参数化执行计划。
下面是上面的代码被重新编写来使用sp_executesql 系统存储过程：

　DBCC FREEPROCCACHE;
　　GO
　　EXEC sp_executesql N'SELECT SUM(LineTotal) AS LineTotal
　　FROM AdventureWorks.Sales.SalesOrderHeader H
　　JOIN AdventureWorks.Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderID
　　WHERE H.SalesOrderID = @SalesOrderID', N'@SalesOrderID INT', @SalesOrderID = 56000;
　　GO
　　EXEC sp_executesql N'SELECT SUM(LineTotal) AS LineTotal
　　FROM AdventureWorks.Sales.SalesOrderHeader H
　　JOIN AdventureWorks.Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderID
　　WHERE H.SalesOrderID = @SalesOrderID', N'@SalesOrderID INT', @SalesOrderID = 56001;
　　GO
　　SELECT stats.execution_count AS exec_count,
　　p.size_in_bytes AS [size],
　　[sql].[text] AS [plan_text]
　　FROM sys.dm_exec_cached_plans p
　　OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql
　　JOIN sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handle;
　　GO

如同你所看到的，我重新编写了这两个SELECT语句，使它们通过使用“EXEC sp_executesql”语句来执行。对这些EXEC语句中的每一个，我都传递三个不同的参数。第一个参数是基本的SELECT语句，但是我将SalesOrderID的值用一个变量(@SalesOrderID)替代。在第二个参数中，我确定了@SalesOrderID的数据类型，在这个例子中它是一个integer。然后在最后一个参数中，我传递了SalesOrderID的值。这个参数将控制我的SELECT根据SalesOrderID值所生成的结果。sp_executesql的每次执行中前两个参数都是一样的。但是第三个参数不同，因为每个都有不同的SalesOrderID值。

　　现在当我运行上面的代码时，我从DMV SELECT语句得到下面的输出(注意，输出被重新格式化了，以便它更易读)：

cnt size plan_text
　　--- ----------- -----------------------------------------------------------------------------------------
　　2 49152 (@SalesOrderID INT)SELECT SUM(LineTotal) AS LineTotal
　　FROM AdventureWorks.Sales.SalesOrderHeader H
　　JOIN AdventureWorks.Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderID
　　WHERE H.SalesOrderID = @SalesOrderID

从这个输出，你可以看出，我有一个参数化缓存计划，它被执行了两次，为每个EXEC语句各执行了一次。

　　使用参数化查询来节省资源和优化性能

　　在语句可以被执行之前，每个T-SQL语句都需要被评估，而且需要建立一个执行计划。创建执行计划会占用宝贵的CPU资源。当执行计划被创建后，它使用内存空间将它存储在过程缓存中。降低CPU和内存使用的一个方法是利用参数化查询。尽管数据库可以被设置为对所有查询FORCE参数化，但是这不总是最好的选择。通过了解你的哪些T-SQL语句可以被参数化然后使用sp_executesql存储过程，你可以帮助SQL Server节省资源并优化你的查询的性能。

更多SQL内容来自木庄网络博客

标签：SQL

返回前面的内容