Всем привет. Может кто подскажет почему один и тот же запрос, получающий выборку данных из временных таблиц с одним и тем же количеством записей чаще выполняется около 5 секунд, но иногда зависает на 1-2 часа?

Выбрать
                        втОтбор.Номенклатура,
                        втОтбор.Размещение,
                        втОтбор.СтруктурнаяЕдиница
                    Из
                        вт_ТаблицаОтбораЛокальная как втОтбор
                            ВНУТРЕННЕЕ СОЕДИНЕНИЕ вт_Родословная_Номенклатура КАК вт_Родословная_Номенклатура
                                ПО вт_Родословная_Номенклатура.ссылка = втОтбор.Номенклатура
                            ВНУТРЕННЕЕ СОЕДИНЕНИЕ вт_Родословная_Размещение КАК вт_Родословная_Размещение
                                ПО вт_Родословная_Размещение.ссылка = втОтбор.Размещение
                            ВНУТРЕННЕЕ СОЕДИНЕНИЕ вт_Родословная_СтруктурнаяЕдиница КАК вт_Родословная_СтруктурнаяЕдиница
                                ПО вт_Родословная_СтруктурнаяЕдиница.ссылка = втОтбор.СтруктурнаяЕдиница
                        Внутреннее соединение втОтборПредковПоЗначениям КАК втРодители
                            По вт_Родословная_Номенклатура.Предок = втРодители.НоменклатураПредок
                                И вт_Родословная_Размещение.Предок = втРодители.РазмещениеПредок
                                И вт_Родословная_СтруктурнаяЕдиница.Предок = втРодители.Структурная_единицаПредок
                    Сгруппировать по
                        втОтбор.Номенклатура,
                        втОтбор.Размещение,
                        втОтбор.СтруктурнаяЕдиница

И какая нахрен деградация индексов?
По теме - с выборкой и помещением в Вт проблем нет.

(35) (37) спасибо. Тогда нужно только выловить планы и убедиться в этом.

(41) скажу так, из своей достаточно богатой практики. Единственное стабильное решение это использовать промежуточные объекты БД, я делал очень похожее, вспомогательный РС, заполняемый в подписке ускорил всё на порядки.
Все остальное давало только временный эффект.

Это задача из серии вычислить остатки на каждый день.
Да можно написать запрос.
Он первое время будет работать изумительно.
А потом в один прекрасный или не очень день все сломается.
Потом это оптимизируется и ещё какое то время работает и снова ломается.
То что, используются большие ВТ это уже признак того, что решение не стабильно. Сегодня 1кк, завтра 10кк и снова тема на форуме?)

(43) про архитектуру и логику это тема для отдельной темы :) Ибо этот запрос из блока функционала, в котором используется МВТ с десятками таблиц, кочующий по разным процедурам...

(43) >>>То что, используются большие ВТ это уже признак того, что решение не стабильно

+100

Вообще ВТ это не панацея, не надо ее пихать везде... Единственное бесспорное условие необходимости ВТ - это многократное ее использование, все остальное под вопросом :)

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

(45) Норм работает в жизни, если грамотно спроектировать.
Вот изменения уже потом идут через боль.

О блин, пока тут треплюсь уже на 15 минут повис запрос.

(47) так сразу смотри очередь к диску и план запроса копируй в скриншоты

(46) Скажу так за 5 лет по 100 000 позиций (отраслевому справочника аналогичному номенклатуре по сути), вполне себе бодро шуршит, я 1.5 года там не работаю, но на сколько мне известно до сих пор летает.
Другое дело, что в первой редакции мы кое что не учли и пришлось делать схожий регистр но с другим полем.
И по факту 2 РС с остатками. Да база пухнет, но заказчик просто рыдал от счастья, когда вместо минут все стало работать менее секунды.

(44) Могу только посочувствовать

В активити мониторе есть этот запрос, но план не отображается.
Я еще наверное не сказал, что результат запросе также помещается во временную таблицу.

