Выполнение хранимой процедуры занимает много времени при возврате количества строк

Question

У меня есть хранимая процедура, как это:

ALTER procedure [dbo].[IBS_fetchreleasedinpodiumgridnew]
   @locid INTEGER = NULL
AS BEGIN
   SET NOCOUNT ON

   DECLARE @TodayMinus7Days DATETIME
   SET @TodayMinus7Days = getdate()-7

   SELECT  
       t.TBarcode, t.PlateNo, t.DelEcode,
       cast(t.Paydate as Time) [REQ],
       datediff(MINUTE, t.PayDate,
                CASE t.Status
                   WHEN 3 THEN GETDATE()
                   WHEN 4 THEN t.KeyRoomDate
                   WHEN 5 THEN  t.KeyRoomDate
                END) as KRRT,
       datediff(MINUTE, t.PayDate,
                CASE t.Status
                   WHEN 3 THEN GETDATE()
                   WHEN 4 THEN GETDATE()
                   WHEN 5 THEN t.DelDate
                END) as DT
   FROM    
       dbo.Transaction_tbl t
   WHERE   
      (([status] IN (3,4))
      OR 
      ([status] = 5 AND DATEDIFF(n, DelDate, GETDATE()) <= 3)
      )
      AND locid = 6 
      AND dtime >= @TodayMinus7Days
   ORDER BY  
       paydate 
end

У меня есть некластеризованный индекс на Locid, dtime колонны.

В моей таблице более 4 записей о недостатках. При возврате более 25 строк выполнение занимает много времени (иногда 30 секунд, иногда 45 секунд). Почему это происходит? Как я могу выполнить мою хранимую процедуру быстрее?

Кто-то предложил добавить этот код

OPTION (OPTIMIZE FOR (@TodayMinus7Days UNKNOWN))

в конце этой хранимой процедуры... если я добавлю это, производительность моего выполнения увеличится? Какая польза от этого кода? Каковы все другие оптимизации, которые я должен сделать в этой хранимой процедуре?

Любая помощь приветствуется!

это мой план выполнения запроса

Answer 1

Идея "оптимизировать для" не является необоснованной.
Идея состоит в том, чтобы сказать механизму БД не предполагать, что первое значение даты, используемое с этим процессом, типично при составлении плана выполнения.

Но ваш план выполнения настолько прост, с одной таблицей, я ожидаю, что подсказка не может быть ответом.

План выполнения показывает 2 вещи.

Во-первых, самая медленная вещь - это Сортировка == ваш Заказ по. Сорта всегда медленные и плохо масштабируются. Если по какой-либо причине вам это действительно не нужно, удаление Order By окажет наибольшее влияние. Если вам действительно нужен Order By, единственный способ устранить сортировку - добавить индекс в столбец Order By... и использовать его.
Как и в случае с любым индексным решением, вы должны взвесить все за и против его добавления.

И это приводит к другой операции - поиску ключа.
У вас есть два шага к извлечению данных, поиску по индексу, а затем поиск по ключу для всех столбцов, которые вы хотите. Вы можете исключить поиск ключей, сделав в своем индексе все необходимые столбцы (или создайте такой индекс новым). Опять же, вы должны взвесить все за и против изменения индекса в вашей системе.