Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 эффективность отфильтрованного индекса  [new]
Shlippenbaranus
Member

Откуда:
Сообщений: 241
Добрый день, подскажите, пожалуйста, что происходит.

MS SQL 2008 R2, имеем таблицу:
create table A(
  a varchar(80),
  b varchar(80),
  c varchar(200)
)

В таблице 15 миллионов записей, из них 400 тысяч удовлетворяют условию
b='smth' and c in ('общий префикс - суффикс 1','общий префикс - суффикс 2')

Задача - ускорить выборку в запросах именно этих 400 тыс записей.

Создаем фильтрованный индекс:
create index ind_A on A (a) where (b = 'smth' and c in ('общий префикс - суффикс 1','общий префикс - суффикс 2'))


При попытке взять count(1) индекс работает, время выполнения запроса ожидаемо сокращается:
      select
        count(1)
      from
        A
      where
        b='smth' and c in ('общий префикс - суффикс 1','общий префикс - суффикс 2')

SQL Server Execution Times:
CPU time = 46 ms, elapsed time = 65 ms.

      select
        count(1)
      from
        A
      where
        b='smth' and left(c,длина общего префикса)= 'общий префикс'

SQL Server Execution Times:
CPU time = 5687 ms, elapsed time = 1934 ms.

Но как только запрос усложняется, толку от индекса сразу становится нуль:
      select
        count(1)
      from
        A join B on A.a = B.A
      where
        A.b='smth' and A.c in ('общий префикс - суффикс 1','общий префикс - суффикс 2')

SQL Server Execution Times:
CPU time = 10673 ms, elapsed time = 5561 ms.

      select
        count(1)
      from
        A join B on A.a = B.A
      where
        A.b='smth' and left(A.c,длина общего префикса)= 'общий префикс'

SQL Server Execution Times:
CPU time = 9078 ms, elapsed time = 5230 ms.

Почему это может происходить и как от этого спасаться?
13 июн 12, 13:28    [12707300]     Ответить | Цитировать Сообщить модератору
 Re: эффективность отфильтрованного индекса  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Все ответы на ваши "почему" - в планах выполнения ваших запросов.
13 июн 12, 13:43    [12707455]     Ответить | Цитировать Сообщить модератору
 Re: эффективность отфильтрованного индекса  [new]
Shlippenbaranus
Member

Откуда:
Сообщений: 241
Гавриленко Сергей Алексеевич
Все ответы на ваши "почему" - в планах выполнения ваших запросов.


Дык. Было б понятно из планов - я бы не спрашивал :(.

Судя по актуальному плану, для запроса "попроще" в одном из случаев происходит Table Scan, в другом - Index Csan, что понятно и ожидаемо. А для запроса "сложнее" - Table Scan независимо от синтаксиса.

Т.е. ответ как бы есть, но тривиальный: индекс не используется. Что я и так глазами вижу. Но почему?
13 июн 12, 14:26    [12707836]     Ответить | Цитировать Сообщить модератору
 Re: эффективность отфильтрованного индекса  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Shlippenbaranus
...Т.е. ответ как бы есть, но тривиальный: индекс не используется. Что я и так глазами вижу. Но почему?
А с чего ему использоваться?
Сервер обязан вернуть ВСЕ строки удовлетворяющие условию запроса!
Вы же пишите неполное условие из индекса.

Сообщение было отредактировано: 13 июн 12, 14:31
13 июн 12, 14:31    [12707873]     Ответить | Цитировать Сообщить модератору
 Re: эффективность отфильтрованного индекса  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
Shlippenbaranus
... как от этого спасаться?

а если заставить использовать этот индекс хинтом, что получится?
13 июн 12, 14:33    [12707886]     Ответить | Цитировать Сообщить модератору
 Re: эффективность отфильтрованного индекса  [new]
Shlippenbaranus
Member

Откуда:
Сообщений: 241
Shakill
а если заставить использовать этот индекс хинтом, что получится?


План запроса существенно усложняется, но результата это не дает.


tpg
А с чего ему использоваться?
Сервер обязан вернуть ВСЕ строки удовлетворяющие условию запроса!
Вы же пишите неполное условие из индекса.

Э... можно подробнее? Где у меня неполное условие из индекса?

Вот это условие,
where
        A.b='smth' and A.c in ('общий префикс - суффикс 1','общий префикс - суффикс 2')
из запроса идентично условию в индексе.
13 июн 12, 16:43    [12709156]     Ответить | Цитировать Сообщить модератору
 Re: эффективность отфильтрованного индекса  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
Shlippenbaranus
Shakill
а если заставить использовать этот индекс хинтом, что получится?


План запроса существенно усложняется, но результата это не дает.

"результата это не дает" переводится как "индекс в плане задействуется, но запрос работает долго, даже дольше чем без хинта"?
13 июн 12, 16:48    [12709195]     Ответить | Цитировать Сообщить модератору
 Re: эффективность отфильтрованного индекса  [new]
Shlippenbaranus
Member

Откуда:
Сообщений: 241
Shlippenbaranus
План запроса существенно усложняется, но результата это не дает.


Или точнее, дает, но не на два порядка, как это происходит при выполнении более простого запроса. На пару десятков процентов.

Кстати, скажите, может ли такое быть:

SQL Server Execution Times:
CPU time = 9359 ms, elapsed time = 4819 ms.

Т.е. полное время выполнения запроса как бы меньше, чем время, в течении которого трудился процессор :).

В каких единицах и как считаются эти значения?
13 июн 12, 17:00    [12709290]     Ответить | Цитировать Сообщить модератору
 Re: эффективность отфильтрованного индекса  [new]
Shlippenbaranus
Member

Откуда:
Сообщений: 241
Shakill
"результата это не дает" переводится как "индекс в плане задействуется, но запрос работает долго, даже дольше чем без хинта"?


Заметил эту неточность сам :). Запрос работает чуть-чуть быстрее. Не в сотню раз, как ожидалось.
13 июн 12, 17:03    [12709308]     Ответить | Цитировать Сообщить модератору
 Re: эффективность отфильтрованного индекса  [new]
RubinDm
Member

Откуда:
Сообщений: 461
Shlippenbaranus, попробуйте так:
select ...
from B
cross apply -- такая конструкция часто справляется с задачей правки мозга серверу в нужном направлении.
( select A.*
  from A
  where A.a = B.A -- в случае cross apply (не outer apply) такой фильтр отработает как B inner join A
    and (A.b='smth' and A.c in ('общий префикс - суффикс 1','общий префикс - суффикс 2'))
    -- фильтрованный индекс надо сделать по полю A.a, с включением прочих полей по мере необходимости.
) A
13 июн 12, 17:07    [12709334]     Ответить | Цитировать Сообщить модератору
 Re: эффективность отфильтрованного индекса  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
Shlippenbaranus
SQL Server Execution Times:
CPU time = 9359 ms, elapsed time = 4819 ms.

Т.е. полное время выполнения запроса как бы меньше, чем время, в течении которого трудился процессор :).

меньше чем время, в течение которого трудилось бы одно ядро :)
13 июн 12, 17:07    [12709338]     Ответить | Цитировать Сообщить модератору
 Re: эффективность отфильтрованного индекса  [new]
Ray D
Member

Откуда: from the middle of nowhere
Сообщений: 3598
Блог
RubinDm
Shlippenbaranus, попробуйте так:
select ...
from B
cross apply -- такая конструкция часто справляется с задачей правки мозга серверу в нужном направлении.

А можете объяснить почему?
13 июн 12, 17:25    [12709468]     Ответить | Цитировать Сообщить модератору
 Re: эффективность отфильтрованного индекса  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
Shlippenbaranus
Shlippenbaranus
План запроса существенно усложняется, но результата это не дает.


Или точнее, дает, но не на два порядка, как это происходит при выполнении более простого запроса. На пару десятков процентов

