你可能不知道的技术细节:存储过程参数传递的影响
前言
小例子
1 ---创建测试表 2 SELECT IDENTITY(INT,1,1) AS RID, 3 * INTO TB1 4 FROM sys.all_columns 5 GO 6 ---模拟大量数据 7 INSERT INTO TB1 8 SELECT * 9 FROM sys.all_columns 10 GO 100 11 12 13 14 --在 user_type_id列 创建一个索引 15 CREATE NONCLUSTERED INDEX [NonClusteredIndex-20160625-164531] ON [dbo].[TB1] 16 ( 17 [user_type_id] ASC 18 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 19 GO 20 21 --开启IO统计 22 set statistics io on 23 24 --测试查询执行计划 25 select * from tb1 where user_type_id = 10
![](https://images2015.cnblogs.com/blog/832221/201612/832221-20161220154415464-1867620907.png)
注:本例中,语句的执行应该走索引seek + key look up
测试一
1 --测试1:使用定义变量,把参数值传递给变量 2 3 create PROCEDURE dbo.USP_GetData 4 ( 5 @PID INT 6 ) 7 AS 8 BEGIN 9 DECLARE @ID INT 10 SET @ID= @PID 11 SELECT * 12 FROM TB1 13 WHERE user_type_id = @ID 14 END 15 GO 16 EXEC dbo.USP_GetData @PID=10
![](https://images2015.cnblogs.com/blog/832221/201612/832221-20161220154628932-1677068105.png)
![](https://images2015.cnblogs.com/blog/832221/201612/832221-20161220154638042-1293219373.png)
结论:如果在存储过程中定义变量,并为变量SET赋值,该变量的值无法为执行计划提供参考(即执行计划不考虑该变量),将会出现预估行数和实际行数相差过大导致执行计划不优的情况
测试二
1 ---测试2 : 对参数进行运算 2 create PROCEDURE dbo.USP_GetData2 3 ( 4 @PID INT 5 ) 6 AS 7 BEGIN 8 SET @PID=@PID-1 9 SELECT* 10 FROM TB1 11 WHERE user_type_id = @PID 12 END 13 GO 14 EXEC dbo.USP_GetData2 @PID=11
![](https://images2015.cnblogs.com/blog/832221/201612/832221-20161220154727245-1386620437.png)
结论:如果在存储过程中使用SET为存储过程参数重新赋值,执行计划仍采用执行时传入的值来生成执行计划。
测试三
1 --测试3 :对参数行进拼接 2 3 create PROCEDURE dbo.USP_GetData3 4 ( 5 @PID INT 6 ) 7 AS 8 BEGIN 9 DECLARE @ID INT 10 set @ID = 2 11 SET @PID = @ID + @PID 12 SELECT * 13 FROM TB1 14 WHERE user_type_id = @PID 15 END 16 GO 17 EXEC dbo.USP_GetData3 @PID= 8
![](https://images2015.cnblogs.com/blog/832221/201612/832221-20161220155144495-213335163.png)
![](https://images2015.cnblogs.com/blog/832221/201612/832221-20161220155152964-408593178.png)
结论:如果在存储过程中使用新定义的变量与传入参数拼接重新赋值,执行计划仍采用执行时传入的值来生成执行计划。
测试四
1 --测试4 : 对变量进行运算 2 create PROCEDURE dbo.USP_GetData4 3 ( 4 @PID INT 5 ) 6 AS 7 BEGIN 8 SELECT * 9 FROM TB1 10 WHERE user_type_id = @PID+ 2 11 END 12 GO 13 EXEC dbo.USP_GetData4 @PID=8
![](https://images2015.cnblogs.com/blog/832221/201612/832221-20161220160159932-1984009901.png)
![](https://images2015.cnblogs.com/blog/832221/201612/832221-20161220160211011-26476715.png)
结论:虽然传入参数在传入后被修改,但是生成执行计划时仍使用传入时的值
测试五
1 --测试5 :对变量进行复杂运算 2 create PROCEDURE dbo.USP_GetData5 3 ( 4 @PID INT 5 ) 6 AS 7 BEGIN 8 SELECT * 9 FROM TB1 10 WHERE user_type_id = @PID+ CAST(RAND()*600 AS INT) 11 END 12 GO 13 EXEC dbo.USP_GetData5 @PID=8 14 GO
![](https://images2015.cnblogs.com/blog/832221/201612/832221-20161220160324901-1117874543.png)
结论:对参数做复杂运算,无法获得准确的值,因此不能准确地预估行数,也不能生成合理的执行计划
测试六
1 --测试6 : 复杂运算使用变量拼接 2 create PROCEDURE dbo.USP_GetData6 3 ( 4 @PID INT 5 ) 6 AS 7 BEGIN 8 DECLARE @ID INT 9 set @ID = CAST(RAND()*600 AS INT) 10 SET @PID = @ID + @PID 11 SELECT * 12 FROM TB1 13 WHERE user_type_id = @PID 14 END 15 GO 16 EXEC dbo.USP_GetData6 @PID=8 17 GO
![](https://images2015.cnblogs.com/blog/832221/201612/832221-20161220160510182-1730450279.png)
![](https://images2015.cnblogs.com/blog/832221/201612/832221-20161220160516136-999898206.png)
总结
技术支持做了比较长的时间了,遇到了很多很多坑,在这些坑中不断反思,慢慢成长!不要说什么数据库更优秀,不要说我们海量数据库需要什么什么高端的技术,其实解决问题的关键只是那么一点点的基础知识。
注:本例中还有另外一种情况就是查询的数据量很大,那么本身走全表扫描是最优计划,而由于参数传递的问题错误的走了index seek + key look up 道理是一样的。
--------------博客地址-----------------------------------------------------------------------------
原文地址: http://www.cnblogs.com/double-K/
如有转载请保留原文地址!
----------------------------------------------------------------------------------------------------
注:此文章为原创,欢迎转载,请在文章页面明显位置给出此文链接!
若您觉得这篇文章还不错请点击下右下角的推荐,非常感谢!