Имя: Пароль:
1C
1С v8
Проблема со скоростью выполнения запроса
0 Tester
 
03.05.19
16:34
Всем привет. Может кто подскажет почему один и тот же запрос, получающий выборку данных из временных таблиц с одним и тем же количеством записей чаще выполняется около 5 секунд, но иногда зависает на 1-2 часа?

Выбрать
                        втОтбор.Номенклатура,
                        втОтбор.Размещение,
                        втОтбор.СтруктурнаяЕдиница
                    Из
                        вт_ТаблицаОтбораЛокальная как втОтбор
                            ВНУТРЕННЕЕ СОЕДИНЕНИЕ вт_Родословная_Номенклатура КАК вт_Родословная_Номенклатура
                                ПО вт_Родословная_Номенклатура.ссылка = втОтбор.Номенклатура
                            ВНУТРЕННЕЕ СОЕДИНЕНИЕ вт_Родословная_Размещение КАК вт_Родословная_Размещение
                                ПО вт_Родословная_Размещение.ссылка = втОтбор.Размещение
                            ВНУТРЕННЕЕ СОЕДИНЕНИЕ вт_Родословная_СтруктурнаяЕдиница КАК вт_Родословная_СтруктурнаяЕдиница
                                ПО вт_Родословная_СтруктурнаяЕдиница.ссылка = втОтбор.СтруктурнаяЕдиница
                        Внутреннее соединение втОтборПредковПоЗначениям КАК втРодители
                            По вт_Родословная_Номенклатура.Предок = втРодители.НоменклатураПредок
                                И вт_Родословная_Размещение.Предок = втРодители.РазмещениеПредок
                                И вт_Родословная_СтруктурнаяЕдиница.Предок = втРодители.Структурная_единицаПредок
                    Сгруппировать по
                        втОтбор.Номенклатура,
                        втОтбор.Размещение,
                        втОтбор.СтруктурнаяЕдиница
1 Tester
 
03.05.19
16:43
Во временных таблицах примерно столько данных:
вт_ТаблицаОтбораЛокальная: 1 368 904 записей
вт_Родословная_Номенклатура: 161 806 записей
вт_Родословная_Размещение: 681 записей
вт_Родословная_СтруктурнаяTдиница: 126 записей
втОтборПредковПоЗначениям: 91 записей
2 Tester
 
12.05.19
12:11
Не можем побороть беду. То выполняется за пару секунд, то висит 8 часов. Могут ли быть блокировки или еще что, если запрос соединяет только временные таблицы?
3 organizm
 
12.05.19
12:21
зачем здесь соединение, если не используете его данные. вполне можно использовать "ГДЕ". если уж нужно СОЕДИНЕНИЕ , то попробуйте поставить индексы во временных таблицах.
4 organizm
 
12.05.19
12:22
СГРУППИРОВАТЬ то же не надо, если нет агрегатных функций, можно РАЗЛИЧНЫЕ поставить
5 leonidkorolev
 
12.05.19
12:23
Запрос выполняется всегда под одним пользователем, т.е. РЛС одинаковые всегда?
РЛС могут сильно ухудшить запрос.
6 organizm
 
12.05.19
12:23
моет там у вас еще и составные типы... тогда нужно выразить их
7 d4rkmesa
 
12.05.19
12:27
(0) Надо понаблюдать на сервере sql за памятью, tempdb и т.д..
8 Tester
 
12.05.19
12:32
(3) переписывал на ГДЕ, эффект до конца непонятен, но суть та же - то быстро, то висит. Временные проиндексированы.
(4) пробовал РАЗЛИЧНЫЕ - нет результата.
(5) да, всегда выполняется под админом. Забыл добавить, что запрос выполняется фоновым заданием.
(7) да вот наблюдаем. В тех журнале 1С в момент выполнения ничего такого не нашел. С сиквелом тоже все вроде норм, 400 гигов озу уже выделили, все на SSD.
То ли блокировка какая, но сложно отловить, то ли еще что. Пробовал план запроса профайлером отловить, но сложно словить момент, когда будет подвисон и в итоге не хватает места профайлеру на хранение логов.
9 leonidkorolev
 
12.05.19
12:38
(8) "То ли блокировка какая, но сложно отловить, то ли еще что. Пробовал план запроса профайлером отловить, но сложно словить момент, когда будет подвисон и в итоге не хватает места профайлеру на хранение логов."

Если запрос долго выполняется, то его можно не ловить в профайлере. Он будет в Activity Monitor висет. Там на последних версиях скуля можно даже смотреть как он выполняется в онлайн. Можно держнуть план и сам текст запроса.
У меня стоить джоб на длинные запросы. Если какойто запрос в системе выполняется более 30 минут, мне на почту приходят письма. Я захожу в Activity Monitor и смотрю что за косяк.
10 zwolf
 
12.05.19
12:57
(0) Оптимизатор не может определить приемлимый порядок выполнения соединений. Поэтому, например, первое же соедиенение и отдает тебе 1368904 * 161806 записей с пожеланияеми всяческих успехов.
Попробуй переписать на выбрать из выбрать с подобром выборок так, чтобы количество записей постоянно уменьшалось. Ты же не забыл, что даже внутреннее соединение размножает строки результата, если условию соединения удовлетворяет больше одной записи?
11 dmpl
 
12.05.19
13:02
Сколько записей в результате получается?
12 Tester
 
12.05.19
13:12
(9) спасибо, будем пробовать ловить дальше.
(6) тут кстати да, поле Размещение составного типа.
(10) сейчас запрос как раз переписан на Выбрать из Выбрать, но результат нет. Про соединение по полям с неуникальными записями думал, но тут не в этом дело.
(11) в результате около 500 000.
13 vde69
 
12.05.19
13:15
1.  проиндексируй и расположи именно в таком порядке поля в первой ВТ вт_ТаблицаОтбораЛокальная
Номенклатура, Размещение, СтруктурнаяЕдиница

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

то есть у тебя именно последнее соединение все портит
14 vde69
 
12.05.19
13:16
(12) >>>тут кстати да, поле Размещение составного типа.

это ЖОПА!!!

от этого в первую очередь избавляйся
15 palsergeich
 
12.05.19
13:19
(14) Да не жопа это, если не заниматься разыменовыванием, не надо преувеличивать.
По представленному тексту - все ок.
Меня напрягает
вт_ТаблицаОтбораЛокальная: 1 368 904 записей
вт_Родословная_Номенклатура: 161 806 записей
Слишком много строк. Надо смотреть план, скорее всего проблемы там.
16 palsergeich
 
12.05.19
13:22
(6) Учи матчасть, когда имеет смысл ВЫРАЗИТЬ.
Долеко не всегда от этого есть хоть какой нибудь прок.
17 vde69
 
12.05.19
13:22
а меня напрягает

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

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

соответственно именно это и вызывает неопределенность плана запроса, он его то так то иначе строит...
18 vde69
 
12.05.19
13:24
(16) по этому полю идет джойн, и при составном поле там обязательно будет запрос к метаданным,

для проверки можно поставить этот джойн последним
19 palsergeich
 
12.05.19
13:26
(16) Начитаются советов.
А зачем это надо не понимают.
Простой пример:
В регистре есть измерение составного типа. Тип Приходная и расходная накладная.
Умнички переписывают все на выразить. Старательно, все запросы. а отличия нет. Показываешь ему планы запросов, а он не верит и бубни Выразить поможет...
(17) Это полное внешнее соединение. Ну логично, что все долго.
(18) 100500 раз доказывалось - порядок джойнов в запросе ни на что не влияет, за дебя это делает анализатор.
20 palsergeich
 
12.05.19
13:26
(19) Точнее оптимизатор СУДД
21 palsergeich
 
12.05.19
13:27
СУБД, тьфу
22 vde69
 
12.05.19
13:27
(19) в регистре составной тип не страшен, в во ВТ страшен
23 vde69
 
12.05.19
13:29
(22) +

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

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

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

ВЫБРАТЬ РАЗЛИЧНЫЕ
    взОтбор.Номенклатура,
    взОтбор.Размещение,
    взОтбор.СтруктурнаяЕдиница
