你可能不知道的技术细节:存储过程参数传递的影响

2016-12-20 19:24:37来源:cnblogs.com作者:Double_K人点击

前言

  很多人认为数据库其实很简单,也没什么大深入的细节去研究,但是真正的一些细节问题决定着你的是否是专家。  本文主要讲述一下存储过程参数传递的一些小细节,很多人知道参数嗅探,本例也可以理解成参数嗅探的威力加强版++

小例子

 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_columns10 GO 10011  12  13  14 --在 user_type_id列 创建一个索引15 CREATE NONCLUSTERED INDEX [NonClusteredIndex-20160625-164531] ON [dbo].[TB1]16 (17     [user_type_id] ASC18 )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 GO20  21 --开启IO统计22 set statistics io on23  24 --测试查询执行计划25 select * from tb1 where user_type_id = 10
 

 

注:本例中,语句的执行应该走索引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 INT10 SET @ID= @PID11 SELECT *12 FROM TB113 WHERE user_type_id = @ID14 END15 GO16 EXEC dbo.USP_GetData @PID=10
 

 

 结论:如果在存储过程中定义变量,并为变量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 TB111 WHERE user_type_id = @PID12 END13 GO14 EXEC dbo.USP_GetData2 @PID=11

 

  

 

结论:如果在存储过程中使用SET为存储过程参数重新赋值,执行计划仍采用执行时传入的值来生成执行计划。

 

测试三

 1 --测试3 :对参数行进拼接 2  3 create PROCEDURE dbo.USP_GetData3 4 ( 5 @PID INT 6 ) 7 AS 8 BEGIN 9 DECLARE @ID INT10 set @ID = 2 11 SET @PID = @ID + @PID12 SELECT *13 FROM TB114 WHERE user_type_id = @PID15 END16 GO17 EXEC dbo.USP_GetData3 @PID= 8

 

  

 


 结论:如果在存储过程中使用新定义的变量与传入参数拼接重新赋值,执行计划仍采用执行时传入的值来生成执行计划。 

测试四

 1 --测试4 : 对变量进行运算  2 create PROCEDURE dbo.USP_GetData4 3 ( 4   @PID INT 5 ) 6 AS 7 BEGIN 8 SELECT * 9 FROM TB110 WHERE user_type_id = @PID+ 211 END12 GO13 EXEC dbo.USP_GetData4 @PID=8

 

 

 

  结论:虽然传入参数在传入后被修改,但是生成执行计划时仍使用传入时的值

 

测试五

 1 --测试5 :对变量进行复杂运算  2 create PROCEDURE dbo.USP_GetData5 3 ( 4 @PID INT 5 ) 6 AS 7 BEGIN 8 SELECT * 9 FROM TB110 WHERE user_type_id = @PID+ CAST(RAND()*600 AS INT)11 END12 GO13 EXEC dbo.USP_GetData5 @PID=814 GO

 

 

 结论:对参数做复杂运算,无法获得准确的值,因此不能准确地预估行数,也不能生成合理的执行计划

 

测试六

 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 + @PID11 SELECT *12 FROM TB113 WHERE user_type_id = @PID14 END15 GO16 EXEC dbo.USP_GetData6 @PID=817 GO

 

  

 

   结论:针对测试五可以使用参数拼接的方式,以便准确地预估行数,使用正确的执行计划  

 总结

  技术支持做了比较长的时间了,遇到了很多很多坑,在这些坑中不断反思,慢慢成长!不要说什么数据库更优秀,不要说我们海量数据库需要什么什么高端的技术,其实解决问题的关键只是那么一点点的基础知识。

  注:本例中还有另外一种情况就是查询的数据量很大,那么本身走全表扫描是最优计划,而由于参数传递的问题错误的走了index seek + key look up 道理是一样的。

 

--------------博客地址-----------------------------------------------------------------------------

原文地址: http://www.cnblogs.com/double-K/

如有转载请保留原文地址! 

 

 ----------------------------------------------------------------------------------------------------

注:此文章为原创,欢迎转载,请在文章页面明显位置给出此文链接!
若您觉得这篇文章还不错请点击下右下角的推荐,非常感谢!

 

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台