同樣的SQL直接跑(4秒)跟包成proceudure跑(3分)時間差距很大
後來查到奇怪的解決方式
把sp_commit_case的傳入的參數給local變數,然後Procedure裡原本用到傳入參數的地方都改為local變數
這裡"Parameter
Sniffing"有解釋原因
如果Procudure內直接使用傳入參數當where條件的話，則SQL只會存在第一次執行時所創建的執行計劃
意思就是說只有第一次才有優化,第二次後就….
或許create procedure使用with recompile及清空procedure cache都可達成相同目的?
以上分享給大家知道
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| I had the same problem as the original poster but the
quoted answer did not solve the problem for me. The query still ran really
slow from a stored procedure. I found another answer here "Parameter Sniffing", Thanks Omnibuzz. Boils down to using "local Variables" in your stored procedure queries, but read the original for more understanding, it's a great write up. e.g. Slow way: CREATE PROCEDURE GetOrderForCustomers(@CustID varchar(20)) AS BEGIN SELECT * FROM orders WHERE customerid = @CustID END
Fast way:CREATE PROCEDURE GetOrderForCustomersWithoutPS(@CustID varchar(20)) AS BEGIN DECLARE @LocCustID varchar(20) SET @LocCustID = @CustID
SELECT * FROM orders WHERE customerid = @LocCustID END
Hope this helps somebody else, doing this reduced my
execution time from 5+ minutes to about 6-7 seconds. |
