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

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

Во временных таблицах примерно столько данных:
вт_ТаблицаОтбораЛокальная: 1 368 904 записей
вт_Родословная_Номенклатура: 161 806 записей
вт_Родословная_Размещение: 681 записей
вт_Родословная_СтруктурнаяTдиница: 126 записей
втОтборПредковПоЗначениям: 91 записей

Не можем побороть беду. То выполняется за пару секунд, то висит 8 часов. Могут ли быть блокировки или еще что, если запрос соединяет только временные таблицы?

зачем здесь соединение, если не используете его данные. вполне можно использовать "ГДЕ". если уж нужно СОЕДИНЕНИЕ , то попробуйте поставить индексы во временных таблицах.

СГРУППИРОВАТЬ то же не надо, если нет агрегатных функций, можно РАЗЛИЧНЫЕ поставить

Запрос выполняется всегда под одним пользователем, т.е. РЛС одинаковые всегда?
РЛС могут сильно ухудшить запрос.

моет там у вас еще и составные типы... тогда нужно выразить их

(0) Надо понаблюдать на сервере sql за памятью, tempdb и т.д..

(3) переписывал на ГДЕ, эффект до конца непонятен, но суть та же - то быстро, то висит. Временные проиндексированы.
(4) пробовал РАЗЛИЧНЫЕ - нет результата.
(5) да, всегда выполняется под админом. Забыл добавить, что запрос выполняется фоновым заданием.
(7) да вот наблюдаем. В тех журнале 1С в момент выполнения ничего такого не нашел. С сиквелом тоже все вроде норм, 400 гигов озу уже выделили, все на SSD.
То ли блокировка какая, но сложно отловить, то ли еще что. Пробовал план запроса профайлером отловить, но сложно словить момент, когда будет подвисон и в итоге не хватает места профайлеру на хранение логов.

(8) "То ли блокировка какая, но сложно отловить, то ли еще что. Пробовал план запроса профайлером отловить, но сложно словить момент, когда будет подвисон и в итоге не хватает места профайлеру на хранение логов."

Если запрос долго выполняется, то его можно не ловить в профайлере. Он будет в Activity Monitor висет. Там на последних версиях скуля можно даже смотреть как он выполняется в онлайн. Можно держнуть план и сам текст запроса.
У меня стоить джоб на длинные запросы. Если какойто запрос в системе выполняется более 30 минут, мне на почту приходят письма. Я захожу в Activity Monitor и смотрю что за косяк.

(0) Оптимизатор не может определить приемлимый порядок выполнения соединений. Поэтому, например, первое же соедиенение и отдает тебе 1368904 * 161806 записей с пожеланияеми всяческих успехов.
Попробуй переписать на выбрать из выбрать с подобром выборок так, чтобы количество записей постоянно уменьшалось. Ты же не забыл, что даже внутреннее соединение размножает строки результата, если условию соединения удовлетворяет больше одной записи?

Сколько записей в результате получается?

(9) спасибо, будем пробовать ловить дальше.
(6) тут кстати да, поле Размещение составного типа.
(10) сейчас запрос как раз переписан на Выбрать из Выбрать, но результат нет. Про соединение по полям с неуникальными записями думал, но тут не в этом дело.
(11) в результате около 500 000.

1.  проиндексируй и расположи именно в таком порядке поля в первой ВТ вт_ТаблицаОтбораЛокальная
Номенклатура, Размещение, СтруктурнаяЕдиница

2. оставь соединение трех и клади во ВТ а потом ее соединяй с последней таб втОтборПредковПоЗначениям

то есть у тебя именно последнее соединение все портит

(12) >>>тут кстати да, поле Размещение составного типа.

это ЖОПА!!!

от этого в первую очередь избавляйся

(14) Да не жопа это, если не заниматься разыменовыванием, не надо преувеличивать.
По представленному тексту - все ок.
Меня напрягает
вт_ТаблицаОтбораЛокальная: 1 368 904 записей
вт_Родословная_Номенклатура: 161 806 записей
Слишком много строк. Надо смотреть план, скорее всего проблемы там.

(6) Учи матчасть, когда имеет смысл ВЫРАЗИТЬ.
Долеко не всегда от этого есть хоть какой нибудь прок.

а меня напрягает

                        Внутреннее соединение втОтборПредковПоЗначениям КАК втРодители

где в условии нет соединения с основной таблицей вт_ТаблицаОтбораЛокальная

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

(16) по этому полю идет джойн, и при составном поле там обязательно будет запрос к метаданным,

для проверки можно поставить этот джойн последним

