Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Execution plan. Принудительное сканирование по индексу  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 487
Имеется хранимая процедура:

CREATE PROCEDURE [dbo].[getItems]
...
AS
BEGIN
     
    SELECT ...

    FROM 
            tb1 AS M 
    INNER JOIN 
            tb2 ON tb2.ID = M.tb2ID
    LEFT JOIN 
            tb3 ON tb.3ID = M.tb3ID
    LEFT JOIN ...
    where 
                @p1 is NULL or  M.Field1= @p1
            ) and (
                @p2 is NULL or  M.Field2= @p2
            ) and (
                @p3 is NULL or  M.Field3= @p3
	    ...

Таблица tb1 проиндексирована по Field1, Field2, Field3.
Если убрать из условий выборки условия типа (@p1 is NULL), то план показывает, что идет INDEX SEEK по индексам
IX_Field1, IX_Field2, IX_Field3.
В противном случае возможны варианты, и все они заметно медленнее.
Как оптимизировать процедуру: вынести проверки параметров @P вверх в if else и для каждого значения строить отдельный запрос?
Как принудительно заставить оптимизатор использовать индексы, и как запретить ему перестраивать план?
1 июн 12, 15:51    [12651950]     Ответить | Цитировать Сообщить модератору
 Re: Execution plan. Принудительное сканирование по индексу  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
Шамиль Фаридович,

...
WHERE M.Field1 = ISNULL(@p1, M.Field1) AND ...

?
1 июн 12, 16:01    [12652018]     Ответить | Цитировать Сообщить модератору
 Re: Execution plan. Принудительное сканирование по индексу  [new]
denis2710
Member

Откуда: Москва
Сообщений: 3384
Шамиль Фаридович,
А как работать будет с with recompile?
Шамиль Фаридович
Как оптимизировать процедуру: вынести проверки параметров @P вверх в if else и для каждого значения строить отдельный запрос?
один из вариантов
так же можно попробовать переделать на union
можно подумать над динамическим sql,строить запрос в зависимости от параметров

Шамиль Фаридович
Как принудительно заставить оптимизатор использовать индексы, и как запретить ему перестраивать план?

Почему Вы считаете,что при @p is NULL,т.е выбрать все строки таблицы не фильтруя их по Field использовать индекс по Field лучшая стратегия?
1 июн 12, 16:17    [12652121]     Ответить | Цитировать Сообщить модератору
 Re: Execution plan. Принудительное сканирование по индексу  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
Shakill, хотя нет, ерунду написал

можно разнести условия or и построить здоровенный запрос с помощью union all
1 июн 12, 16:22    [12652164]     Ответить | Цитировать Сообщить модератору
 Re: Execution plan. Принудительное сканирование по индексу  [new]
RubinDm
Member

Откуда:
Сообщений: 461
Шамиль Фаридович, попробуйте так:
    select ... -- без изменений
    -- далее изменения
    from tb1 as M
    join -- фактически это фильтр над M по параметру @p1
    ( -- возможно, что сервер, глядя на @p1 сообразит, что union all можно тупо заменить на один из вариантов
      -- но даже если этого не случится, это все равно будет дешево, т.к. данные будут только в одном из двух
      -- вариантов выражения union all (@p1 is or isn't null)
      select M.ID from tb1 as M where @p1 is null -- по идее, это scan по PK
        -- можно попробовать помочь фильтрованным индексом: create unique index UQ_ID on tb1 ( ID ) where Field1 is null
      union all
      select M.ID from tb1 as M where M.Field1 = @p1 -- по идее, это seek по IX_Field1
    ) M1 on M1.ID = M.ID
    join -- фактически это фильтр над M по параметру @p2
    ( -- возможно, что сервер, глядя на @p2 сообразит, что union all можно тупо заменить на один из вариантов
      -- но даже если этого не случится, это все равно будет дешево, т.к. данные будут только в одном из двух
      -- вариантов выражения union all (@p2 is or isn't null)
      select M.ID from tb1 as M where @p2 is null -- по идее, это scan по PK
        -- можно попробовать помочь фильтрованным индексом: create unique index UQ_ID on tb1 ( ID ) where Field2 is null
      union all
      select M.ID from tb1 as M where M.Field2 = @p2 -- по идее, это seek по IX_Field2
    ) M2 on M2.ID = M.ID
    join -- фактически это фильтр над M по параметру @p3
    ( -- возможно, что сервер, глядя на @p3 сообразит, что union all можно тупо заменить на один из вариантов
      -- но даже если этого не случится, это все равно будет дешево, т.к. данные будут только в одном из двух
      -- вариантов выражения union all (@p3 is or isn't null)
      select M.ID from tb1 as M where @p3 is null -- по идее, это scan по PK
        -- можно попробовать помочь фильтрованным индексом: create unique index UQ_ID on tb1 ( ID ) where Field3 is null
      union all
      select M.ID from tb1 as M where M.Field3 = @p3 -- по идее, это seek по IX_Field3
    ) M3 on M3.ID = M.ID

    -- далее без изменений
    INNER JOIN 
            tb2 ON tb2.ID = M.tb2ID
    LEFT JOIN 
            tb3 ON tb.3ID = M.tb3ID
    LEFT JOIN ...
	    ...


если не затруднит - сообщите, пожалуйста, о результатах..
1 июн 12, 22:26    [12653990]     Ответить | Цитировать Сообщить модератору
 Re: Execution plan. Принудительное сканирование по индексу  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Шамиль Фаридович
Имеется хранимая процедура:

CREATE PROCEDURE [dbo].[getItems]
...
AS
BEGIN
     
    SELECT ...

    FROM 
            tb1 AS M 
    INNER JOIN 
            tb2 ON tb2.ID = M.tb2ID
    LEFT JOIN 
            tb3 ON tb.3ID = M.tb3ID
    LEFT JOIN ...
    where 
                @p1 is NULL or  M.Field1= @p1
            ) and (
                @p2 is NULL or  M.Field2= @p2
            ) and (
                @p3 is NULL or  M.Field3= @p3
	    ...


Изучайте и выбирайте:

Dynamic Search Conditions in T-SQL for 2005
Dynamic Search Conditions in T-SQL for 2008
2 июн 12, 00:07    [12654299]     Ответить | Цитировать Сообщить модератору
 Re: Execution plan. Принудительное сканирование по индексу  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 487
RubinDm, ваш вариант пока не пробовал.
Mind, в этой статье описана как раз моя задача - фильтрация по различным столбцам
в зависимости от выбора пользователя.
Microsoft SQL Server 2008 R2 (SP1) - 10.50.1600.0 (X64) 
	Jun 17 2011 00:54:03 
	Copyright (c) Microsoft Corporation
	Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Для статического запроса там все сводится
OPTION (RECOMPILE)
в конце процедуры.
Проблема в том, что поиск для запроса с условием
@p1 is NULL or  M.Field1= @p1

идет scan по индексу IX_Field1, а я хотел бы seek - но хинт forceseek по индексу я использовать не могу, так как он будут только мешать в случае @p1=Null.
Ситуация кардинальным образом меняется, если установить SP1 и довести сервер до версии 10.50.2500.0 (X64) - идет seek.
Кстати, насколько я понимаю, index seek - это поиск по сбалансированному дереву индекса - оптимальный поиск в том случае, когда выборка идет только по столбцам индекса с высокой селективностью (еще лучше, если он включает в себя все столбцы результирующего набора) , а что такое index scan?
И еще, в случае когда @p1=Null включается параллелизм. Как его принудительно отключить для запроса, хотя бы для тестирования производительности?
4 июн 12, 12:32    [12660947]     Ответить | Цитировать Сообщить модератору
 Re: Execution plan. Принудительное сканирование по индексу  [new]
denis2710
Member

Откуда: Москва
Сообщений: 3384
Шамиль Фаридович,
автор
включается параллелизм. Как его принудительно отключить для запроса

maxdop
автор
а что такое index scan?

просмотр всех страниц индекса.
4 июн 12, 12:48    [12661080]     Ответить | Цитировать Сообщить модератору
 Re: Execution plan. Принудительное сканирование по индексу  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Шамиль Фаридович
Ситуация кардинальным образом меняется, если установить SP1 и довести сервер до версии 10.50.2500.0 (X64) - идет seek.
А на предыдущих версиях оно по сути и не работает как задумано.
Шамиль Фаридович
Как его принудительно отключить для запроса, хотя бы для тестирования производительности?
MAXDOP (1)

И еще, для того чтобы эффективно использовать такой подход - статистика всегда должна быть актуальна, это очень важно.
4 июн 12, 21:57    [12664487]     Ответить | Цитировать Сообщить модератору
 Re: Execution plan. Принудительное сканирование по индексу  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 487
При некоторых параметрах запроса мне предлагается создать покрывающий индекс, дополнительно включив поле кластерного индекса и другие поля. С другими полями все понятно, но зачем в некластерный индекс включать поле кластерного, когда оно и так там содержится(row locator)?
5 июн 12, 12:23    [12667114]     Ответить | Цитировать Сообщить модератору
 Re: Execution plan. Принудительное сканирование по индексу  [new]
SamMan
Member

Откуда: Moscow
Сообщений: 759
Шамиль Фаридович
мне предлагается


Предлагается кем/чем?

Шамиль Фаридович
но зачем в некластерный индекс включать поле кластерного


А откуда сама эта идея берется?
5 июн 12, 13:40    [12667840]     Ответить | Цитировать Сообщить модератору
 Re: Execution plan. Принудительное сканирование по индексу  [new]
Шамиль Фаридович
Member

Откуда: Саратов
Сообщений: 487
Видимо Оптимизатором запросов - подобные сообщения я вижу в Действительном плане выполнения.
5 июн 12, 14:07    [12668132]     Ответить | Цитировать Сообщить модератору
 Re: Execution plan. Принудительное сканирование по индексу  [new]
SamMan
Member

Откуда: Moscow
Сообщений: 759
Шамиль Фаридович
Видимо Оптимизатором запросов - подобные сообщения я вижу в Действительном плане выполнения.


А... Так это он просто формальный синтаксис вам пишет. Вы думаете, что если при создании некластерного индекса вы укажете в наборе колонок PK-колонку, так движок включит ее дважды? Сначала по собственной инициативе, потом по нашей? Не беспокойтесь, движок не глупее нас с вами...
5 июн 12, 14:24    [12668358]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить