前言 很多人認為資料庫其實很簡單，也沒什麼大深入的細節去研究，但是真正的一些細節問題決定著你的是否是專家。 本文主要講述一下存儲過程參數傳遞的一些小細節，很多人知道參數嗅探，本例也可以理解成參數嗅探的威力加強版++ 小例子 1 創建測試表 2 SELECT IDENTITY(INT,1,1) AS ...
前言
很多人認為資料庫其實很簡單，也沒什麼大深入的細節去研究，但是真正的一些細節問題決定著你的是否是專家。 本文主要講述一下存儲過程參數傳遞的一些小細節，很多人知道參數嗅探，本例也可以理解成參數嗅探的威力加強版++小例子
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
註：本例中，語句的執行應該走索引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
結論：如果在存儲過程中定義變數，併為變數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
結論：如果在存儲過程中使用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
結論：如果在存儲過程中使用新定義的變數與傳入參數拼接重新賦值，執行計劃仍採用執行時傳入的值來生成執行計劃。
測試四
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
結論：雖然傳入參數在傳入後被修改,但是生成執行計劃時仍使用傳入時的值
測試五
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
結論：對參數做複雜運算,無法獲得準確的值,因此不能準確地預估行數,也不能生成合理的執行計劃
測試六
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
結論：針對測試五可以使用參數拼接的方式，以便準確地預估行數，使用正確的執行計劃
總結
技術支持做了比較長的時間了，遇到了很多很多坑，在這些坑中不斷反思，慢慢成長！不要說什麼資料庫更優秀，不要說我們海量資料庫需要什麼什麼高端的技術，其實解決問題的關鍵只是那麼一點點的基礎知識。
註：本例中還有另外一種情況就是查詢的數據量很大，那麼本身走全表掃描是最優計劃，而由於參數傳遞的問題錯誤的走了index seek + key look up 道理是一樣的。
--------------博客地址-----------------------------------------------------------------------------
原文地址: http://www.cnblogs.com/double-K/
如有轉載請保留原文地址!
----------------------------------------------------------------------------------------------------
註：此文章為原創，歡迎轉載，請在文章頁面明顯位置給出此文鏈接！
若您覺得這篇文章還不錯請點擊下右下角的推薦，非常感謝！
