Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Оптимизатор SQL Server 2012 превращает предикат IN в запросе в выражение >= и <=  [new]
АБД из Казани
Guest
Оптимизатор SQL Server 2012 превращает предикат IN в запросе в выражение >= и <=.
Например, во фразе where написано Client in (100, 100000), в плане выполнения Client >= 10 and Client <= 100000.
В SQL Server 2005 такого поведения не было (было Client = 10 or Client = 100000).
Критично на больших объемах.
Реальный запрос привести не могу-слишком сложный для стороннего воспроизведения.
Со статистикой все в порядке.
Все необходимые индексы построены.
Переписать запрос - чужое ПО.

Что можно предпринять для решения проблемы ?
20 апр 14, 21:08    [15907864]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL Server 2012 превращает предикат IN в запросе в выражение >= и <=  [new]
Glory
Member

Откуда:
Сообщений: 104751
АБД из Казани
Реальный запрос привести не могу-слишком сложный для стороннего воспроизведения

Ну так почему это тогда проблема ?
Конечный результат что ли неправильный ?
Или вам просто не нравится этот конкретный план выполнения ?
20 апр 14, 21:10    [15907875]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL Server 2012 превращает предикат IN в запросе в выражение >= и <=  [new]
АБД из Казани
Guest
Glory,

Тормозит на больших объемах,
сканирование большего объема данных
20 апр 14, 21:15    [15907887]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL Server 2012 превращает предикат IN в запросе в выражение >= и <=  [new]
Glory
Member

Откуда:
Сообщений: 104751
АБД из Казани
сканирование большего объема данных

Ну так у вас же "очень сложный запрос"
Если вы знаете лучший план, то помогите серверу с его нахождением
20 апр 14, 21:17    [15907889]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL Server 2012 превращает предикат IN в запросе в выражение >= и <=  [new]
АБД из Казани
Guest
Как заставить оптимизатор использовать OR, а не BETWEEN (>= и <=) ?
20 апр 14, 21:20    [15907902]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL Server 2012 превращает предикат IN в запросе в выражение >= и <=  [new]
Glory
Member

Откуда:
Сообщений: 104751
АБД из Казани
Как заставить оптимизатор использовать OR, а не BETWEEN (>= и <=) ?


- переписать запрос
- создать индексы
- использовать хинты
- использовать plan guide
20 апр 14, 21:29    [15907929]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL Server 2012 превращает предикат IN в запросе в выражение >= и <=  [new]
SomewhereSomehow
Member

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

Действительный план давайте.

Оптимизатор может выполнять преобразование в диапазон (см. Dynamic Seeks and Hidden Implicit Conversions).
Но чтобы вот так вдруг...и даже без residual предиката? и Seek меняется на Scan? (При этом нужно различать точечный поиск от того же сканирования, которое отображается как Seek).

Опыт работы с сиквелом научил меня - всегда есть возможность наткнуться на что-то новое, так что никогда не стоит отвергать даже самых сверхъестественных заявлений. Но тот же опыт научил сперва проверять - а правда ли, и если да, то не описано ли это в документации, а если нет то не описано ли это на коннекте, а если нет то не описано ли это в блогах. Если нет - то поздравляю, вы нашли что-то новое! =)

Для начала - нужен действитеольный план. Если оттуда будет непонятно, то структура таблиц/индексов/размеры, самое кошерное - слепок статистики, чтобы воспроизвести (это если багу будем искать).
20 апр 14, 21:39    [15907964]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL Server 2012 превращает предикат IN в запросе в выражение >= и <=  [new]
АБД из Казани
Guest
Glory
АБД из Казани
Как заставить оптимизатор использовать OR, а не BETWEEN (>= и <=) ?


- переписать запрос
- создать индексы
- использовать хинты
- использовать plan guide


Можно попробовать plan guide,
но похоже на баг оптимизатора - plan guide на каждый случай не напасешься
20 апр 14, 21:39    [15907967]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL Server 2012 превращает предикат IN в запросе в выражение >= и <=  [new]
Glory
Member

Откуда:
Сообщений: 104751
АБД из Казани
но похоже на баг оптимизатора

Когда кажется, то надо креститься. Или преводить репро.
20 апр 14, 21:41    [15907972]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL Server 2012 превращает предикат IN в запросе в выражение >= и <=  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
АБД из Казани
Можно попробовать plan guide,
но похоже на баг оптимизатора - plan guide на каждый случай не напасешься


План, план ждем.
Очень интересно было бы, если бы это был баг. Но я не обнадеживаю себя.
20 апр 14, 21:43    [15907977]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL Server 2012 превращает предикат IN в запросе в выражение >= и <=  [new]
SomewhereSomehow
Member

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

Кстати, два плана давайте лучше. 2005 и 2012 - сравним и посмотрим.
20 апр 14, 21:50    [15907993]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL Server 2012 превращает предикат IN в запросе в выражение >= и <=  [new]
SomewhereSomehow
Member

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

И еще, не думайте за других, что сложно, а что нет - давайте реальный, действительный план того запроса, который тормозит.
Есть конечно очень сложные планы, но иногда в них можно выделить предполагаемый проблемный кусок. Есть совсем жесть - ну тогда лучше уж запрос разбить на несколько, глядишь и проблема решится.
20 апр 14, 21:55    [15908001]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL Server 2012 превращает предикат IN в запросе в выражение >= и <=  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
АБД из Казани
но похоже на баг оптимизатора
Да баг, я тоже уверен.
Только вот в чём сложилась проблема. Никто из нас кто его видел не смогли перевести его из своего проекта в репро.
Я понимаю что вы не можете секретную инфу показывать, но на самом деле может именно вы можете поменять это положение во всём мире.

SomewhereSomehow - это тот кто тут неоднократно клялся (простите за оборот) что держит M$ за одно место и может сдвинуть проблему с мёртвой точки. Так что АБД из Казани, можете всётаки найти время сделать копию таблиц, заполнить данные рыбой (заменить галимотьёй), задать левые названия и выдать репо. Это трудоёмко но необходимо всем нам.

Если вам нужна помощь для этого, думаю каждый из нас откликнется.
21 апр 14, 01:06    [15908577]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL Server 2012 превращает предикат IN в запросе в выражение >= и <=  [new]
АБД из Казани
Guest
Максимально упростил свой проблемный запрос.
Скрипт для воспроизведения на 2012 и 2005 (планы абсолютно разные, на 2005 выполняется на порядок быстрее) :
use Test
GO
-- создание объектов
create table Clients (
  ClientId integer identity not null primary key,
  Name varchar(250) not null
)
GO
create table Accounts (
  Account char(20) not null primary key,
  Client integer not null foreign key references Clients,
  Client2 integer not null foreign key references Clients,
  Name varchar(250) not null
)
GO
-- заполнение данными
set nocount on
declare @i integer
set @i=1
while @i <= 20000 begin
  insert Clients (Name) values ('Client number '+CAST(@i as varchar(10)))
  insert Accounts (Account,Client,Client2,Name) values (cast(@i as char(20)),@i,@i,'Account number '+CAST(@i as varchar(10)))
  set @i=@i+1
end    
GO
-- проблемный упрощенный запрос 
select c.Clientid
from Clients as c  
left join (
  select * from (    
    select c.Clientid, ROW_NUMBER() Over(Partition by c.Clientid Order by c.Clientid) as rn
    from Clients as c  
    join Accounts as a on c.ClientId in (a.Client,a.Client2)     
  ) j1 where isnull(rn,1)=1
) j2 on c.ClientId=j2.ClientId   
where c.ClientId in (443,10851) 
GO
21 апр 14, 14:24    [15910929]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL Server 2012 превращает предикат IN в запросе в выражение >= и <=  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
1.
create index Acc_Client on Accounts (Client);
create index Acc_Client2 on Accounts (Client2);

2.
select c.Clientid
from Clients as c  
where c.ClientId in (443,10851) and
(
 exists(select 1 from Accounts where Client = c.ClientId) or
 exists(select 1 from Accounts where Client2 = c.ClientId)
);
21 апр 14, 15:02    [15911198]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL Server 2012 превращает предикат IN в запросе в выражение >= и <=  [new]
АБД из Казани
Guest
invm
1.
create index Acc_Client on Accounts (Client);
create index Acc_Client2 on Accounts (Client2);

2.
select c.Clientid
from Clients as c  
where c.ClientId in (443,10851) and
(
 exists(select 1 from Accounts where Client = c.ClientId) or
 exists(select 1 from Accounts where Client2 = c.ClientId)
);


Ваш вариант не эквивалентен по результату исходному.
Переписать запрос не могу (чужой софт). Я упростил запрос, сохраняя его внутренню логику.
Нужно иметь в виду, что на самом деле текст запроса кроме последней строки - это тело view, и условие в последней строке налагается на view
21 апр 14, 15:26    [15911376]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL Server 2012 превращает предикат IN в запросе в выражение >= и <=  [new]
SomewhereSomehow
Member

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

Спасибо, за хорошее репро. Я даже не поленился поставить себе 2005 сервер, чтобы посмотреть на план.

Это тот случай, когда минус на минус дает плюс. Давайте по порядку.

автор
было Client = 10 or Client = 100000


Так есть и сейчас.

Посмотрите на план, у вас там два обращения к таблице Clients. Одно использует два одиночных поиска по 443 и 10851, второе интервал. Можете еще заменить в репро c.ClientId in (443,10851) на c.ClientId = 443 or c.ClientId = 10851. Разницы не увидите. О чем я собственно и говорил выше.

Картинка с другого сайта.

Интересно другое - а именно, причина такого поведения.

Если вы оставите в IN одно значение, оптимизатор его успешно протолкнет ниже и все выполнится быстро, но как только значений больше чем одно, приоритет имеет ранжирование, т.е. если протолкнуть оператор поиска по четкому равенству на нижний уровень, то он повлияет на ROW_NUMBER, что повлияет на результат. Однако, поле ClientID используется в предложении partition by - в таком случае, все должно быть в порядке. Но увы, оптимизатор оказывается недостаточно умен. У него вообще есть проблемы с оконными функциями, вот тут, пару лет назад мы с Paul White обсуждали эту проблему (в комментариях к статье), похоже, этот случай можно отнести туда же.

Теперь, что касается плана в 2005-м. В 2005 тоже во втором случае использует интервал (видно из плана). Только оценки и порядок таблиц другой. Как я и говорил - тот случай, когда оптимизатор сам себе стреляет в ногу, или минус на минус дает плюс.

Иногда, менее точная оценка в одном месте, за счет некоторых особенностей модели, приводит к более быстрому плану, за счет выбора другой формы/типа соединения/резервирования большего количества памяти и т.д. Как раз такой случай. В более новой версии сервер использует четкие константы 443 и 10851 и гистограмму, для оценки, в раннем - динамические условия. Область выделенная красным, это формирование диапазона поиска когда значения поиска неизвестны (исключение дубликатов, сортировка, и т.д. - т.е. чтобы тогда, когда запрос будет выполняться, неизвестные значения были сформированы в правильный интервал). Значение выражений неизвестно, сервер использует догадки, что оказывается абсолютно верным. Выбирается такой порядок таблиц.

В 2012 порядок таблиц выбирается очень неудачно. Это и является причиной медленного плана.

Попробуйте заменить на неизвестные - увидите, что порядок таблиц поменялся и запрос выполняется в секунду, вместо 15 секунд (на моей машине).

declare @c1 int= 443, @c2 int = 10851

-- проблемный упрощенный запрос 
select c.Clientid
from Clients as c  
left join (
  select * from (    
    select c.Clientid, ROW_NUMBER() Over(Partition by c.Clientid Order by c.Clientid) as rn
    from Clients as c  
    join Accounts as a on c.ClientId in (a.Client,a.Client2)     
  ) j1 where isnull(rn,1)=1
) j2 on c.ClientId=j2.ClientId   
where c.ClientId in (@c1,@c2) 


Что можно с этим поделать.

Можно заменить на локальные переменные. Можно заменить на табличную переменную, если критериев несколько. Получите похожий план, как в 2005.

declare @t table(ClientId int primary key)
insert @t values (443),(10851)

-- проблемный упрощенный запрос 
select c.Clientid
from Clients as c  
left join (
  select * from (    
    select c.Clientid, ROW_NUMBER() Over(Partition by c.Clientid Order by c.Clientid) as rn
    from Clients as c  
    join Accounts as a on c.ClientId in (a.Client,a.Client2)     
  ) j1 where isnull(rn,1)=1
) j2 on c.ClientId=j2.ClientId   
where c.ClientId in (select ClientId from @t)

+ план с табличной переменной
Картинка с другого сайта.


Можете индексами поиграться. Я не знаю какая у вас там в реальности система, так что конкретных рекомендаций давать не буду.

Попутно, можете зафайлить этот прискорбный факт на коннект о регрессии в плане, может они что-то поправят, к тому же такое же поведение и в 2014 СТР2. Кстати если поднять оценку, например недокументированной командой update statistics Clients with rowcount = 80000, чтобы приблизить ее к оценке 20 000 в 2005 - то ситуация не меняется, значит дело не только в оценке но и в эвристике выбора порядка соединений.


Mnior
SomewhereSomehow - это тот кто тут неоднократно клялся (простите за оборот) что держит M$ за одно место и может сдвинуть проблему с мёртвой точки.

Чего-чего? Что куришь там?
21 апр 14, 20:53    [15912878]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL Server 2012 превращает предикат IN в запросе в выражение >= и <=  [new]
SomewhereSomehow
Member

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

Поправка. план с табличной переменнойт - случайно выложил план с неизвестными константами. План с табличной переменной можете получить сами.
21 апр 14, 21:16    [15912926]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL Server 2012 превращает предикат IN в запросе в выражение >= и <=  [new]
АБД из Казани
Guest
SomewhereSomehow,

Спасибо за развернутый подробный ответ.
Попробую предложенное решение на реальном запросе и сообщу о результате.
21 апр 14, 22:21    [15913110]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL Server 2012 превращает предикат IN в запросе в выражение >= и <=  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
АБД из Казани,

Можете еще plan guide прикрутить:
declare @sql nvarchar(max), @params nvarchar(max), @s nvarchar(max) = N'select c.Clientid
from Clients as c  
left join (
  select * from (    
    select c.Clientid, ROW_NUMBER() Over(Partition by c.Clientid Order by c.Clientid) as rn
    from Clients as c  
    join Accounts as a on c.ClientId in (a.Client,a.Client2)     
  ) j1 where isnull(rn,1)=1
) j2 on c.ClientId=j2.ClientId   
where c.ClientId in (443,10851);';

exec sp_get_query_template @s, @sql output, @params output;
exec sp_create_plan_guide N'pg1', @sql, N'template', null, @params, N'option(parameterization forced)';
go

select c.Clientid
from Clients as c  
left join (
  select * from (    
    select c.Clientid, ROW_NUMBER() Over(Partition by c.Clientid Order by c.Clientid) as rn
    from Clients as c  
    join Accounts as a on c.ClientId in (a.Client,a.Client2)     
  ) j1 where isnull(rn,1)=1
) j2 on c.ClientId=j2.ClientId   
where c.ClientId in (443,10851); 
go

exec sp_control_plan_guide N'drop', N'pg1';
go
21 апр 14, 22:32    [15913181]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL Server 2012 превращает предикат IN в запросе в выражение >= и <=  [new]
АБД из Казани
Guest
invm,
Спасибо.

SomewhereSomehow,
Ваше решение работает, спасибо.

Буду связываться с разработчиками ПО с предложением исправления кода ПО
22 апр 14, 09:45    [15914121]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL Server 2012 превращает предикат IN в запросе в выражение >= и <=  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
SomewhereSomehow
все должно быть в порядке. Но увы, оптимизатор оказывается недостаточно умен. У него вообще есть проблемы с оконными функциями, вот тут, пару лет назад мы с Paul White обсуждали эту проблему (в комментариях к статье), похоже, этот случай можно отнести туда же.
Да было море обсуждений у многих юзверей.
Вот только то, что вы обсуждали это с самим Paul White и при этом нет никакого профита - это только ещё сильнее деморализует.

SomewhereSomehow
Попутно, можете зафайлить этот прискорбный факт на коннект о регрессии в плане, может они что-то поправят ...
ИМХО говорит о том что тут уже происходит перегиб и скорее ТС не преодолеет эту планку.
Тут ещё надо знать буржуйский, притом ракурс того поноса "претензий" который попадает на коннект, требует знать его не плохо чтобы случайно не быть проигнорированным из-за плохо подобранного словца.

Даю 99% что "АБД из Казани" не будет лазить ни в какой там непонятный "коннект".
SomewhereSomehow
Что куришь там?
То что предлагаете, не больше. А что не так?
22 апр 14, 13:06    [15915500]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL Server 2012 превращает предикат IN в запросе в выражение >= и <=  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Mnior
А что не так?

То, что я не клялся, что держу кого-то за какие-то места.
22 апр 14, 13:56    [15915900]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL Server 2012 превращает предикат IN в запросе в выражение >= и <=  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
SomewhereSomehow,

Это всего лишь оборот, для мотивации оппонента (ТС).
Но и читая вас, складывалось впечатление что вы на короткой ноге, но главное что вы довольно не безразлично относитесь к движку скуля и что делают разрабы. Что меня удивляет, ибо я не чувстую такого оптимизма в будущем движка, не вижу предпосылок.
Я не в теме, кто есть кто на этой поляне.
Так что если я неправ звиняйте, если я бы больше понимал ваши мотивы ...
22 апр 14, 18:30    [15918441]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить