--如果在存储过程中定义变量，并为变量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))方式来解决因变量值导致的执行计划不优的问题