а на поле B.a есть индексы?
13 июн 12, 17:50    [12709709]     Ответить | Цитировать Сообщить модератору
 Re: эффективность отфильтрованного индекса  [new]
Ray D
Member

Откуда: from the middle of nowhere
Сообщений: 3598
Блог
Планы бы уже показали, все бы давно стало понятно.

автор
Все ответы на ваши "почему" - в планах выполнения ваших запросов.
13 июн 12, 17:53    [12709729]     Ответить | Цитировать Сообщить модератору
 Re: эффективность отфильтрованного индекса  [new]
Shlippenbaranus
Member

Откуда:
Сообщений: 241
RubinDm
Shlippenbaranus, попробуйте так:
select ...
from B
cross apply -- такая конструкция часто справляется с задачей правки мозга серверу в нужном направлении.
( select A.*
  from A
  where A.a = B.A -- в случае cross apply (не outer apply) такой фильтр отработает как B inner join A
    and (A.b='smth' and A.c in ('общий префикс - суффикс 1','общий префикс - суффикс 2'))
    -- фильтрованный индекс надо сделать по полю A.a, с включением прочих полей по мере необходимости.
) A

"в случае cross apply такой фильтр отработает как B inner join A" - да, согласен. И сервер тоже: планы получились идентичные :).

Shakill
а на поле B.a есть индексы?

Да, кластерный.

Но, может, это несущественно. Решив упростить до предела, я сделал вот так:
      select
        count(1)
      from
        A
      where
        b='smth' and c in ('общий префикс - суффикс 1','общий префикс - суффикс 2')
, и вот так:
      select
        *
      from
        A
      where
        b='smth' and c in ('общий префикс - суффикс 1','общий префикс - суффикс 2')
, и планы опять получились различные. В первом случае процесс начинается с Index Seek, во втором - с Table Scan. Не содержится ли тут ответ на вопросы?

Собственно, думал, что да - но повторил два последних запроса, заменив фильтрованный индекс обычным,
drop index ind_A on A 
create index ind_A on A (b,c)
и получил точно такой же результат. Чего-то я всерьез не понимаю.
13 июн 12, 19:31    [12710212]     Ответить | Цитировать Сообщить модератору
 Re: эффективность отфильтрованного индекса  [new]
Shlippenbaranus
Member

Откуда:
Сообщений: 241
Ray D
Планы бы уже показали, все бы давно стало понятно.

