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

Откуда:
Сообщений: 241
Добрый день, помогите понять, как работает оптимизатор. Наверняка это известная вещь, я с данным эффектом сталкивался неоднократно для мутных запросов, но сейчас как-то все очень просто, а правды, по прежнему, нет.

Есть большая таблица,
create table c (..., thetime datetime, theid varchar(20), theparameter varchar(80), ....)
(для понимания, это некоторый лог, отражающий работу оборудования; theid - идентификатор физического события, это не id записи в базе). По theid есть некластерный индекс, по thetime, соответственно, кластерный. Еще есть два индекса, налагаемых на thetime,theid и дополнительные поля:
create index ind_c1 on c (thetime,theid,....) include (theparameter)
create index ind_c2 on c (thetime,theid,....)
Эти индексы позволяют эффективно отбирать записи с учетом условия1 и условия2 соответственно.

Есть запрос:
        select
          c.thetime, c.theparameter
        from
          c
        join
          c d
        on
          d.thetime between @f and @t
          and d.theid = c.theid
          and условие1
        where
          c.thetime between @f and @t
          and условие2
- отрабатывает за несколько секунд. План запроса показывает два параллельных, сопоставимых по весу, процесса вида Index Seek -> Parallelizm -> Sort, где один Index Seek осуществляется по индексу ind_c1, второй по индексу ind_c2, после чего идет Merge Join и удовольствие :). В общем, все приблизительно так, как и хочется.

Но когда мы хотим отобрать из полученных записей те, которые соответствуют дополнительному условию isnumeric(theparameter)=0, и для этого пишем, допустим,
        select
          c.thetime, c.theparameter
        from
          c
        join
          c d
        on
          d.thetime between @f and @t
          and d.theid = c.theid
          and условие2
        where
          c.thetime between @f and @t
          and условие1
          and isnumeric(с.theparameter)=0
- оптимизатор сходит с ума. Запрос выполняется неск-ко десятков минут, причем согласно ожидаемому плану (прилагаю; дожаться актуального здоровья не хватило), используются исключительно индексы по theid и thetime, а результат достигается за счет кошмарных Nested Loops и Key Lookup.

ПОЧЕМУ????

Что мешает системе в качестве первого шага отобрать записи тем же способом, что и для первого запроса, а в кач-ве второго - выбрать из них отвечающие условию isnumeric(с.theparameter)=0 ???? Что мешает сделать вот это
      select
        *
      from (
        select
          c.thetime, c.theparameter
        from
          c
        join
          c d
        on
          d.thetime between @f and @t
          and d.theid = c.theid
          and условие2
        where
          c.thetime between @f and @t
          and условие1
        ) c
      where
        isnumeric(c.theparameter)=0
буквально? И что, напротив, заставляет превращать последний запрос в предпоследний, генерируя для него точно такой же гадостный план.

Я б еще как-то понял, если бы в силу особенностей ms sql проверка условия isnumeric(theparameter)=0 была б настолько тяжелой, что "шунтировала" бы собой всю остальную оптимизацию. Т.е. в виду необходимости потратить очень много времени на проверку условия, на все остальное сервер бы клал. Но ведь нет. Если сделать вот так:
        select
          c.thetime, c.theparameter
        into
          #c
        from
          c
        join
          c d
        on
          d.thetime between @f and @t
          and d.theid = c.theid
          and условие2
        where
          c.thetime between @f and @t
          and условие1

       select * from #c where isnumeric(theparameter)=0
время выполнения второго запроса оказывается мало по сравненю со временем выполнения первого. Так в чем же дело? Почему сервер не может решить такую простую задачу? И, главное, как это можно побороть?

Версия сервера Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.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) (Hypervisor)
18 окт 12, 18:06    [13342591]     Ответить | Цитировать Сообщить модератору
 Re: Как здесь работает оптимизатор?  [new]
Shlippenbaranus
Member

Откуда:
Сообщений: 241
Стоп, первый (быстрый) запрос выглядит так:
        select
          c.thetime, c.theparameter
        from
          c
        join
          c d
        on
          d.thetime between @f and @t
          and d.theid = c.theid
          and условие2
        where
          c.thetime between @f and @t
          and условие1

Т.е. он ничем не отличается от второго, за исключением проверки на isnumeric.
18 окт 12, 18:11    [13342616]     Ответить | Цитировать Сообщить модератору
 Re: Как здесь работает оптимизатор?  [new]
Shlippenbaranus
Member

Откуда:
Сообщений: 241
"с данным эффектом сталкивался неоднократно" - имеется в виду то явление, когда многократного увеличения скорости выполнения запроса, предполагающего ограничение выборки в соответствии с некоторым условием, удается достичь, сняв это условие, сбросив данные во временную таблицу, и наложив условие на select из этой таблицы.
18 окт 12, 18:20    [13342641]     Ответить | Цитировать Сообщить модератору
 Re: Как здесь работает оптимизатор?  [new]
skyANA
Member

Откуда: Зеленоград
Сообщений: 28355
with c (thetime, theparameter) as
(
        select
          c.thetime, c.theparameter
        from
          c
        join
          c d
        on
          d.thetime between @f and @t
          and d.theid = c.theid
          and условие2
        where
          c.thetime between @f and @t
          and условие1
)
select * from c where isnumeric(theparameter)=0
?
18 окт 12, 19:04    [13342826]     Ответить | Цитировать Сообщить модератору
 Re: Как здесь работает оптимизатор?  [new]
Crimean
Member

Откуда:
Сообщений: 13147
я, обычно, "усиливаю" или "ослабляю" условия. в двух словах не описать :)
в данном случае, навскидку, можно использовать что-то типа
and exists( select 1 where isnumeric( FIELD ) = 1 ) для "ослабления" и and FIELD like '%[0-9]%' для "усиления"
p.s.
и это еще не самые страшные конструкции
18 окт 12, 19:16    [13342875]     Ответить | Цитировать Сообщить модератору
 Re: Как здесь работает оптимизатор?  [new]
Shlippenbaranus
Member

Откуда:
Сообщений: 241
skyANA,
благодарю за идею, с cte то же самое. Да и с чего б ему, по логике отличаться? Хотя, впрочем, да. С логикой тут некоторые проблемы :).

Crimean,
эээ, т.е. это нормально? Я надеялся, вы (с маленькой буквы, т.е. сообщество) меня ткнете в ошибку, но из Ваших (с большой буквы) слов следует, что ситуация является распространенной, и рационально не лечится. Я Вас правильно понял? Про игры с условием понял, спасибо, попробую.
18 окт 12, 19:38    [13342940]     Ответить | Цитировать Сообщить модератору
 Re: Как здесь работает оптимизатор?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Shlippenbaranus
причем согласно ожидаемому плану (прилагаю; дожаться актуального здоровья не хватило)
А план то где?

Попробуйте создать вычисляемую колонку по isnumeric(theparameter). При этом в запросе её использовать не обязательно, можно оставить как есть.
18 окт 12, 20:09    [13343067]     Ответить | Цитировать Сообщить модератору
 Re: Как здесь работает оптимизатор?  [new]
Crimean
Member

Откуда:
Сообщений: 13147
Mind
Попробуйте создать вычисляемую колонку по isnumeric(theparameter). При этом в запросе её использовать не обязательно, можно оставить как есть.


и проиндексировать? или статистику создать? или persist? а то просто так создать - толку не будет
18 окт 12, 22:07    [13343425]     Ответить | Цитировать Сообщить модератору
 Re: Как здесь работает оптимизатор?  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Shlippenbaranus
это нормально?
А как вы думаете, какова вероятность что условие сработает?
IsNumeric(с.TheParameter) = 0
50% на 50% Либо сработает, либо не сработает.

Предложенный вариант Mind с вычисляемой колонкой должен автоматически создать статистику (не сразу) и тогда оптимизатор будет более реальнее оценивать количество строк в запросе.
18 окт 12, 22:13    [13343454]     Ответить | Цитировать Сообщить модератору
 Re: Как здесь работает оптимизатор?  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Crimean
и проиндексировать? или статистику создать? или persist? а то просто так создать - толку не будет
Упс. А чё не делает для вычисляемых? А если руками создать?
А где это в BOL написано?
18 окт 12, 22:15    [13343463]     Ответить | Цитировать Сообщить модератору
 Re: Как здесь работает оптимизатор?  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Если пройтись по BOL-у то вроде нигде не сказано, что автосоздание не сработает.
Разве что какие-то баги с не-детерминистическими функциями. Но IsNumeric вроде как детерминистическая.
18 окт 12, 22:36    [13343565]     Ответить | Цитировать Сообщить модератору
 Re: Как здесь работает оптимизатор?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Mnior
Если пройтись по BOL-у то вроде нигде не сказано, что автосоздание не сработает.
Разве что какие-то баги с не-детерминистическими функциями. Но IsNumeric вроде как детерминистическая.
Автоматом создаст, проверено, при следующем запуске, ибо запрос однозначно будет рекомпилирован.
18 окт 12, 23:07    [13343687]     Ответить | Цитировать Сообщить модератору
 Re: Как здесь работает оптимизатор?  [new]
Crimean
Member

Откуда:
Сообщений: 13147
это если не отключено автосоздание :) ну и может понадобиться конекшен сеты подправить
и - в ряде случаев все равно может не помочь..
18 окт 12, 23:13    [13343708]     Ответить | Цитировать Сообщить модератору
 Re: Как здесь работает оптимизатор?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Crimean
это если не отключено автосоздание :)
Я бы за такое вообще убивал.
Crimean
ну и может понадобиться конекшен сеты подправить
А это про что?
Crimean
и - в ряде случаев все равно может не помочь..
Попробовать стоит. Ну и посмотреть на эстимейты естественно.
18 окт 12, 23:22    [13343738]     Ответить | Цитировать Сообщить модератору
 Re: Как здесь работает оптимизатор?  [new]
Crimean
Member

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

иногда статистики реально мешают. когда хорошее вроде поле вредно индексить или просто условия сложные
конекшен сеты - при работе с вычисляемыми полями всякие арифаборты и ансинулы роялят, а чем тут к базе ходят мы не знаем, вдруг одибись или прости госпроди дб-либ?
19 окт 12, 00:16    [13343877]     Ответить | Цитировать Сообщить модератору
 Re: Как здесь работает оптимизатор?  [new]
andrey_z
Member

Откуда:
Сообщений: 196
В быстром варианте Index Seek для алиаса "c" (c.thetime between ...) делается по ind_c1 или ind_c2?
19 окт 12, 08:38    [13344237]     Ответить | Цитировать Сообщить модератору
 Re: Как здесь работает оптимизатор?  [new]
Shlippenbaranus
Member

Откуда:
Сообщений: 241
Спасибо откликнувшимся.

andrey_z
В быстром варианте Index Seek для алиаса "c" (c.thetime between ...) делается по ind_c1 или ind_c2?


В быстром варианте Index Seek для алиаса "c" (c.thetime between ...) делается по ind_c1. theparameter входит в этот индекс, как included колонка, необходимость делать Key Lookup отсутствует, что подтверждается поведением сервера в том случае, когда theparameter нужно просто включить в результирующий датасет.

Mind
А план то где?


Действительно, не приложился. Пытаюсь еще раз.

Mind, Mnior, Crimean,

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

1. Непонятно, зачем это нужно, как показывает первый (быстрый) запрос, оптимизатор вполне адекватно оценивает количество строк для запроса без дополнительного условия, и делает правильный вывод: использовать индексы. А ведь это количество строк представляет собой верхнюю границу для количества строк с доп. условием. Т.е. какие бы новые ограничения не налагались им на данные, которые могут быть отобраны в рамках Index Seek по ind_c1, количество строк в результирующем наборе гарантированно не будет больше. Так что эффективность обращения к Index Seek по ind_c1 по сравнению с более тяжелыми процедурами (Index Seek по индексам для thetime и для theid + Key Lookup) очевидна - что и показывает эксперимент с разделением одного запроса на две части. Кто здесь тупит - я или парни из Редмонда? (Надеюсь, что я: я исправлюсь, а вот они - не гарантирую :))

2. Как правильно предположил Crimean, к базе тут ходят, в том числе, и по odbc, так что играться с индексами по вычисляемым полям довольно рискованно. Да и не выход это, если системно подходить. Запросов бывает много и разных, не обвешивать же таблицу вычисляемыми полями по каждому случаю. Даже, если индексы по ним не обязательны.

К сообщению приложен файл. Размер - 32Kb
19 окт 12, 13:12    [13346318]     Ответить | Цитировать Сообщить модератору
 Re: Как здесь работает оптимизатор?  [new]
andrey_z
Member

Откуда:
Сообщений: 196
thetime, theid и theparameter могут же быть NULL? Есть такое теоретическое предположение, что оптимизатор не может разложить исходные условия на диапазоны индексов, из-за того что не может оценить NULL/не-NULL результат isnumeric(). Ну или как-то так.
19 окт 12, 13:29    [13346468]     Ответить | Цитировать Сообщить модератору
 Re: Как здесь работает оптимизатор?  [new]
andrey_z
Member

Откуда:
Сообщений: 196
Извиняюсь, с NULL в isnumeric() я перемудрил (она же возвращает только 0 или 1). Но NULL может быть причиной.
19 окт 12, 13:43    [13346591]     Ответить | Цитировать Сообщить модератору
 Re: Как здесь работает оптимизатор?  [new]
Shurgenz
Member

Откуда: Питер
Сообщений: 1938
andrey_z
thetime, theid и theparameter могут же быть NULL? Есть такое теоретическое предположение, что оптимизатор не может разложить исходные условия на диапазоны индексов, из-за того что не может оценить NULL/не-NULL результат isnumeric(). Ну или как-то так.


ноги мне кажется растут от этого... условие на IS NOT NULL попробовать добавить.

ну можно еще EXISTS и NOT EXISTS (о чем Crieman писал кстати) поскольку для NULL значений работает как бы тройственная логика, а для EXISTS двойственная.

ну вот запрос к примеру:

select 1
where 1 not in (select 2 a union all select null)

не вернет ничего, хотя в подзапросе 1 нет
19 окт 12, 13:46    [13346618]     Ответить | Цитировать Сообщить модератору
 Re: Как здесь работает оптимизатор?  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Shlippenbaranus
Т.е. какие бы новые ограничения не налагались им на данные, которые могут быть отобраны в рамках Index Seek по ind_c1, количество строк в результирующем наборе гарантированно не будет больше.
Логическая ошибка.

Вот допустим оптимизатор смог сгенерировать только два плана запроса (Merge двух индексов и Lokup), далее он будет выбирать какой из них выбрать.

А) Если нет дополнительного выражения <Expr1> (IsNumeric(X) = 0), то видно что накладные расходы на Lookup выше - слишком много строк.
(Merge-А vs Lokup-А)

Б) Если есть дополнительного выражения <Expr1>, то из-за отсутствия статистики он подумал что строк будет в 2 раза меньше (к примеру) и поэтому Lookup намного эффективнее, ибо подымать целых 2 индекса и фильтровать накладнее.
(Merge-Б vs Lokup-Б)

Это сразу видно, вы сравните количество сток и стоимости у этих планов.

Т.е. он не сравнивает планы с условием и без него (это ж сколько комбинаций может быть), он сравнивает всевозможные планы со всему запросу целиком.
19 окт 12, 14:03    [13346762]     Ответить | Цитировать Сообщить модератору
 Re: Как здесь работает оптимизатор?  [new]
Crimean
Member

Откуда:
Сообщений: 13147
Shlippenbaranus
не обвешивать же таблицу вычисляемыми полями по каждому случаю


а почему нет? обычно, число "стабильных" вычислений более чем конечно
и - для всех клиентских либов можно найти способы управления конекшен сетами
другой вопрос - как на это отреагируют существующие прикладные запросы, но это также решаемо
19 окт 12, 16:06    [13347745]     Ответить | Цитировать Сообщить модератору
 Re: Как здесь работает оптимизатор?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Shlippenbaranus,

План запроса показывает два [b]параллельных[/b], сопоставимых по весу, процесса вида Index Seek -> [b]Parallelizm[/b] -> Sort

Но ожидаемый план "плохого" запроса не содержит параллелизма. Если так, то посмотрите, какое именно условие вы добавляете, не предотвращает ли оно в каким-то образом параллелизм в запросе? Попробуйте в "быстром" запросе ограничить параллелизм option(maxdop 1), как план изменит форму и станет ли он настолько же плохо выполняться что и медленный?
19 окт 12, 16:29    [13347914]     Ответить | Цитировать Сообщить модератору
 Re: Как здесь работает оптимизатор?  [new]
Shlippenbaranus
Member

Откуда:
Сообщений: 241
andrey_z
thetime, theid и theparameter могут же быть NULL? .... NULL может быть причиной.

Shurgenz
ноги мне кажется растут от этого... условие на IS NOT NULL попробовать добавить.


thetime и theid не могут быть NULL по определению, theparameter может быть NULL. Но theparameter не участвует в индексации, это included-колонка, вряд ли он может мешать разложить исходные условия на диапазоны индексов.
19 окт 12, 18:36    [13348470]     Ответить | Цитировать Сообщить модератору
 Re: Как здесь работает оптимизатор?  [new]
Shlippenbaranus
Member

Откуда:
Сообщений: 241
Crimean
Shlippenbaranus
не обвешивать же таблицу вычисляемыми полями по каждому случаю


а почему нет? обычно, число "стабильных" вычислений более чем конечно
и - для всех клиентских либов можно найти способы управления конекшен сетами


Как сказать. Есть такой принцип: "Работает? Забетонируй!". Я не администрирую все приложения, которые гадя... извиняюсь, кладут свои данные в базу, и не очень хочу изчать драйвера оборудования. Плюс, боюсь создавать ситуацию, что появится умный админ, пожмет плечами, что-нибудь перенастроит, а отсутствие данных будет обнаружено через неделю. Уже было такое, что пришел программист, обнаружил отсутствие индекса, создал его под себя. По вычисляемому полю в критически важной таблице, ага.

Эти проблемы решаются, да, но я, для начала, стараюсь их не создавать :).
19 окт 12, 18:37    [13348473]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить