同樣的SQL直接跑(4秒)跟包成proceudure跑(3分)時間差距很大

同樣的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.