Ну, это надо руками их поправить (заменить реальные имена и значения условными), некогда было пока что :(
13 июн 12, 19:35    [12710232]     Ответить | Цитировать Сообщить модератору
 Re: эффективность отфильтрованного индекса  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Shlippenbaranus
Но, может, это несущественно. Решив упростить до предела, я сделал вот так:
      select
        count(1)
      from
        A
      where
        b='smth' and c in ('общий префикс - суффикс 1','общий префикс - суффикс 2')
, и вот так:
      select
        *
      from
        A
      where
        b='smth' and c in ('общий префикс - суффикс 1','общий префикс - суффикс 2')
, и планы опять получились различные. В первом случае процесс начинается с Index Seek, во втором - с Table Scan. Не содержится ли тут ответ на вопросы?

Собственно, думал, что да - но повторил два последних запроса, заменив фильтрованный индекс обычным,
drop index ind_A on A 
create index ind_A on A (b,c)
и получил точно такой же результат. Чего-то я всерьез не понимаю.
Ну прочитаете вы свои 400 тыщ айдишников из индекса, а за остальными данными то все равно в таблицу лезть, 400 тыщ раз, так что уж проще сразу скан таблицы чем ваш индекс. Добавляйте INCLUDE хотя бы...
14 июн 12, 00:13    [12710855]     Ответить | Цитировать Сообщить модератору
 Re: эффективность отфильтрованного индекса  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Shlippenbaranus
Ray D
Планы бы уже показали, все бы давно стало понятно.

Ну, это надо руками их поправить (заменить реальные имена и значения условными), некогда было пока что :(
Тогда учитесь понимать планы сами.
14 июн 12, 00:14    [12710857]     Ответить | Цитировать Сообщить модератору
 Re: эффективность отфильтрованного индекса  [new]
Shlippenbaranus
Member

Откуда:
Сообщений: 241
Mind
Ну прочитаете вы свои 400 тыщ айдишников из индекса, а за остальными данными то все равно в таблицу лезть, 400 тыщ раз, так что уж проще сразу скан таблицы чем ваш индекс. Добавляйте INCLUDE хотя бы...

Не могу согласиться.

Если нужна одна запись, что будет дешевле: прочитать айдишник из индекса, и вынуть ее по айдишнику, или сканировать таблицу? Очевидно, что первое.

Если нужны все записи, кроме одной, что будет дешевле? Очевидно второе.

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

В нашем случае 400 тыс. - это меньше, чем 3% от общего количества записей. Что есть СУЩЕСТВЕННО меньше, чем 10%. Поиск по индексу ДОЛЖЕН быть эффективен. Почему же сервер думает иначе? Что здесь может быть не учтено?
14 июн 12, 08:26    [12711257]     Ответить | Цитировать Сообщить модератору
 Re: эффективность отфильтрованного индекса  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Shlippenbaranus, а сколько стоит "обращение" 400тыс раз к таблице? (Key Lookup) Или вы думаете это бесплатно?
14 июн 12, 09:45    [12711588]     Ответить | Цитировать Сообщить модератору
 Re: эффективность отфильтрованного индекса  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Shlippenbaranus
...Почему же сервер думает иначе? Что здесь может быть не учтено?
Статистика, например, протухла.
14 июн 12, 09:50    [12711623]     Ответить | Цитировать Сообщить модератору
 Re: эффективность отфильтрованного индекса  [new]
Кот Матроскин
Member

Откуда: Москва
Сообщений: 8933
tpg,
Если бы статистика протухла - то указание индекса хинтом лечило бы ситуацию, а, по словам ТС, оно не лечит.


Shlippenbaranus
А Вы не хотите попробовать ваш индекс покрывающим сделать? В реальной схеме много полей придется добавлять?
Потому что пока, судя по вашим запросам и их результатам, разница ровно в этом - хватает ли индекса или приходится в таблицу лезть
14 июн 12, 12:14    [12712693]     Ответить | Цитировать Сообщить модератору
 Re: эффективность отфильтрованного индекса  [new]
Klopovnik
Member

Откуда: Рига
Сообщений: 39
Mind,

Копай в сторону перекрывающих индексов.
Планы разные так как:
в первом случае работа идет только с индексом, так как первый столбец есть в индексе
а во втором случае - он связывает по примарному ключу с основной таблицей и идет за дополнительными (не включенными в индекс данными) в основную таблицу...
Не забывайте - скл сервер всегда включает ключ таблицы в некластерный индекс, и индекс это такая же таблица, только с другим порядком строк
14 июн 12, 12:23    [12712791]     Ответить | Цитировать Сообщить модератору
 Re: эффективность отфильтрованного индекса  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Klopovnik
Не забывайте - скл сервер всегда включает ключ таблицы в некластерный индекс
Да ну нет конечно, не всегда.
14 июн 12, 12:25    [12712799]     Ответить | Цитировать Сообщить модератору
 Re: эффективность отфильтрованного индекса  [new]
Кот Матроскин
Member

Откуда: Москва
Сообщений: 8933
tpg,
Если бы статистика протухла - то указание индекса хинтом лечило бы ситуацию, а, по словам ТС, оно не лечит.


Shlippenbaranus
А Вы не хотите попробовать ваш индекс покрывающим сделать? В реальной схеме много полей придется добавлять?
Потому что пока, судя по вашим запросам и их результатам, разница ровно в этом - хватает ли индекса или приходится в таблицу лезть
14 июн 12, 12:25    [12712804]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить