0
http://www.sqlskills.com/blogs/kimberly/exec-and-sp_executesql-how-are-they-different/
http://www.sqlskills.com/blogs/kimberly/high-performance-procedures/
Posted on
Monday, January 04, 2016
by
醉·醉·鱼
and labeled under
sql
引用: http://www.brentozar.com/archive/2013/12/recompile-hints-and-execution-plan-caching/http://www.sqlskills.com/blogs/kimberly/exec-and-sp_executesql-how-are-they-different/
http://www.sqlskills.com/blogs/kimberly/high-performance-procedures/
Kimberly Tripp 看上去建议使用Dynamic query来避免parameter sniffing的问题。这里来一个简化版了。
第一次执行SP的时候，query plan会被cache起来。第二次执行的时候，会被用到。很显然，第二次会遇到parameter sniffing问题。
ALTER PROCEDURE Get_OrderID_OrderQty
@ProductID INT,
@OrderId INT = NULL
AS
SELECT SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail
WHERE ProductID = @ProductID
AND (SalesOrderID = @OrderId OR @OrderId IS NULL)
GO
SET STATISTICS IO ON
EXEC Get_OrderID_OrderQty @ProductID=870
EXEC Get_OrderID_OrderQty @ProductID=870, @OrderId= 51108
SET STATISTICS IO OFF
改成Dynamic query的方式，就好了。
这样就好了？那我们就too young too simple了。有一个明显的问题，就是tipping point。如果这个时候执行下面的query，你就会得到的一个糟糕的结果。实际上，IO由1246变成了14379，这是非常非常的糟糕的。最好参数里面能够有high selective的，否则还是得老老实实加上OPTION(RECOMPILE)。
ALTER PROCEDURE Get_OrderID_OrderQty
@ProductID INT,
@OrderId INT = NULL
AS
DECLARE @sql NVARCHAR(MAX) = N'';
SET @sql = 'SELECT SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail
WHERE ProductID = @ProductID '
IF @OrderId IS NOT NULL
SET @sql = @sql + 'AND SalesOrderID = @OrderId'
EXEC sys.sp_executesql @sql
, N'@ProductID INT, @OrderId INT'
, @ProductID, @OrderId
GO
SET STATISTICS IO ON
EXEC Get_OrderID_OrderQty @ProductID=897
EXEC Get_OrderID_OrderQty @ProductID=897, @OrderId= 51108
SET STATISTICS IO OFF
这样就好了？那我们就too young too simple了。有一个明显的问题，就是tipping point。如果这个时候执行下面的query，你就会得到的一个糟糕的结果。实际上，IO由1246变成了14379，这是非常非常的糟糕的。最好参数里面能够有high selective的，否则还是得老老实实加上OPTION(RECOMPILE)。
SET STATISTICS IO ON
EXEC Get_OrderID_OrderQty @ProductID=870
SELECT SalesOrderDetailID, OrderQty, SalesOrderID
FROM Sales.SalesOrderDetail
WHERE ProductID = 870
SET STATISTICS IO OFF
Post a Comment