INSERT INTO #tt283 WITH(TABLOCK) (_Q_000_F_000RRef, _Q_000_F_001_TYPE, _Q_000_F_001_RTRef, _Q_000_F_001_RRRef, _Q_000_F_002RRef) SELECT DISTINCT
T1.Q_001_F_000RRef,
T1.Q_001_F_002_TYPE,
T1.Q_001_F_002_RTRef,
T1.Q_001_F_002_RRRef,
T1.Q_001_F_004RRef
FROM (SELECT
T2._Q_000_F_000RRef AS Q_001_F_000RRef,
T3._Q_000_F_001RRef AS Q_001_F_001RRef,
T2._Q_000_F_001_TYPE AS Q_001_F_002_TYPE,
T2._Q_000_F_001_RTRef AS Q_001_F_002_RTRef,
T2._Q_000_F_001_RRRef AS Q_001_F_002_RRRef,
T4._Q_000_F_001_TYPE AS Q_001_F_003_TYPE,
T4._Q_000_F_001_RTRef AS Q_001_F_003_RTRef,
T4._Q_000_F_001_RRRef AS Q_001_F_003_RRRef,
T2._Q_000_F_002RRef AS Q_001_F_004RRef,
T5._Q_000_F_001RRef AS Q_001_F_005RRef
FROM #tt47 T2 WITH(NOLOCK)
INNER JOIN #tt92 T3 WITH(NOLOCK)
ON (T3._Q_000_F_000RRef = T2._Q_000_F_000RRef)
INNER JOIN #tt90 T4 WITH(NOLOCK)
ON (T4._Q_000_F_000_TYPE = T2._Q_000_F_001_TYPE AND T4._Q_000_F_000_RTRef = T2._Q_000_F_001_RTRef AND T4._Q_000_F_000_RRRef = T2._Q_000_F_001_RRRef)
INNER JOIN #tt84 T5 WITH(NOLOCK)
ON (T5._Q_000_F_000RRef = T2._Q_000_F_002RRef)) T1
INNER JOIN #tt282 T6 WITH(NOLOCK)
ON (((CASE WHEN T1.Q_001_F_001RRef IS NOT NULL THEN 0x08 END = T6._Q_000_F_000_TYPE AND CASE WHEN T1.Q_001_F_001RRef IS NOT NULL THEN 0x000000D6 END = T6._Q_000_F_000_RTRef AND T1.Q_001_F_001RRef = T6._Q_000_F_000_RRRef) AND (T1.Q_001_F_003_TYPE = T6._Q_000_F_001_TYPE AND T1.Q_001_F_003_RTRef = T6._Q_000_F_001_RTRef AND T1.Q_001_F_003_RRRef = T6._Q_000_F_001_RRRef)) AND (CASE WHEN T1.Q_001_F_005RRef IS NOT NULL THEN 0x08 END = T6._Q_000_F_002_TYPE AND CASE WHEN T1.Q_001_F_005RRef IS NOT NULL THEN 0x00000125 END = T6._Q_000_F_002_RTRef AND T1.Q_001_F_005RRef = T6._Q_000_F_002_RRRef))

Винт загружен, то там бэкап делается и пересчет статистики.
https://b.radikal.ru/b23/1905/a9/b396ee06e8c2.png

(51) Это не план, а текст запроса

И очередь к диску больше 1

(51) причина тормозов - очередь к диску D:,

причина очереди - надо смотреть...

ну и кстати пересчет статистики во время тяжолого запроса - не айс точно

(54) Причина - скидывание всякого из ОЗУ на HDD, а потом чтение

1КК записей уже нормально так весят

обращаю внимание, второй джойн идет с условием AND, это скорее всего из-за составного типа.
это условие идет скорее всего мимо индекса....

(57) AND это не OR и условие равенство

(55) правильно, сваливание в своп, о чем я и писал ранее, возможно нужно ограничить SQL выделяемой памятью (но тут смотреть надо, это сейчас не понацея а только один из вариантов). По тому как это не SQL делает а операционка..

(58) все равно он в индекс не попадет...

(60) А не факт что у этой ВТ в принципе есть индекс.

А если есть, то его создание и хранение - это еще больше свопов

Очередь к диску максимум 2,5 - чуть больше нормы. Так что не клевещите на диск.

Для начала: сделай отбор по каждой табличке во временную (ну может кроме той к которой все присоединяете) и только потом соединение.

И да, как тут отметили - базы требуют регулярного обслуживания!

(61) без индексов будет ЖП. SQL оптимизируя запросы с ВТ сам иногда добавляет не кластерезованные внутрение идексы.

(55) миллион записей скидывается на диск? С той структурой что есть они много мегабайт займут? за 8 часов можно сделалть даже на 5 дюймовые флопы 100500 копий такого свопа

+ (63) если 8.3.Х.Х - неплохо сделать для временных таблиц по полям соединения индексы.

А план не могу посмотреть потому что запрос не выполнился?
https://c.radikal.ru/c00/1905/98/f7ca7da686fa.png

(67) а из ТЖ?

и да, сделай как написал в (63) + (66)

+(68) сейчас ухожу по делам, но если есть проблема в чтении текстового плана запроса - по возвращению могу посмотреть

(65) Много мегабайт займут. А если еще постоянное чтение благодоря Nested loops то и по времени очень затратно
(68) (66) там по моему индекс во временных стал кластерным не сначала, а совсем относительно недвано.

(70) я, млин, не знаю где его посмотреть. В ТЖ ничего нет, хотя включена фиксация всего.

<plansql/>

Пример ТЖ со сбором плланов
<config xmlns="http://v8.1c.ru/v8/tech-log">;
 <dump location="C:\1C_Info\Dumps" create="1" type="2"/>
 <plansql/>
 <log location="C:\1C_Info\Logs" history="1">
  <event>
   <eq property="name" value=" DBPOSTGRS"/>
   <ge property="duration" value="100000"/>
  </event>
  <property name="all"/>
 </log>
</config>

(73) ВЫ его с ТЖ сейчас совсем прибьете :)

(72) на запросе ПКМ и показать план выполнения, если он нам не показывается, то или уже завершен и удален или еще не сформирован

(74) тьфу, затупил. Фиксировались только EXCP.
(75) план не показывает, выше ссылку на скрин приложил. Запрос висит, но плана нет.

(75) +

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

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

(77) сейчас выключил все job'ы - запрос как висел так и висит.

https://docs.microsoft.com/ru-ru/sql/relational-databases/performance/display-the-estimated-execution-plan?view=sql-server-2017

https://ausevich.ru/subd/metody-polucheniya-plana-zaprosa-v-subd-ms-sql-server/

(81) спасибо, но предполагаемый план запроса к временным таблицам, созданным в другом соединении не получить. Нужно дождаться выполнения запроса, который уже выполняется 1.5 часа.

(82) Есть возможность выложить базу на онлайн диск?

(83) нет, к сожалению. База уже 700 Гб.
Перезапустил фоновое - этот же запрос выполнился за 3 секунды.

(84) для начала сделай как написано в (69) а там по результату

(85) Вынес вложенный запрос в отдельную временную таблицу. Итого пока стабильно выполняется 3 минуты. Это, конечно, медленнее 5 секунд, но быстрее 8 часов ) Итого если будет стабильно выполняться и дальше, то скорость устроит. Поля, по которым соединяется, вроде как проиндексированы.

(86) А, так это еще и вложенный запрос был?)

(86) 3 минуты - это жесть конечно

(86) https://youtu.be/9mvm0TlHO4s

(88) у него может ещё и базы не дообслуживались. У него и журнал и база на 1 диске и хз сколько там чего и как с базой в это время крутит-вертит. Короче трэш, содом и гоморра и лечить нужно по шагам, имхо.

(87) я в самом вначале прикладывал 1 запрос просто с соединениями, потом переделанный с подзапросом (оба выполнялись примерно одинаково). Сейчас попробовал вынести подзапрос в ВТ - пока мониторю.
(88) а что тут такого, если это не бухия на 3 буха, а база 700 ГБ, в спр. Номенклатура 500 тысяч элементов и РИБ > 20 узлов? :)

(86) если ты пишешь во временные таблицы то это твоя обязанность создать индексы для них а не вроде хз кого. Но делать замер времени т.к. как ни странно может только ухудшить (такое бывает).

Из (51) видно, что составные поля есть... там точно к 1 типу не сводится? Но это делать вторым шагом когда закончишь с индексами.

(91) Номенклатура 500 тысяч элементов это не так много, в крупных СЦ в год больше миллиона новой номенклатуры вводится и 20 узлов РИБ тоже, они на запрос прямо никак не влияют.
А из 700 ГБ 650 могут оказаться прикрепленными файлами

(91) 700 ничё, но если структура продумана, то не на много хуже чем 200 например... у тебя-же наверняка старые записи прошлых лет в первую очередь?
500 тыс и 20+ узлов? у меня есть базы и покруче чем 500 тыс и 20+филиалов и онлайн. Тут не в том вопрос.


Стоп!!! у тебя 700Гб и журнал и база на 1 диске? Что за жпо? и для темпов нет своего ССД? А бэкапы не на том-же диске хранишь?

(91) У тебя в результат запроса выводится 500 кзаписей? Если нет - то это - треш.

typical day of 700 Gb database.
Haha, classic
http://itd0.mycdn.me/image?id=868876644159&t=20&plc=WEB&tkn=*gqqXZFgKRgR_80NlfufisFRXSRg

(93) Про 650 ГБ файлов из 700 ГБ - это классная шутка :) Но к сожалению все 700 ГБ - это гребаные нужные данные :(
(94) К сожалению все на 1 физическом диске/массиве - и журнал и база и tempdb. Бэкапы на внешний nas делаются.

(95) Да, около того.

Автор, ты хоть план запроса покажи.
Это вот тут
http://prntscr.com/nnk1qp

(99)тоже любопытно?

(100) Ставлю на mdop=0

Ну или на tablespool

(101) нууу, ну вряд-ли тут параллельность так всё ломает.
(102) это вероятнее, но хз, хотя 1 диск на всё...

Автор, а там диск или хотя-бы какой рэйд-массив?

(99) уже отловил.
План:
https://a.radikal.ru/a10/1905/b5/216b5d3d91b7.png


Запрос:

INSERT INTO #tt246 WITH(TABLOCK) (_Q_000_F_000RRef, _Q_000_F_001RRef, _Q_000_F_002_TYPE, _Q_000_F_002_RTRef, _Q_000_F_002_RRRef, _Q_000_F_003_TYPE, _Q_000_F_003_RTRef, _Q_000_F_003_RRRef, _Q_000_F_004RRef, _Q_000_F_005RRef) SELECT DISTINCT
T1._Q_000_F_000RRef,
T2._Q_000_F_001RRef,
T1._Q_000_F_001_TYPE,
T1._Q_000_F_001_RTRef,
T1._Q_000_F_001_RRRef,
T3._Q_000_F_001_TYPE,
T3._Q_000_F_001_RTRef,
T3._Q_000_F_001_RRRef,
T1._Q_000_F_002RRef,
T4._Q_000_F_001RRef
FROM #tt8 T1 WITH(NOLOCK)
INNER JOIN #tt55 T2 WITH(NOLOCK)
ON (T2._Q_000_F_000RRef = T1._Q_000_F_000RRef)
INNER JOIN #tt53 T3 WITH(NOLOCK)
ON (T3._Q_000_F_000_TYPE = T1._Q_000_F_001_TYPE AND T3._Q_000_F_000_RTRef = T1._Q_000_F_001_RTRef AND T3._Q_000_F_000_RRRef = T1._Q_000_F_001_RRRef)
INNER JOIN #tt47 T4 WITH(NOLOCK)
ON (T4._Q_000_F_000RRef = T1._Q_000_F_002RRef)

+ (103) скорее всего классика: старая статистика, большое расхождение между прогнозом и фактом и соответственно не тот способ соединения

(104) и есть 100500 способов его выполнения (ака планов выполнения этого самого запроса)

(104) mdop=0

http://prntscr.com/nnk67v

Max Degree of Parallelism = 0

(109) Ставь =1, перезагружай SQL (но не уверен, что необходимо) и пробуй заново

(30) "а вообще засады в 8 часов с запросом - это обычно не чисто софтовая проблема, чаще всего это проблема ухода ОЗУ в своп"
С временными таблицами размером в единицы мегабайт? Какой тут своп. Тут нестедлуп в чистом виде. И бороться надо именно с ним.

(110) при смене паралелизма требуется перезапуск службы

из (104) видно, что довольно много времени занимает сортировка, а всего плана и не видно....

(112) Не надо. Вот только что попробовал у себя.

(113) Вставка в ВТ там половину времени.

Ай, короче забейте пока. Этот запрос возвращает 85 млн строк :)

(116) зачем тебе столько?

(91) имено это я и предлогал....

заодно в вынесеном запросе второй джойн поставь третьим (где составной тип).

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

основная + первый ждойн
основная + второй джойн
основная + третий джойн

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

и еще
в SQL есть понятие что-то вроде плотности индекса (как точно не помню), в краце это некое отношение показывающее эффективность выборки, короче опиши для тех полей по которым джойнится сколько в среднем одинаковых значений в полях которые индексируешь, и примерное соотношение соеднинения

