為什麼查詢在儲存過程中比在查詢視窗中執行得慢？

我有一個複雜的查詢，它在查詢視窗中執行 2 秒，但作為儲存過程大約需要 5 分鐘。為什麼作為儲存過程執行需要這麼長時間？

這是我的查詢的樣子。

它需要一組特定的記錄（由@id和標識@createdDate）和特定的時間範圍（從 開始的 1 年@startDate），並返回已發送信件的匯總列表和因這些信件而收到的估計付款。

CREATE PROCEDURE MyStoredProcedure
   @id int,
   @createdDate varchar(20),
   @startDate varchar(20)

AS
SET NOCOUNT ON

   -- Get the number of records * .7
   -- Only want to return records containing letters that were sent on 70% or more of the records
   DECLARE @limit int
   SET @limit = IsNull((SELECT Count(*) FROM RecordsTable WITH (NOLOCK) WHERE ForeignKeyId = @id AND Created = @createdDate), 0) * .07

   SELECT DateSent as [Date] 
       , LetterCode as [Letter Code]
       , Count(*) as [Letters Sent]
       , SUM(CASE WHEN IsNull(P.DatePaid, '1/1/1753') BETWEEN DateSent AND DateAdd(day, 30, DateSent) THEN IsNull(P.TotalPaid, 0) ELSE 0 END) as [Amount Paid]
   INTO #tmpTable
   FROM (

       -- Letters Table. Filter for specific letters
       SELECT DateAdd(day, datediff(day, 0, LR.DateProcessed), 0) as [DateSent] -- Drop time from datetime
           , LR.LetterCode -- Letter Id
           , M.RecordId -- Record Id
       FROM LetterRequest as LR WITH (NOLOCK)
       INNER JOIN RecordsTable as M WITH (NOLOCK) ON LR.RecordId = M.RecordId
       WHERE ForeignKeyId = @id AND Received = @createdDate
           AND LR.Deleted = 0 AND IsNull(LR.ErrorDescription, '') = ''
           AND LR.DateProcessed BETWEEN @startDate AND DateAdd(year, 1, @startDate)
           AND LR.LetterCode IN ('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o')
   ) as T
   LEFT OUTER JOIN (

       -- Payment Table. Payments that bounce are entered as a negative payment and are accounted for
       SELECT PH.RecordId, PH.DatePaid, PH.TotalPaid
       FROM PaymentHistory as PH WITH (NOLOCK)
           INNER JOIN RecordsTable as M WITH (NOLOCK) ON PH.RecordId = M.RecordId
           LEFT OUTER JOIN PaymentHistory as PR WITH (NOLOCK) ON PR.ReverseOfUId = PH.UID
       WHERE PH.SomeString LIKE 'P_' 
           AND PR.UID is NULL 
           AND PH.DatePaid BETWEEN @startDate AND DateAdd(day, 30, DateAdd(year, 1, @startDate))
           AND M.ForeignKeyId = @id AND M.Created = @createdDate
   ) as P ON T.RecordId = P.RecordId

   GROUP BY DateSent, LetterCode
   --HAVING Count(*) > @limit
   ORDER BY DateSent, LetterCode

   SELECT *
   FROM #tmpTable
   WHERE [Letters Sent] > @limit

   DROP TABLE #tmpTable

最終結果如下所示：

日期 字母 程式碼 字母 發送 支付金額
2012 年 1 月 1 日一個 1245 12345.67
2012 年 1 月 1 日 b 2301 1234.56
2012 年 1 月 1 日 c 1312 7894.45
2012 年 1 月 1 日一個 1455 2345.65
2012 年 1 月 1 日 c 3611 3213.21

我無法確定減速在哪裡，因為在查詢編輯器中一切都執行得非常快。只有當我將查詢移動到儲存過程時，它才開始需要很長時間才能執行。

我確定這與生成的查詢執行計劃有關，但我對 SQL 了解不足，無法確定可能導致問題的原因。

應該注意的是，查詢中使用的所有表都有數百萬條記錄。

有人可以向我解釋為什麼作為儲存過程執行所需的時間比在查詢編輯器中執行的時間要長，並幫助我確定查詢的哪一部分在作為儲存過程執行時可能導致性能問題？

正如Martin 在評論中指出的那樣，問題在於查詢正在使用不適合給定參數的記憶體計劃。

他在應用程序中的慢，SSMS 中的快？了解性能之謎提供了很多有用的資訊，這些資訊引導我找到了一些解決方案。

我目前使用的解決方案是將參數複製到過程中的局部變數，我認為這會使 SQL 在執行時重新評估查詢的執行計劃，因此它會為給定的參數選擇最佳執行計劃，而不是使用查詢的不適當的記憶體計劃。

其他可能有效的解決方案是使用OPTIMIZE FORorRECOMPILE查詢提示。

引用自：https://dba.stackexchange.com/questions/15262