ИЗ
    (ВЫБРАТЬ
        втОтбор.Номенклатура КАК Номенклатура,
        вт_Родословная_Номенклатура.Предок КАК НоменклатураПредок,
        втОтбор.Размещение КАК Размещение,
        вт_Родословная_Размещение.Предок КАК РазмещениеПредок,
        втОтбор.СтруктурнаяЕдиница КАК СтруктурнаяЕдиница,
        вт_Родословная_СтруктурнаяЕдиница.Предок КАК Структурная_единицаПредок
    ИЗ
        вт_ТаблицаОтбораЛокальная КАК втОтбор
            ВНУТРЕННЕЕ СОЕДИНЕНИЕ вт_Родословная_Номенклатура КАК вт_Родословная_Номенклатура
            ПО (вт_Родословная_Номенклатура.ссылка = втОтбор.Номенклатура)
            ВНУТРЕННЕЕ СОЕДИНЕНИЕ вт_Родословная_Размещение КАК вт_Родословная_Размещение
            ПО (вт_Родословная_Размещение.ссылка = втОтбор.Размещение)
            ВНУТРЕННЕЕ СОЕДИНЕНИЕ вт_Родословная_СтруктурнаяЕдиница КАК вт_Родословная_СтруктурнаяЕдиница
            ПО (вт_Родословная_СтруктурнаяЕдиница.ссылка = втОтбор.СтруктурнаяЕдиница)) КАК взОтбор
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ втОтборПредковПоЗначениям КАК втРодители
        ПО взОтбор.НоменклатураПредок = втРодители.НоменклатураПредок
            И взОтбор.РазмещениеПредок = втРодители.РазмещениеПредок
            И взОтбор.Структурная_единицаПредок = втРодители.Структурная_единицаПредок
26 palsergeich
 
12.05.19
13:33
(19) Забираю слова про полное внутренне назад, внимательно прочитал текст запроса. Там его нет.
Запрос написан корректно.
Единственное что смущает - 2 таблицы с очень большой выборкой
27 palsergeich
 
12.05.19
13:37
(25) Друг.
ни порядок полей, ни порядок соединений в тексте ничего не меняет. Единственное, что при изменении порядка -> заново запускается процедура поиска плана, и так как статистика могла измениться, план теоретически может стать другим, но в подавляющем большинстве это ничего не меняет.
открой настольную книгу эксперта и почитай. Одни из рекомендаций - не делать временные таблицы большими.
1КК это много
161К это тоже много.
Индексирование тоже не бесплатное и жрет ресурсы и место, имеет смысл при обращении к таблице более чем в 1 запросе, в остальных случаев - выстрел вхолостую.
28 Tester
 
12.05.19
13:40
(26) Тут вопрос с другой стороны - могут ли блокироваться как-то данные, что не дает выполнить запрос либо 100% в запросе/плане?
(27) Про индексирование согласен. Сравнение показывало, что в целом выполняется либо также либо еще дольше.
29 palsergeich
 
12.05.19
13:41
Самый правильный метод - взять листочек, бумажку и думать можно ли в текущей архитектуре отказаться от этих больших временных таблиц?
Если нет, то подумать, что надо изменить в архитектуре, что бы не было таких больших временных таблиц.
Может быть магически тебе как то получиться сделать лучше, хотя не вижжу за счет чего, но со временем роста базы - проблема вернется
30 vde69
 
12.05.19
13:44
(25) попробуй так, вроде должно быть стабильно по скорости...

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

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

То есть при не верном расположении полей по любому будет фулскан, даже если в условии часть полей индексированы
33 vde69
 
12.05.19
13:51
https://www.sql.ru/forum/32583/degradaciya-indeksov

последний пост ничего не напоминает :)
34 palsergeich
 
12.05.19
13:52
(33) oracle 8.1.7.3
35 leonidkorolev
 
12.05.19
13:52
(31) "Я его киляю "
Не надо его килять. В этот момент его как раз и надо изучать. Дергать план, сам запрос в формате скуля. Там все ответы.

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

Это оптимизатор строит разные планы.
36 vde69
 
12.05.19
13:53
(34) на SQL тоже самое, счас поищу
37 vde69
 
12.05.19
13:53
(35) >>>Это оптимизатор строит разные планы.

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

тут вопрос в том почему он их строит разными
38 palsergeich
 
12.05.19
13:53
(32) Слыхал слыхал.
Но во первых нигде не говориться о записи существенных объемов постоянно.
39 palsergeich
 
12.05.19
13:54
Потому что как говорять SQLщики, если у Вас ииспользуются временные таблицы - значит у Вас корявая архитектура
40 palsergeich
 
12.05.19
13:58
И какая нахрен деградация индексов?
По теме - с выборкой и помещением в Вт проблем нет.
41 Tester
 
12.05.19
14:00
(35) (37) спасибо. Тогда нужно только выловить планы и убедиться в этом.
42 palsergeich
 
12.05.19
14:04
(41) скажу так, из своей достаточно богатой практики. Единственное стабильное решение это использовать промежуточные объекты БД, я делал очень похожее, вспомогательный РС, заполняемый в подписке ускорил всё на порядки.
Все остальное давало только временный эффект.
43 palsergeich
 
12.05.19
14:07
Это задача из серии вычислить остатки на каждый день.
Да можно написать запрос.
Он первое время будет работать изумительно.
А потом в один прекрасный или не очень день все сломается.
Потом это оптимизируется и ещё какое то время работает и снова ломается.
То что, используются большие ВТ это уже признак того, что решение не стабильно. Сегодня 1кк, завтра 10кк и снова тема на форуме?)
44 Tester
 
12.05.19
14:11
(43) про архитектуру и логику это тема для отдельной темы :) Ибо этот запрос из блока функционала, в котором используется МВТ с десятками таблиц, кочующий по разным процедурам...
45 vde69
 
12.05.19
14:12
(43) >>>То что, используются большие ВТ это уже признак того, что решение не стабильно

+100

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

Ну а по поводу дополнительного регистра на каждый день - я думаю это то-же не самая лучшая идея (пример - производственный календарь в 1с, с ним всегда какие-то траблы вылазят...) хотя конечно все зависит от конкретики
46 palsergeich
 
12.05.19
14:14
(45) Норм работает в жизни, если грамотно спроектировать.
Вот изменения уже потом идут через боль.
47 Tester
 
12.05.19
14:15
О блин, пока тут треплюсь уже на 15 минут повис запрос.
48 vde69
 
12.05.19
14:16
(47) так сразу смотри очередь к диску и план запроса копируй в скриншоты
49 palsergeich
 
12.05.19
14:19
(46) Скажу так за 5 лет по 100 000 позиций (отраслевому справочника аналогичному номенклатуре по сути), вполне себе бодро шуршит, я 1.5 года там не работаю, но на сколько мне известно до сих пор летает.
Другое дело, что в первой редакции мы кое что не учли и пришлось делать схожий регистр но с другим полем.
И по факту 2 РС с остатками. Да база пухнет, но заказчик просто рыдал от счастья, когда вместо минут все стало работать менее секунды.
50 palsergeich
 
12.05.19
14:22
(44) Могу только посочувствовать
51 Tester
 
12.05.19
14:30
В активити мониторе есть этот запрос, но план не отображается.
Я еще наверное не сказал, что результат запросе также помещается во временную таблицу.

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
52 palsergeich
 
12.05.19
14:32
(51) Это не план, а текст запроса
53 palsergeich
 
12.05.19
14:33
И очередь к диску больше 1
54 vde69
 
12.05.19
14:33
(51) причина тормозов - очередь к диску D:,

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

ну и кстати пересчет статистики во время тяжолого запроса - не айс точно
55 palsergeich
 
12.05.19
14:34
(54) Причина - скидывание всякого из ОЗУ на HDD, а потом чтение
56 palsergeich
 
12.05.19
14:35
1КК записей уже нормально так весят
57 vde69
 
12.05.19
14:37
обращаю внимание, второй джойн идет с условием AND, это скорее всего из-за составного типа.
это условие идет скорее всего мимо индекса....
58 palsergeich
 
12.05.19
14:38
(57) AND это не OR и условие равенство
59 vde69
 
12.05.19
14:39
(55) правильно, сваливание в своп, о чем я и писал ранее, возможно нужно ограничить SQL выделяемой памятью (но тут смотреть надо, это сейчас не понацея а только один из вариантов). По тому как это не SQL делает а операционка..
60 vde69
 
12.05.19
14:40
(58) все равно он в индекс не попадет...
61 palsergeich
 
12.05.19
14:41
(60) А не факт что у этой ВТ в принципе есть индекс.
62 palsergeich
 
12.05.19
14:41
А если есть, то его создание и хранение - это еще больше свопов
63 rphosts
 
12.05.19
14:42
Очередь к диску максимум 2,5 - чуть больше нормы. Так что не клевещите на диск.

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

И да, как тут отметили - базы требуют регулярного обслуживания!
64 vde69
 
12.05.19
14:43
(61) без индексов будет ЖП. SQL оптимизируя запросы с ВТ сам иногда добавляет не кластерезованные внутрение идексы.
65 rphosts
 
12.05.19
14:45
(55) миллион записей скидывается на диск? С той структурой что есть они много мегабайт займут? за 8 часов можно сделалть даже на 5 дюймовые флопы 100500 копий такого свопа
66 rphosts
 
12.05.19
14:46
+ (63) если 8.3.Х.Х - неплохо сделать для временных таблиц по полям соединения индексы.
67 Tester
 
12.05.19
14:49
А план не могу посмотреть потому что запрос не выполнился?
https://c.radikal.ru/c00/1905/98/f7ca7da686fa.png
68 rphosts
 
12.05.19
14:50
(67) а из ТЖ?
69 rphosts
 
12.05.19
14:50
и да, сделай как написал в (63) + (66)
70 rphosts
 
12.05.19
14:51
+(68) сейчас ухожу по делам, но если есть проблема в чтении текстового плана запроса - по возвращению могу посмотреть
71 palsergeich
 
12.05.19
14:52
(65) Много мегабайт займут. А если еще постоянное чтение благодоря Nested loops то и по времени очень затратно
(68) (66) там по моему индекс во временных стал кластерным не сначала, а совсем относительно недвано.
72 Tester
 
12.05.19
15:01
(70) я, млин, не знаю где его посмотреть. В ТЖ ничего нет, хотя включена фиксация всего.
73 palsergeich
 
12.05.19
15:06
<plansql/>
74 palsergeich
 
12.05.19
15:07
Пример ТЖ со сбором плланов
<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>
75 vde69
 
12.05.19
15:11
(73) ВЫ его с ТЖ сейчас совсем прибьете :)

(72) на запросе ПКМ и показать план выполнения, если он нам не показывается, то или уже завершен и удален или еще не сформирован
76 Tester
 
12.05.19
15:13
(74) тьфу, затупил. Фиксировались только EXCP.
(75) план не показывает, выше ссылку на скрин приложил. Запрос висит, но плана нет.
77 vde69
 
12.05.19
15:14
(75) +

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

бекапы - типовые вроде нет, но уверен, что с кривыми руками можно сервак завесить даже обновлением статистики....
78 Tester
 
12.05.19
15:17
(77) сейчас выключил все job'ы - запрос как висел так и висит.
79 vde69
 
12.05.19
15:17
81 vde69
 
12.05.19
15:27
82 Tester
 
12.05.19
15:31
(81) спасибо, но предполагаемый план запроса к временным таблицам, созданным в другом соединении не получить. Нужно дождаться выполнения запроса, который уже выполняется 1.5 часа.
83 H A D G E H O G s
 
12.05.19
15:35
(82) Есть возможность выложить базу на онлайн диск?
84 Tester
 
12.05.19
15:37
(83) нет, к сожалению. База уже 700 Гб.
Перезапустил фоновое - этот же запрос выполнился за 3 секунды.
85 rphosts
 
12.05.19
17:04
(84) для начала сделай как написано в (69) а там по результату
86 Tester
 
12.05.19
17:15
(85) Вынес вложенный запрос в отдельную временную таблицу. Итого пока стабильно выполняется 3 минуты. Это, конечно, медленнее 5 секунд, но быстрее 8 часов ) Итого если будет стабильно выполняться и дальше, то скорость устроит. Поля, по которым соединяется, вроде как проиндексированы.
87 palsergeich
 
12.05.19
17:17
(86) А, так это еще и вложенный запрос был?)
88 H A D G E H O G s
 
12.05.19
17:19
(86) 3 минуты - это жесть конечно
89 H A D G E H O G s
 
12.05.19
17:21
90 rphosts
 
12.05.19
17:26
(88) у него может ещё и базы не дообслуживались. У него и журнал и база на 1 диске и хз сколько там чего и как с базой в это время крутит-вертит. Короче трэш, содом и гоморра и лечить нужно по шагам, имхо.
91 Tester
 
12.05.19
17:27
(87) я в самом вначале прикладывал 1 запрос просто с соединениями, потом переделанный с подзапросом (оба выполнялись примерно одинаково). Сейчас попробовал вынести подзапрос в ВТ - пока мониторю.
(88) а что тут такого, если это не бухия на 3 буха, а база 700 ГБ, в спр. Номенклатура 500 тысяч элементов и РИБ > 20 узлов? :)
92 rphosts
 
12.05.19
17:31
(86) если ты пишешь во временные таблицы то это твоя обязанность создать индексы для них а не вроде хз кого. Но делать замер времени т.к. как ни странно может только ухудшить (такое бывает).

Из (51) видно, что составные поля есть... там точно к 1 типу не сводится? Но это делать вторым шагом когда закончишь с индексами.
93 palsergeich
 
12.05.19
17:33
(91) Номенклатура 500 тысяч элементов это не так много, в крупных СЦ в год больше миллиона новой номенклатуры вводится и 20 узлов РИБ тоже, они на запрос прямо никак не влияют.
А из 700 ГБ 650 могут оказаться прикрепленными файлами
94 rphosts
 
12.05.19
17:35
(91) 700 ничё, но если структура продумана, то не на много хуже чем 200 например... у тебя-же наверняка старые записи прошлых лет в первую очередь?
500 тыс и 20+ узлов? у меня есть базы и покруче чем 500 тыс и 20+филиалов и онлайн. Тут не в том вопрос.


Стоп!!! у тебя 700Гб и журнал и база на 1 диске? Что за жпо? и для темпов нет своего ССД? А бэкапы не на том-же диске хранишь?
95 H A D G E H O G s
 
12.05.19
17:35
(91) У тебя в результат запроса выводится 500 кзаписей? Если нет - то это - треш.
96 H A D G E H O G s
 
12.05.19
17:39
97 Tester
 
12.05.19
17:40
(93) Про 650 ГБ файлов из 700 ГБ - это классная шутка :) Но к сожалению все 700 ГБ - это гребаные нужные данные :(
(94) К сожалению все на 1 физическом диске/массиве - и журнал и база и tempdb. Бэкапы на внешний nas делаются.
98 Tester
 
12.05.19
17:41
(95) Да, около того.
99 H A D G E H O G s
 
12.05.19
17:45
Автор, ты хоть план запроса покажи.
Это вот тут
http://prntscr.com/nnk1qp
100 rphosts
 
12.05.19
17:48
(99)тоже любопытно?
101 H A D G E H O G s
 
12.05.19
17:50
(100) Ставлю на mdop=0
102 H A D G E H O G s
 
12.05.19
17:50
Ну или на tablespool
103 rphosts
 
12.05.19
17:53
(101) нууу, ну вряд-ли тут параллельность так всё ломает.
(102) это вероятнее, но хз, хотя 1 диск на всё...

Автор, а там диск или хотя-бы какой рэйд-массив?
104 Tester
 
12.05.19
17:54
(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)
105 rphosts
 
12.05.19
17:55
+ (103) скорее всего классика: старая статистика, большое расхождение между прогнозом и фактом и соответственно не тот способ соединения
106 rphosts
 
12.05.19
17:55
(104) и есть 100500 способов его выполнения (ака планов выполнения этого самого запроса)
107 H A D G E H O G s
 
12.05.19
17:56
(104) mdop=0
108 H A D G E H O G s
 
12.05.19
17:57
109 Tester
 
12.05.19
18:02
Max Degree of Parallelism = 0
110 H A D G E H O G s
 
12.05.19
18:03
(109) Ставь =1, перезагружай SQL (но не уверен, что необходимо) и пробуй заново
111 Провинциальный 1сник
 
12.05.19
18:08
(30) "а вообще засады в 8 часов с запросом - это обычно не чисто софтовая проблема, чаще всего это проблема ухода ОЗУ в своп"
С временными таблицами размером в единицы мегабайт? Какой тут своп. Тут нестедлуп в чистом виде. И бороться надо именно с ним.
112 vde69
 
12.05.19
18:10
(110) при смене паралелизма требуется перезапуск службы
113 vde69
 
12.05.19
18:13
из (104) видно, что довольно много времени занимает сортировка, а всего плана и не видно....
114 H A D G E H O G s
 
12.05.19
18:13
(112) Не надо. Вот только что попробовал у себя.
115 H A D G E H O G s
 
12.05.19
18:14
(113) Вставка в ВТ там половину времени.
116 Tester
 
12.05.19
18:18
Ай, короче забейте пока. Этот запрос возвращает 85 млн строк :)
117 H A D G E H O G s
 
12.05.19
18:20
(116) зачем тебе столько?
118 vde69
 
12.05.19
18:23
(91) имено это я и предлогал....

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

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

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

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

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

