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

Откуда:
Сообщений: 6
Доброе время суток.

В последнее время ряд запросов в Management Studio перестал выполняться. Речь идет о запросах с перечислением большого количества значений в in ( ... )

Пример:

select * from clientdata where dtdelete is null and regno in ( ... ) order by regno asc
- выдает ошибку "Сообщение 8632, уровень 17, состояние 2, строка 1 - Внутренняя ошибка: был достигнут предел служб выражений. Проверьте потенциально сложные выражения в запросе и постарайтесь их упростить."

select c.regno, convert(varchar, s.dtopen, 104) from ClientData c join ClientScheta s on c.FID=s.posc where c.dtdelete is null and c.regno in ( ... ) order by regno asc
- выдает ошибку "Сообщение 8623, уровень 16, состояние 1, строка 1 - Обработчик запросов исчерпал внутренние ресурсы, и ему не удалось предоставить план запроса. Это редкое событие, которое может происходить только при очень сложных запросах или запросах, которые обращаются к очень большому числу таблиц или секций. Упростите запрос. Если предполагается, что это сообщение получено по ошибке, свяжитесь со службой поддержки для получения дополнительных сведений."

Раньше подобные запросы выполнялись нормально для 100 000+ значений в in, а сейчас не хотят даже с ~60 000... Предела оперативной памяти в момент выполнения запроса сервер не достигает. С чем может быть связано такое ухудшение производительности и как решить данную проблему? Версия SQL - Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
1 июл 15, 11:54    [17836853]     Ответить | Цитировать Сообщить модератору
 Re: Ухудшение производительности запросов с in ( .. )  [new]
Glory
Member

Откуда:
Сообщений: 104764
PROrabbit
С чем может быть связано такое ухудшение производительности

С тем, что в хелпе заранее предупредили

Including an extremely large number of values (many thousands) in an IN clause can consume resources and return errors 8623 or 8632. To work around this problem, store the items in the IN list in a table.
1 июл 15, 11:57    [17836871]     Ответить | Цитировать Сообщить модератору
 Re: Ухудшение производительности запросов с in ( .. )  [new]
Добрый Э - Эх
Guest
IN-список откуда и как формируется? что мешает засунуть его в таблицу (табличную переменную, врменную таблицу) и сделать JOIN вместо IN ?
1 июл 15, 11:57    [17836877]     Ответить | Цитировать Сообщить модератору
 Re: Ухудшение производительности запросов с in ( .. )  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 19271
PROrabbit
100 000+ значений в in
Силён... а во временную таблицу их скинуть не хочешь?
1 июл 15, 11:57    [17836880]     Ответить | Цитировать Сообщить модератору
 Re: Ухудшение производительности запросов с in ( .. )  [new]
iap
Member

Откуда: Москва
Сообщений: 46833
Добрый Э - Эх
IN-список откуда и как формируется? что мешает засунуть его в таблицу (табличную переменную, врменную таблицу) и сделать JOIN вместо IN ?
Зачем JOIN?
IN, но только IN(SELECT....)
1 июл 15, 11:59    [17836897]     Ответить | Цитировать Сообщить модератору
 Re: Ухудшение производительности запросов с in ( .. )  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 28996
PROrabbit
С чем может быть связано такое ухудшение производительности и как решить данную проблему? Версия SQL - Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
Связано с изменением оптимизатора, компилятора.

Решить можно переписыванием запроса. Например, вставлять эти значения во временную таблицу. Вряд ли эти "100 000+ значений в in" набирались руками, так что наверное несложно переписать.
1 июл 15, 12:06    [17836951]     Ответить | Цитировать Сообщить модератору
 Re: Ухудшение производительности запросов с in ( .. )  [new]
Добрый Э - Эх
Guest
iap,

не любли IN-subquery ещё с тех пор, когда оптимизатор сам не умел их "разворачивать" в джойны.
1 июл 15, 12:11    [17836993]     Ответить | Цитировать Сообщить модератору
 Re: Ухудшение производительности запросов с in ( .. )  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 6697
+
Заставляет задуматься о соответствии программиста его занимаемой должности...
1 июл 15, 12:11    [17837002]     Ответить | Цитировать Сообщить модератору
 Re: Ухудшение производительности запросов с in ( .. )  [new]
iap
Member

Откуда: Москва
Сообщений: 46833
Добрый Э - Эх
iap,

не любли IN-subquery ещё с тех пор, когда оптимизатор сам не умел их "разворачивать" в джойны.
В списке может оказаться не по одному экземпляру некоторых значений...
1 июл 15, 12:13    [17837013]     Ответить | Цитировать Сообщить модератору
 Re: Ухудшение производительности запросов с in ( .. )  [new]
Добрый Э - Эх
Guest
iap,


distinct никто не отменял. как на список перед вставкой в таблицу, так и при джойне на селект из таблицы-списка или на результат всего джойна.
1 июл 15, 12:27    [17837113]     Ответить | Цитировать Сообщить модератору
 Re: Ухудшение производительности запросов с in ( .. )  [new]
iap
Member

Откуда: Москва
Сообщений: 46833
Добрый Э - Эх
iap,


distinct никто не отменял. как на список перед вставкой в таблицу, так и при джойне на селект из таблицы-списка или на результат всего джойна.
А чем это лучше?
DISTINCT - гарантия торможения запроса
1 июл 15, 12:39    [17837193]     Ответить | Цитировать Сообщить модератору
 Re: Ухудшение производительности запросов с in ( .. )  [new]
iap
Member

Откуда: Москва
Сообщений: 46833
Вообще, IN() я тоже не люблю.
Всегда пишу EXISTS()
1 июл 15, 12:40    [17837199]     Ответить | Цитировать Сообщить модератору
 Re: Ухудшение производительности запросов с in ( .. )  [new]
PROrabbit
Member

Откуда:
Сообщений: 6
Благодарю за информацию. Со временными таблицами логично, так в итоге и сделал. Хотел просто понять, почему то, что раньше выполнялось без проблем, выполняться вдруг перестало.

Владислав Колосов
Заставляет задуматься о соответствии программиста его занимаемой должности...

Никогда такой должности не занимал и не планирую.)
1 июл 15, 12:58    [17837353]     Ответить | Цитировать Сообщить модератору
 Re: Ухудшение производительности запросов с in ( .. )  [new]
Glory
Member

Откуда:
Сообщений: 104764
PROrabbit
Хотел просто понять, почему то, что раньше выполнялось без проблем, выполняться вдруг перестало.

Потому что "Including an extremely large number of values "
1 июл 15, 13:00    [17837384]     Ответить | Цитировать Сообщить модератору
 Re: Ухудшение производительности запросов с in ( .. )  [new]
PROrabbit
Member

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

Раньше точно такой же запрос с еще большим "number of values" выполнялся без проблем.
1 июл 15, 13:04    [17837417]     Ответить | Цитировать Сообщить модератору
 Re: Ухудшение производительности запросов с in ( .. )  [new]
Glory
Member

Откуда:
Сообщений: 104764
PROrabbit
Glory,

Раньше точно такой же запрос с еще большим "number of values" выполнялся без проблем.

Ну так вернитесь во времени назад
1 июл 15, 13:05    [17837429]     Ответить | Цитировать Сообщить модератору
 Re: Ухудшение производительности запросов с in ( .. )  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 19271
PROrabbit
Раньше точно такой же запрос с еще большим "number of values" выполнялся без проблем.

У меня тут давеча соседка померла. До того 75 лет прожила, и ни разу с ней раньше такой фигни не случалось...
1 июл 15, 13:10    [17837467]     Ответить | Цитировать Сообщить модератору
 Re: Ухудшение производительности запросов с in ( .. )  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 3945
PROrabbit
Glory,

Раньше точно такой же запрос с еще большим "number of values" выполнялся без проблем.


Я еще в 2006-2007 году делал оптимизации, где подобные IN валились по ошибке и приходилось, если число ID-шнишников выше разумной величины, класть их во временную таблицу и оттуда IN (SELECT ID FROM #T). И валилось это дело уже где-то после 500-1000 значений. Там скрипт длинный собирался динамически.

Если вам надо их непременно вставить в скрипт, то вставляйте порциями через VALUES по 50-100 шт, потом на эту таблицу In (а лучше JOIN). Таким способом можно больше передать в запросе. И об индексе надо подумать.

Но, ИХМО, что-то у вас не так архитектурно.
1 июл 15, 13:10    [17837472]     Ответить | Цитировать Сообщить модератору
 Re: Ухудшение производительности запросов с in ( .. )  [new]
PROrabbit
Member

Откуда:
Сообщений: 6
Akina
PROrabbit
Раньше точно такой же запрос с еще большим "number of values" выполнялся без проблем.

У меня тут давеча соседка померла. До того 75 лет прожила, и ни разу с ней раньше такой фигни не случалось...

Если померла, значит были на то причины (болезнь, критический износ комплектующих). У нашего сервера подобных проблем не наблюдается.
1 июл 15, 13:32    [17837709]     Ответить | Цитировать Сообщить модератору
 Re: Ухудшение производительности запросов с in ( .. )  [new]
Glory
Member

Откуда:
Сообщений: 104764
PROrabbit
У нашего сервера подобных проблем не наблюдается.

Вам еще раз процитировать хелп ?
Including an extremely large number of values (many thousands) in an IN clause can consume resources and return errors 8623 or 8632. To work around this problem, store the items in the IN list in a table.

У вас есть many thousands, как вы думате ?
1 июл 15, 13:34    [17837728]     Ответить | Цитировать Сообщить модератору
 Re: Ухудшение производительности запросов с in ( .. )  [new]
PROrabbit
Member

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

Я читал и хелп и множество других вещей на этот счет. Суть проблемы заключается в том, что при визуально идентичных ситуациях имеется разный результат. При неизменной структуре БД и 100% идентичных запросах месяц назад мы имели успешную отработку, а сегодня ошибку.
1 июл 15, 13:40    [17837791]     Ответить | Цитировать Сообщить модератору
 Re: Ухудшение производительности запросов с in ( .. )  [new]
Glory
Member

Откуда:
Сообщений: 104764
PROrabbit
Я читал и хелп и множество других вещей на этот счет. Суть проблемы заключается в том, что при визуально идентичных ситуациях имеется разный результат. При неизменной структуре БД и 100% идентичных запросах месяц назад мы имели успешную отработку, а сегодня ошибку.

Ваши рассуждения из серии - я всегда здесь переходил дорогу, а сегодня меня тут сбила машина.
Сервер использовал свое право "can consume resources and return errors 8623 or 8632"
Какая еще суть вам нужна ?
1 июл 15, 13:42    [17837801]     Ответить | Цитировать Сообщить модератору
 Re: Ухудшение производительности запросов с in ( .. )  [new]
PROrabbit
Member

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

Если возникла проблема, стоит разобраться почему именно. Если сервер вдруг "воспользовался правом", значит у него на это были причины. Можно сказать, что на это "воля господня" и не разбираться в проблеме, а можно попытаться найти моменты, которые изменились, будь то процент выделения процессорного времени, версия обновления сервера, изменение конфигурации и т.д... Вот я и хочу понять, в какую сторону копать.
1 июл 15, 13:57    [17837940]     Ответить | Цитировать Сообщить модератору
 Re: Ухудшение производительности запросов с in ( .. )  [new]
Glory
Member

Откуда:
Сообщений: 104764
PROrabbit
Если возникла проблема, стоит разобраться почему именно.

Разумеется. Причина - это large number of values.

PROrabbit
Если сервер вдруг "воспользовался правом", значит у него на это были причины.

Вы можете начать поиск этих причин.
Только зачем, если запрос все равно придется перписать ?
1 июл 15, 14:01    [17837973]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Ухудшение производительности запросов с in ( .. )  [new]
yourij_mw
Member

Откуда:
Сообщений: 193
TRACEFLAG 8780 - дает дополнительное время на поиск плана.

мне помогло!!
5 сен 19, 14:44    [21964616]     Ответить | Цитировать Сообщить модератору
 Re: Ухудшение производительности запросов с in ( .. )  [new]
iap
Member

Откуда: Москва
Сообщений: 46833
yourij_mw
TRACEFLAG 8780 - дает дополнительное время на поиск плана.

мне помогло!!
Костыль? Ай-яй-яй!
5 сен 19, 16:23    [21964751]     Ответить | Цитировать Сообщить модератору
 Re: Ухудшение производительности запросов с in ( .. )  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2291
yourij_mw
TRACEFLAG 8780 - дает дополнительное время на поиск плана.

мне помогло!!
убивал бы....
5 сен 19, 22:32    [21964972]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: 1 2      [все]
Все форумы / Microsoft SQL Server Ответить