T-SQL: Помогите оптимизировать запрос

#sql_server

                    
Добрый день.

Есть запрос, который выполняется большое кол-во времени (даже примерно сказать не
могу, больше 3ех минут). Таймаут на выполнение запроса стоит 1 мин, потом ее выполнение
обрывается. Все индексы построены, в запрос 1 SELECT:

    SELECT
        p.[personId] as [personId],
        --r.[innerId],
        p.inn  as ИНН,
        ps.[firstNameRu] as [Имя рус.],
        ps.[lastNameRu] as [Фамилия рус.],
        ps.[middleNameRu] as [Отчество рус.],
        ps.[firstNameUa] as [Имя укр.] ,
        ps.[lastNameUa] as [Фамилия укр.],
        ps.[middleNameUa] as [Отчество укр.],
        ps.[birthday] as [Дата рождения],
        p.[phone] as Телефон,
        (
            select comment+',  ' from dbo.Reason  
            where personId = p.personId
            for xml path('')
        ) as Причина,
        CASE
            WHEN ps.[lostDate] IS NULL THEN 0
            WHEN ps.[lostDate] IS NOT NULL THEN 1
        END as [Паспорт потерян],
        ps.[series] as Серия,
        ps.[number] as Номер,
        ps.[office] as [Место выдачи],
        ps.[dateReceipt] as [Дата выдачи],
        p.[city] as Город,
        ps.[address] as Прописка
    FROM dbo.Person (NOLOCK) p
    LEFT JOIN dbo.Passport (NOLOCK) ps
      ON ps.personId = p.personId

    WHERE 

    --isnull(inn,0)=isnull(@inn,isnull(inn,0))

    (isnull(inn, 0)               = isnull(@inn,            isnull(inn,0))) AND
    (isnull([firstNameRu], 0)     = isnull(@firstNameRu,    isnull([firstNameRu],0))) AND
    (isnull([lastNameRu], 0)      = isnull(@lastNameRu,     isnull([lastNameRu],0))) AND
    (isnull([middleNameRu], 0)    = isnull(@middleNameRu,   isnull([middleNameRu],0))) AND
    (isnull([firstNameUa], 0)     = isnull(@firstNameUa,    isnull([firstNameUa],0))) AND
    (isnull([lastNameUa], 0)      = isnull(@lastNameUa,     isnull([lastNameUa],0))) AND
    (isnull([middleNameUa], 0)    = isnull(@middleNameUa,   isnull([middleNameUa],0))) AND
    ((@birthday is null) or (ps.[birthday] = @birthday)) AND
    (isnull([phone], 0)           = isnull(@contactPhone,   isnull(phone,0))) AND
    ----(@reason is null) or (
    ----    CONTAINS((
    ----        select comment+',  ' from dbo.Reason  
    ----        where personId = p.personId
    ----        for xml path('')
    ----    ), @reason)
    ----)) AND
    --(@PassportLost is null) or 
    --(
    --(CASE
    --  WHEN ps.[lostDate] IS NULL THEN 0
    --  WHEN ps.[lostDate] IS NOT NULL THEN 1
    --END) = @PassportLost
    --) AND
    (isnull([series], 0)        = isnull(@passportSeries, isnull([series],0))) AND
    (isnull([number], 0)        = isnull(@passportNumber, isnull([number],0))) AND
    (isnull([office], 0)        = isnull(@ktoVidal,       isnull([office],0))) AND
    ((@PassportReceivingDate is null) or ( [dateReceipt] = @PassportReceivingDate)) AND
    (isnull([city], 0)          = isnull(@City,           isnull([city],0))) AND
    (isnull([address], 0)       = isnull(@PassportAdress, isnull([address],0)))


Смысл запроса: у нас есть 2 связанные таблицы и большое число параметров. по которым
может производиться отбор (всего около 15 параметров, причем могут быть заданы не все,
а например только 1 фильтр, или 2 и 3ий)

Подскажите, пожалуйста, как можно оптимизировать запрос?

Спасибо

План выполнения запроса показал, что 60% идет поиск в таблице Person:

    

Ответы

Ответ 1

Ок, по куску плана видна пара проблем:

Index scan на плане - это перебор всех строк в таблице Persons в поисках подходящих
под фильтр. Фильтр у вас написан так, что он читает и проверяет все упоминаемые в нем
колонки, даже если значение не было передано. Судя по толщине стрелок, данных в Persons
достаточно много. Т.е. ваш запрос всегда перелопачивает всю базу. 

Эффективного способа способа написать "если параметр спущен, то сравнить с ним",
насколько я знаю, нет. Это одна из проблем выборки данных через хранимки. Вам придется
строить SQL динамически, или на стороне приложения, или на стороне SQL Server, и выполнять
его через sp_executesql (лучше первый вариант).

Вторая проблема - для каждой из выбранных строк SQL Server достает данные из Passport.
Индекс, по которому он это делает - не покрывающий. т.е. для каждого из Person SQL Server


лезет в индекс IX_Passport_PersonId_IsЧтоТоТам, как в наиболее подходящий (скорее
всего в этом индексе первая колонка - Passport.PersonID), достает из этого индека PassportID.
с этим PassportID лезет в PK_Passport, достает оттуда данные.


этого можно избежать, создав покрывающий индекс - 

CREATE NONCLUSTERED INDEX [IX_Passport_For_Person] ON [dbo].[Passport]
(
    [personID] ASC
)
INCLUDE 
(
    [firstNameRu],
    -- и остальные колонки, которые показываются в плане в ноде Key Lookup
) 


но это лучше сделать после динамического построения условий - может быть у вас key
lookup вообще исчезнет.


Ответ 2

Я переписал запрос, и сделал его динамическим. Решило все проблемы. Всем спасибо.