например на 1000 записей основной таблицы есть 10 записей вспомогательной, при этом соответсвий всего 2 (два уникальных значения, но записей отбираем 10), при этом мы должны понимать, что в результат попадет не 10 записей а немного больше :), собственно я скорее ставку в сабже сделаю именно на этот вариант..... (так сказать неявное умножение)
119 vde69
 
12.05.19
18:25
(116) судя по всему я в (118) прав :)
120 vde69
 
12.05.19
18:30
а стабильность в 3 минуты ты получил по тому, что ВТ неяным образом удаляет дубли, то есть потратил время на удаление из этих 67 лямов почти всего :)
121 rphosts
 
12.05.19
18:31
(109) МЛЯ!!!!
122 vde69
 
12.05.19
18:31
кстати вопрос Дмитрию:

при создании ВТ удаляются дубли или происходит свертка?
123 rphosts
 
12.05.19
18:33
(122) только если создаешь с гроупбай или с безповторяющихся. просто так только кошки....
124 rphosts
 
12.05.19
18:34
+ (121) настраивавшему сервер по рукам линейкой... металлической!
125 rphosts
 
12.05.19
18:34
+ (123) хотя у Димы может другая версия
126 H A D G E H O G s
 
12.05.19
18:36
(122) Ниче не удаляется и не свертывается, что отправилось в ВТ, то и будет в ВТ.
127 H A D G E H O G s
 
12.05.19
18:39
(124) Я пока не видел ни одного клиента, у которого был бы настроен SQL полностью по заветам ИТС.
128 rphosts
 
12.05.19
18:42
(127) у меня настроено... кое что не гуманно к лиц, но чуть-чуть, но там по другому никак.... ибо что за хрень местами в 1С (например покури внешние источники если субд не сиквел)
129 vde69
 
12.05.19
18:54
по поводу составных типов:

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

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

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

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

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

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

ВЫБРАТЬ Товар.Наименование ИЗ Справочник.Товары КАК Товар ГДЕ Артикул = "001"
|ОБЪЕДИНИТЬ ВСЕ
|ВЫБРАТЬ Товар.Наименование ИЗ Справочник.Товары КАК Товар ГДЕ Артикул = "002"
130 Tester
 
12.05.19
19:50
(129) спс, с этим сталкивался лично. Разница в скорости выполнения отличалась в разы.
131 nicxxx
 
13.05.19
05:18
(32) Ты серьезно???
https://docs.microsoft.com/ru-ru/sql/t-sql/queries/search-condition-transact-sql?view=sql-server-2017
"Порядок выполнения логических операторов может меняться в зависимости от настроек оптимизатора запросов."
132 zwolf
 
13.05.19
06:45
(131) Он пытается рассказать о двух вещах сразу - о селективности индекса в зависимости от порядка его полей и о покрывающих индексах. Ну, как может (:
133 nicxxx
 
20.05.19
17:42
Вот, нашел где важен порядок. 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)
134 rphosts
 
20.05.19
18:58
(133) из 1С нет возможности передать подсказку оптимизатору.
135 H A D G E H O G s
 
20.05.19
19:01
(129) Хороший пример, когда разбивать на несколько запросов по ИЛИ бессмысленно.
136 nicxxx
 
21.05.19
05:54
(134) Это лишь ответ на странную фразу vde69 про порядок условий в секции WHERE.
137 Nikoss
 
21.05.19
14:45
(135) зачем тогда этот пример написан в ИТС?
138 nicxxx
 
24.05.19
11:15
(137) Затем, что это всего лишь пример. Когда структура отбора будет чуть сложней, чем (Артикул = "001" ИЛИ Артикул = "002"), тогда UNION ALL может очень пригодиться. Например, если у тебя там составное поле из нескольких документов, миллион строк и разброс данных очень значительный, а надо найди десяток документов двух видов. Через "ИЛИ" индекс скан будет длиться 60 секунд, а index seek + UNION ALL - 2 секунды.
139 Tester
 
29.05.19
14:39
В общем решил проблему переписыванием запроса.
Доказательств, к сожалению, что SQL Server действительно строил неоптимальный план найти не удалось, т.к. подвисания были в нескольких процентах случаев, а в остальных запрос выполнялся пару секунд. Помог вынос результата части запроса во временные таблицы. Оборачивание в подзапрос несколько улучшало ситуацию (подвисания были реже), но все равно были.