MS SQL 2005, Parameter Sniffing, тормозящий Stored Procedure

    Недавно столкнулся с проблемой: скрипт запущенный в Query Analyzer отрабатывал за секунду, а он же в виде хранимой процедуры аж 50 секунд. Оказалось всему виной Parameter Sniffing который призван… оптимизировать запрос. :)

    SQL сервер при компиляции хранимки создает Execution Plan, где предполагает, что входные параметры процедуры будут определенного вида. Это и называется Parameter Sniffing.
    Когда же параметры оказываются несколько иными, то Execution Plan может оказаться несколько неподходящим для оптимального выполнения запроса.

    Наиболее простой метод решения проблемы — отключить Parameter Sniffing. Это делается следующим образом: создаются локальные переменные в теле процедуры и им присваиваются значения входных параметров.

    ALTER PROCEDURE [dbo].[REPORT_USERS_BRANCHES]

    @branchId INT,
    @branchName NVARCHAR(100) OUTPUT,
    @filterByPaymentActivity VARCHAR(50),
    @dateFrom DATETIME,  
    @dateTo DATETIME

    AS

    DECLARE @dtFrom DATETIME,   @dtTo DATETIME, @filter VARCHAR(50), @bId INT
    SET @bId = @branchId
    SET @dtFrom = @DateFrom
    SET @dtTo = @DateTo
    SET @filter = @filterByPaymentActivity
    ...


    * This source code was highlighted with Source Code Highlighter.


    Так же можно использовать опции оптимизации(OPTION(OPTIMIZE FOR ...)), указывать при создании процедуры, что нужно каждый раз рекомпилить процедуру (WITH RECOMPILE).
    Но эти варианты мне не помогли.
    Есть еще метод с деревом решений (Decision Tree SP), но это уже совсем монструозно.

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

    Комментарии 1

      0
      Можно гвоздями прибить план исполнения. А ещё в часто и много используемых процедурах, скорость выполнения которых критична — гвоздями прибиваю индексы и хинты. За два года использования такого приема (последний масштабный проект) — нареканий не было (ни разу не переделывал процедуры по причине изменения скорости работы).

      Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

      Самое читаемое