执行计划--在存储过程中使用SET对执行计划的影响

--如果在存储过程中定义变量，并为变量SET赋值，该变量的值无法为执行计划提供参考（即执行计划不考虑该变量）,将会出现预估行数和实际行数相差过大导致执行计划不优的情况
--如果在存储过程中使用SET为存储过程参数重新赋值，执行计划仍采用执行时传入的值来生成执行计划。
--=======================================
--准备测试数据
DROP TABLE TB1
GO
SELECT IDENTITY(INT,1,1) AS RID,
*INTO TB1
FROM sys.all_columns
GO
INSERT INTO TB1
SELECT *
FROM sys.all_columns
GO 100
ALTER TABLE TB1
ADD PRIMARY KEY(RID)
 
 
--测试查询参数使用变量
--例如下列存储过程，由于在生成执行计划时不知道@ID的具体值，因此无法预估满足PID>@ID条件的
CREATE PROCEDURE dbo.USP_GetData
(
  @PIDINT
)
AS
BEGIN
DECLARE @ID INT
SET @ID= @PID
SELECT *
FROM TB1
WHERE RID>@ID
END
GO
EXEC dbo.USP_GetData @PID=606808
--由于预估行数有问题,导致生成不使用索引的查询计划
 

--================================================= 

--测试修改传入参数的情况
--虽然传入参数在传入后被修改,但是生成执行计划时仍使用传入时的值
CREATE PROCEDURE dbo.USP_GetData2
(
  @PID INT
)
AS
BEGIN
SET @PID=@PID-606800
SELECT*
FROM TB1
WHERE RID>@PID
END
GO
EXEC dbo.USP_GetData2 @PID=606808

 

--================================================= 
--测试在查询时对传入参数做运算
CREATE PROCEDURE dbo.USP_GetData3
(
  @PID INT
)
AS
BEGIN
SELECT COUNT(1)
FROM TB1
WHERE RID>@PID+600080
END
GO
EXEC dbo.USP_GetData3 @PID=20
 

 --================================================= 
--测试在查询时对传入参数做运算(复杂运算)
----对应复杂运算,无法获得准确的值,因此不能准确地预估行数,也不能生成合理的执行计划
CREATE PROCEDURE dbo.USP_GetData4
(
  @PID INT
)
AS
BEGIN
SELECT COUNT(1)
FROM TB1
WHERE RID>@PID+CAST(RAND()*6000800 AS INT)
END
GO
EXEC dbo.USP_GetData4 @PID=20
GO

 

 

总结：
在存储过程中使用到的变量可以分为内部变量和外部变量
1>对于外部变量，存储过程编译时会使用该变量的真实值依据统计来生成执行计划，无论该外部变量是否在存储过程中发生修改
2>对于内部变量，存储过程编译时无法获取该变量的真实值，因此无法使用统计，从而只能生成"最通用"的执行计划(可能是比较差的执行计划)

补充：
可以使用OPTION(optimize for(@PID=75124))方式来解决因变量值导致的执行计划不优的问题