(16) Начитаются советов.
А зачем это надо не понимают.
Простой пример:
В регистре есть измерение составного типа. Тип Приходная и расходная накладная.
Умнички переписывают все на выразить. Старательно, все запросы. а отличия нет. Показываешь ему планы запросов, а он не верит и бубни Выразить поможет...
(17) Это полное внешнее соединение. Ну логично, что все долго.
(18) 100500 раз доказывалось - порядок джойнов в запросе ни на что не влияет, за дебя это делает анализатор.

(19) Точнее оптимизатор СУДД

СУБД, тьфу

(19) в регистре составной тип не страшен, в во ВТ страшен

(22) +

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

(18) Да не будет там никакого обращения к метаданным.
Составные поля плохи в двух случаях: при разыменовании - вот тогда построитель запроса 1с по метаданным подпихивает все таблички, и при сравнении на больше/меньше - тогда сравнения превращаются в CASE и привет производительность.
Во всех остальных случаях (а остается только сравнение на равенство, как здесь) все сводится к сравнению не одного, а трех полей, что совершенно не смертельно.

Ты б уж, дядь Дим, с твоим опытом, должен знать. Даже на ИТСе писали.

(13) (17) Поля проиндексированы и расположены именно так. Сейчас обернул эту часть во вложенный запрос, т.к. предполагал, что сиквел может не следовать моему порядку соединения (как вы правильно тоже заметили), но результатов это не принесло. Попробую может через ВТ.

Сейчас запрос выглядит так, но сегодня ночью висел 8 часов, пока я с утра его не кикнул.

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

(19) Забираю слова про полное внутренне назад, внимательно прочитал текст запроса. Там его нет.
Запрос написан корректно.
Единственное что смущает - 2 таблицы с очень большой выборкой

(25) Друг.
ни порядок полей, ни порядок соединений в тексте ничего не меняет. Единственное, что при изменении порядка -> заново запускается процедура поиска плана, и так как статистика могла измениться, план теоретически может стать другим, но в подавляющем большинстве это ничего не меняет.
открой настольную книгу эксперта и почитай. Одни из рекомендаций - не делать временные таблицы большими.
1КК это много
161К это тоже много.
Индексирование тоже не бесплатное и жрет ресурсы и место, имеет смысл при обращении к таблице более чем в 1 запросе, в остальных случаев - выстрел вхолостую.

(26) Тут вопрос с другой стороны - могут ли блокироваться как-то данные, что не дает выполнить запрос либо 100% в запросе/плане?
(27) Про индексирование согласен. Сравнение показывало, что в целом выполняется либо также либо еще дольше.

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

(25) попробуй так, вроде должно быть стабильно по скорости...

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

На всякий случай когда зависнет на долго глянь очередь к дискам на сервере, нормальное значение всегда меньше 1

(29) Так проблема то в чем - как может 1 и тот же запрос ко временным таблицам с одинаковым количеством записей выполняться в разные периоды времени 5 секунд и 8 часов? Причем в 95% случаев он выполняется 5 секунд, а 5% >1 часа. Мне кажется, что проблема не в запросе, а блокировке данных что-ли, т.к. ну не может быть такой разницы в скорости выполнения. Т.е. работает фоновое, зависло на этом запросе. Я его киляю и этот же запрос уже выполняется 2 секунды. Ну как такое может быть?

(27) что такое деградация индекса - слышал? порядок полей внутри условия должен соответсвовать порядку индекса - почитай рекомендации SQL...  

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

https://www.sql.ru/forum/32583/degradaciya-indeksov

последний пост ничего не напоминает :)

(33) oracle 8.1.7.3

(31) "Я его киляю "
Не надо его килять. В этот момент его как раз и надо изучать. Дергать план, сам запрос в формате скуля. Там все ответы.

"Так проблема то в чем - как может 1 и тот же запрос ко временным таблицам с одинаковым количеством записей выполняться в разные периоды времени 5 секунд и 8 часов?"

Это оптимизатор строит разные планы.

(34) на SQL тоже самое, счас поищу

(35) >>>Это оптимизатор строит разные планы.

с этим согласен на 100%

тут вопрос в том почему он их строит разными

(32) Слыхал слыхал.
Но во первых нигде не говориться о записи существенных объемов постоянно.

Потому что как говорять SQLщики, если у Вас ииспользуются временные таблицы - значит у Вас корявая архитектура

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

(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 действительно строил неоптимальный план найти не удалось, т.к. подвисания были в нескольких процентах случаев, а в остальных запрос выполнялся пару секунд. Помог вынос результата части запроса во временные таблицы. Оборачивание в подзапрос несколько улучшало ситуацию (подвисания были реже), но все равно были.