например на 1000 записей основной таблицы есть 10 записей вспомогательной, при этом соответсвий всего 2 (два уникальных значения, но записей отбираем 10), при этом мы должны понимать, что в результат попадет не 10 записей а немного больше :), собственно я скорее ставку в сабже сделаю именно на этот вариант..... (так сказать неявное умножение)

(116) судя по всему я в (118) прав :)

а стабильность в 3 минуты ты получил по тому, что ВТ неяным образом удаляет дубли, то есть потратил время на удаление из этих 67 лямов почти всего :)

(109) МЛЯ!!!!

кстати вопрос Дмитрию:

при создании ВТ удаляются дубли или происходит свертка?

(122) только если создаешь с гроупбай или с безповторяющихся. просто так только кошки....

+ (121) настраивавшему сервер по рукам линейкой... металлической!

+ (123) хотя у Димы может другая версия

(122) Ниче не удаляется и не свертывается, что отправилось в ВТ, то и будет в ВТ.

(124) Я пока не видел ни одного клиента, у которого был бы настроен SQL полностью по заветам ИТС.

(127) у меня настроено... кое что не гуманно к лиц, но чуть-чуть, но там по другому никак.... ибо что за хрень местами в 1С (например покури внешние источники если субд не сиквел)

по поводу составных типов:

мы видели, что составной тип в сабже был оттранслирован в условие с несколькими или, а теперь читаем ИТС

https://its.1c.ru/db/content/metod8dev/src/developers/scalability/standards/i8105842.htm?_=1557240526#or

Использование логического ИЛИ в условиях
Рекомендации
Использование логического ИЛИ в секции ГДЕ запроса
Не следует использовать ИЛИ в секции ГДЕ запроса. Это может привести к тому, что СУБД не сможет использовать индексы таблиц и будет выполнять сканирование, что увеличит время работы запроса и вероянтность возникновения блокировок. Вместо этого следует разбить один запрос на несколько и объединить результаты.

Например, запрос

ВЫБРАТЬ Товар.Наименование ИЗ Справочник.Товары КАК Товар ГДЕ Артикул = "001" ИЛИ Артикул = "002"

следует заменить на запрос

ВЫБРАТЬ Товар.Наименование ИЗ Справочник.Товары КАК Товар ГДЕ Артикул = "001"
|ОБЪЕДИНИТЬ ВСЕ
|ВЫБРАТЬ Товар.Наименование ИЗ Справочник.Товары КАК Товар ГДЕ Артикул = "002"

(129) спс, с этим сталкивался лично. Разница в скорости выполнения отличалась в разы.

(32) Ты серьезно???
https://docs.microsoft.com/ru-ru/sql/t-sql/queries/search-condition-transact-sql?view=sql-server-2017
"Порядок выполнения логических операторов может меняться в зависимости от настроек оптимизатора запросов."

(131) Он пытается рассказать о двух вещах сразу - о селективности индекса в зависимости от порядка его полей и о покрывающих индексах. Ну, как может (:

Вот, нашел где важен порядок. Table hint INDEX(). "The order of the indexes in the index hint is significant." (https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-2017)

(133) из 1С нет возможности передать подсказку оптимизатору.

(129) Хороший пример, когда разбивать на несколько запросов по ИЛИ бессмысленно.

(134) Это лишь ответ на странную фразу vde69 про порядок условий в секции WHERE.

(135) зачем тогда этот пример написан в ИТС?

(137) Затем, что это всего лишь пример. Когда структура отбора будет чуть сложней, чем (Артикул = "001" ИЛИ Артикул = "002"), тогда UNION ALL может очень пригодиться. Например, если у тебя там составное поле из нескольких документов, миллион строк и разброс данных очень значительный, а надо найди десяток документов двух видов. Через "ИЛИ" индекс скан будет длиться 60 секунд, а index seek + UNION ALL - 2 секунды.

В общем решил проблему переписыванием запроса.
Доказательств, к сожалению, что SQL Server действительно строил неоптимальный план найти не удалось, т.к. подвисания были в нескольких процентах случаев, а в остальных запрос выполнялся пару секунд. Помог вынос результата части запроса во временные таблицы. Оборачивание в подзапрос несколько улучшало ситуацию (подвисания были реже), но все равно были.