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

Откуда:
Сообщений: 30
Есть процедура, в которой объявляется переменная типа Date (declare @ReportDate Date). В начале переменной присваивается значение отчетной даты:

set @ReportDate=(select MAX(ReportDate) from Bills)

затем в тексте процедуры происходит выборка из другой таблицы:

select * from Daily d where d.ReportDate>@ReportDate -- работает час
select * from Daily d where d.ReportDate>'2017-08-11' -- работает две секунды.
select * from Daily d where d.ReportDate>(select MAX(ReportDate) from Bills) -- работает две секунды.

ReportDate во всех таблицах имеет тип Date. Процедура работала нормально три года и только в прошлом месяце начался этот баг. Индексы новые не делались, старые я и реорганизовывал и пересоздавал - не помогло. SQL 2012. Было что то подобное несколько лет назад еще в 2008 SQL (в другой процедуре), как исправил не помню. В чем может быть баг?
14 авг 17, 13:01    [20721612]     Ответить | Цитировать Сообщить модератору
 Re: Жутко тупит запрос, при использовании в where переменной  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Невский,

Поздравляю! вы задали 10000000001 раз один и тот же вопрос
14 авг 17, 13:10    [20721652]     Ответить | Цитировать Сообщить модератору
 Re: Жутко тупит запрос, при использовании в where переменной  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2374
Невский,

это не баг, это фича
https://www.sql.ru/blogs/somewheresomehow/999
14 авг 17, 13:18    [20721686]     Ответить | Цитировать Сообщить модератору
 Re: Жутко тупит запрос, при использовании в where переменной  [new]
LSV
Member [заблокирован]

Откуда: Киев
Сообщений: 30817
Статистику обновляли ?
14 авг 17, 14:23    [20721945]     Ответить | Цитировать Сообщить модератору
 Re: Жутко тупит запрос, при использовании в where переменной  [new]
Невский
Member

Откуда:
Сообщений: 30
TaPaK
Невский, Поздравляю! вы задали 10000000001 раз один и тот же вопрос


Вообще не понял посыла. Если это намек, что я уже размещал такой вопрос, то сразу нет - ни разу его не задавал. Если это намек, что ответ буквально в каждом втором топике содержится, то я уже пару дней листаю форум и делаю поиск через гугл - результата пока ноль. Если Вы так хорошо осведомлены об этой проблеме и можете сходу ткнуть в ее решение, то буду благодарен.

StarikNavy
Невский, это не баг, это фича https://www.sql.ru/blogs/somewheresomehow/999


Не совсем то. Что то подобное я уже читал и Alter/sp_recompile для процедуры делал... Запуск в query analyzer всех трех вариантов запроса дает тот же результат - с переменной час, с константой и вложенным запросом секунда. Впрочем сейчас прочитаю еще раз более внимательно. Дело явно в кеше, так как план выполнения для запроса с переменной зачем то лезет к индексу, который содержит совсем другие поля, а план для константы и подзапроса обращается к правильному индексу...

LSV
Статистику обновляли ?

Пока нет, оставил это на тот случай, если решение не найдется.
14 авг 17, 14:51    [20722058]     Ответить | Цитировать Сообщить модератору
 Re: Жутко тупит запрос, при использовании в where переменной  [new]
Konst_One
Member

Откуда:
Сообщений: 11512
ТС,
а скрипт вашей таблицы с полем ReportDate мы дождёмся?
14 авг 17, 14:56    [20722079]     Ответить | Цитировать Сообщить модератору
 Re: Жутко тупит запрос, при использовании в where переменной  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Невский,

OPTION (RECOMPILE) на запрос и смотрите результат, потом читайте дальше
14 авг 17, 14:56    [20722081]     Ответить | Цитировать Сообщить модератору
 Re: Жутко тупит запрос, при использовании в where переменной  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7385
Невский,

У вас плохая статистика в таблице, по всей видимости. Неравномерное распределение значений даты, например. Можно хинты использовать вроде OPTIMIZE FOR () но это до поры будет работать.
14 авг 17, 15:14    [20722190]     Ответить | Цитировать Сообщить модератору
 Re: Жутко тупит запрос, при использовании в where переменной  [new]
Konst_One
Member

Откуда:
Сообщений: 11512
да ещё наверняка нет никаких индексов
14 авг 17, 15:15    [20722194]     Ответить | Цитировать Сообщить модератору
 Re: Жутко тупит запрос, при использовании в where переменной  [new]
Shakill
Member

Откуда: мск
Сообщений: 1870
Невский,

ваш случай?
https://stackoverflow.com/questions/27564852/why-is-sql-server-using-index-scan-instead-of-index-seek-when-where-clause-conta
14 авг 17, 15:17    [20722210]     Ответить | Цитировать Сообщить модератору
 Re: Жутко тупит запрос, при использовании в where переменной  [new]
Невский
Member

Откуда:
Сообщений: 30
TaPaK
Невский,

OPTION (RECOMPILE) на запрос и смотрите результат, потом читайте дальше


Запрос с переменными но с OPTION (RECOMPILE) в конце выполняется секундально. Но без этой опции снова тупит. Запустил UPDATE STATISTICS WITH FULLSCAN.
14 авг 17, 15:20    [20722226]     Ответить | Цитировать Сообщить модератору
 Re: Жутко тупит запрос, при использовании в where переменной  [new]
Konst_One
Member

Откуда:
Сообщений: 11512
тогда ссылки , что вам давали - это ваш случай. но полное обновление статистики поможет только временно. опцию recompile придётся всё равно в запрос добавлять
14 авг 17, 15:23    [20722242]     Ответить | Цитировать Сообщить модератору
 Re: Жутко тупит запрос, при использовании в where переменной  [new]
Невский
Member

Откуда:
Сообщений: 30
Владислав Колосов
Невский,

У вас плохая статистика в таблице, по всей видимости. Неравномерное распределение значений даты, например. Можно хинты использовать вроде OPTIMIZE FOR () но это до поры будет работать.


Галочка на автоматическое обновление статистики стоит. Индексы я вообще поставил в еженочный джоб проверять и реорганизовывать если нужно. Три года процедура работала нормально, что сейчас произошло фиг его знает. Поставил обновление статистики на таблице.
14 авг 17, 15:23    [20722243]     Ответить | Цитировать Сообщить модератору
 Re: Жутко тупит запрос, при использовании в where переменной  [new]
Невский
Member

Откуда:
Сообщений: 30
Konst_One
да ещё наверняка нет никаких индексов

Я ведь писал по тексту, что запрос с константой ищет по правильному индексу, а через переменную лезет в левый индекс непонятно почему...
14 авг 17, 15:24    [20722248]     Ответить | Цитировать Сообщить модератору
 Re: Жутко тупит запрос, при использовании в where переменной  [new]
Konst_One
Member

Откуда:
Сообщений: 11512
Невский
Konst_One
да ещё наверняка нет никаких индексов

Я ведь писал по тексту, что запрос с константой ищет по правильному индексу, а через переменную лезет в левый индекс непонятно почему...


сорь, этот коммент пропустил, прочитал только начальный
14 авг 17, 15:25    [20722253]     Ответить | Цитировать Сообщить модератору
 Re: Жутко тупит запрос, при использовании в where переменной  [new]
Невский
Member

Откуда:
Сообщений: 30
Konst_One
тогда ссылки , что вам давали - это ваш случай. но полное обновление статистики поможет только временно. опцию recompile придётся всё равно в запрос добавлять

Если обновление статистики поможет, но на время, то конечно добавлю в запрос опцию recompile. Непонятно из-за чего это вообще произошло. Таблице несколько лет, весит несколько десятков гиг. Всегда все работало без проблем...
14 авг 17, 15:28    [20722279]     Ответить | Цитировать Сообщить модератору
 Re: Жутко тупит запрос, при использовании в where переменной  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7385
Невский,

если таблица большая и флаг трассировки 2371 ( и сервер ниже 2016) для сервера не включен, то галочка автообновления статистики не поможет для больших таблиц.
14 авг 17, 16:11    [20722490]     Ответить | Цитировать Сообщить модератору
 Re: Жутко тупит запрос, при использовании в where переменной  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
я так понимаю все считают что RECOMPILE обновляент/пересчитывает статистику?
14 авг 17, 16:21    [20722523]     Ответить | Цитировать Сообщить модератору
 Re: Жутко тупит запрос, при использовании в where переменной  [new]
iap
Member

Откуда: Москва
Сообщений: 46951
Значение (select MAX(ReportDate) from Bills) часто меняется?
14 авг 17, 16:30    [20722548]     Ответить | Цитировать Сообщить модератору
 Re: Жутко тупит запрос, при использовании в where переменной  [new]
Yuri Abele
Member

Откуда: Латвия> Литва > Тольятти > Wiesbaden > Karlsruhe
Сообщений: 1661
TaPaK
я так понимаю все считают что RECOMPILE обновляент/пересчитывает статистику?

Нет конечно, только Execution Plan обновляет
14 авг 17, 17:21    [20722758]     Ответить | Цитировать Сообщить модератору
 Re: Жутко тупит запрос, при использовании в where переменной  [new]
Невский
Member

Откуда:
Сообщений: 30
iap
Значение (select MAX(ReportDate) from Bills) часто меняется?

Каждый день, если не было сбоев. Собственно переменную в процедуре только для того и использую, чтоб если кто то уронил сервак в мое отсутствие, обновление началось не с "вчерашнего дня", а с даты сбоя.
15 авг 17, 09:32    [20723984]     Ответить | Цитировать Сообщить модератору
 Re: Жутко тупит запрос, при использовании в where переменной  [new]
iap
Member

Откуда: Москва
Сообщений: 46951
Невский
iap
Значение (select MAX(ReportDate) from Bills) часто меняется?

Каждый день, если не было сбоев. Собственно переменную в процедуре только для того и использую, чтоб если кто то уронил сервак в мое отсутствие, обновление началось не с "вчерашнего дня", а с даты сбоя.
Процедура компилируется для текущего значения переменной.
Именно для такого значения сохраняется квазиоптимальный план.
А реально при вызове переменная уже имеет другое значение.
И сохранённый план для неё уже оптимальным не является.

Таким образом, создав процедуру с опцией RECOMPILE, можно добиться построения оптимального плана
для текущего значения переменной при каждом запуске.
15 авг 17, 11:12    [20724409]     Ответить | Цитировать Сообщить модератору
 Re: Жутко тупит запрос, при использовании в where переменной  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
iap,


автор
Таким образом, создав процедуру с опцией RECOMPILE, можно добиться построения оптимального плана
для текущего значения переменной при каждом запуске

проуедура с WITH RECOMPILE не строит план для текущего значения
15 авг 17, 11:24    [20724455]     Ответить | Цитировать Сообщить модератору
 Re: Жутко тупит запрос, при использовании в where переменной  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
в догонку если ТС не врёт
автор
Есть процедура, в которой объявляется переменная типа Date (declare @ReportDate Date). В начале переменной присваивается значение отчетной даты:

то к parameter sniffing это не имеет никакого отношения
Весь вопрос в выборе плана оптимизатором на основании текущих индексов/статистик, исходя из

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

оптимизатор считает это более оптимальным путём при запросе с переменной, как вариант прибить индекс, OPTION(RECOMPILE), OPTIMIZE FOR
или изменить разобраться почему лезет в "не правильный" индекс считая его более удачным
15 авг 17, 11:30    [20724489]     Ответить | Цитировать Сообщить модератору
 Re: Жутко тупит запрос, при использовании в where переменной  [new]
Konst_One
Member

Откуда:
Сообщений: 11512
ну там ещё * в запросах, не могло быть, что таблицы менялись?
15 авг 17, 12:03    [20724668]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3 4   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить