Имя: Пароль:
1C
1С v8
Оптимизация запроса при СрезПоследних
, ,
0 dj_serega
 
29.12.13
17:37
Какой из запросов оптимальней?

Текст запроса 1:

ВЫБРАТЬ
    ЦеныНоменклатурыСрезПоследних.Номенклатура,
    ЦеныНоменклатурыСрезПоследних.Цена
ИЗ
    РегистрСведений.ЦеныНоменклатуры.СрезПоследних(
            &Период,
            Номенклатура.НоменклатурнаяГруппа В (&СписокНоменклатурныхГрупп)
                И НЕ Номенклатура В (&СписокНоменклатрыИсключения)
                И НЕ Номенклатура.НеОтображатьВСписках) КАК ЦеныНоменклатурыСрезПоследних

УПОРЯДОЧИТЬ ПО
    ЦеныНоменклатурыСрезПоследних.Номенклатура.ПолеСортировки


Текст запроса 2:

ВЫБРАТЬ
    ЦеныНоменклатурыСрезПоследних.Номенклатура,
    ЦеныНоменклатурыСрезПоследних.Цена
ИЗ
    РегистрСведений.ЦеныНоменклатуры.СрезПоследних(
            &Период,
            Номенклатура В
                (ВЫБРАТЬ
                    Номенклатура.Ссылка
                ИЗ
                    Справочник.Номенклатура КАК Номенклатура
                ГДЕ
                    Номенклатура.НоменклатурнаяГруппа В (&СписокНоменклатурныхГрупп)
                    И НЕ Номенклатура.Ссылка В (&СписокНоменклатрыИсключения)
                    И НЕ Номенклатура.НеОтображатьВСписках)) КАК ЦеныНоменклатурыСрезПоследних

УПОРЯДОЧИТЬ ПО
    ЦеныНоменклатурыСрезПоследних.Номенклатура.ПолеСортировки
1 Нууф-Нууф
 
29.12.13
17:39
получи список номенклатуры во временную, проиндексируй и уже ее используй в качестве условия
2 dj_serega
 
29.12.13
17:40
(1) Понял. Спасибо.
3 dj_serega
 
29.12.13
17:42
(1) Имелось ввиду в Пакет ;)
4 Нууф-Нууф
 
29.12.13
17:43
(3) не возражаю
5 EugeniaK
 
29.12.13
17:45
(0) Зависит от количества записей в справочнике и в РС.
Думаю, в большинстве случаев второй.
6 dj_serega
 
29.12.13
17:53
(5) Ну на текущий момент номенклатуры около 1000, а РС 500.
Рост предполагается и по этому на перед и решил оптимизировать ;)
Если не забуду, через время проверю через замер производительности.
7 vvp91
 
29.12.13
19:05
При наличии упорядочивания по полю, включенному в номенклатуру, и присутствия отбора по номенклатуре надо явно делать одно соединение.

Причина 1: При большом объеме данных, формируемом выборкой из номенклатуры, включать выборку в параметры виртуальной таблицы тоже неправильно, поскольку включать большую некоррелированную выборку в операнд В секции ГДЕ (а именно это и происходит в обоих вариантах) - гарантированно получить проблемы проблемы с оптимизатором.

Причина 2: При маленьком объеме выборки из номенклатуры может случиться выгода от включения подзапроса в параметры виртуальной таблицы, но упорядочивание все испортит (1С трансформирует запрос в соединение). Да и где гарантии, что всегда будут маленькие объемы выборки номенклатуры? Кроме того, на маленьких объемах номенклатуры оптимизатор сам корректно выберет базовую таблицу - номенклатуру.

Во всех случаях, оптимальней будет:


ВЫБРАТЬ
    Цены.Номенклатура,
    Цены.Цена
ИЗ
    РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&Период) КАК Цены
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.Номенклатура КАК Ассортимент
        ПО Ассортимент.Ссылка = Цены.Номенклатура
ГДЕ
    Ассортимент.НоменклатурнаяГруппа В (&НоменклатурныеГруппы)
    И НЕ Ассортимент.Ссылка В (&ПозицииИсключения)
    И НЕ Ассортимент.НеОтображатьВСписках
УПОРЯДОЧИТЬ ПО
    Ассортимент.ПолеСортировки


Да, и еще, называть поля с отрицанием - гарантировано получить двойное отрицание в коде, что плохо читается. Ну и грамматические ошибки в названиях переменных тоже не улучшают картину.
8 dj_serega
 
29.12.13
20:14
(7) 1. Не заметил ошибку в переменной.
2. А вот по этому поводу "НеОтображатьВСписках" спорить с некоторыми бесполезно =) Не знают возможностей платформы, и знать не хотят ;(
3. Спасибо. Попробую такой запрос выдвинуть в жизнь.
9 Лефмихалыч
 
29.12.13
20:23
(1) зачем индексировать временную в данном случае?
10 sanja26
 
29.12.13
21:27
(9) по привычке
11 dj_serega
 
30.12.13
15:02
(7) А без сортировки лучше воспользоваться советом (1) ?
12 MrStomak
 
30.12.13
15:25
(7) Считаю, оптимизатору и так гарантированных проблем от тета-соединений в подзапросах, генерируемых ВТ СрезПоследних, более чем достаточно. Причем в указанном примере этот жутко тормозной срез последних будет выполняться для абсолютно всех записей регистра. Приведенный вариант с внутренним соединением на таблицу номенклатуры необходимо дополнить отбором к ВТ среза последних, иначе это самоубийство, 99% ресурсов уйдет на ВТ.
13 dj_serega
 
30.12.13
15:31
(12) Ну и на маленьких данных не проверишь.
14 Аннушка
 
30.12.13
15:59
эээ... а почему в (7) три последних условия выделены в условия, а не в соединения? разве не оптимальнее будет вместо "где" поставить "и"?
15 azernot
 
30.12.13
16:09
(7) Чтобы утверждать, что такой запрос будет оптимальнее, нужно либо быть мега-супер-пупер профессионалом, который обладает такими знаниями, что может себе позволить пойти наперекор всем, либо быть полным ламером не знающим основ.

Кто ты, дружище?
16 Speshuric
 
30.12.13
17:12
(0) Способ и время выполнения запроса будут отличаться в зависимости от: версии 1С, версии и типа СУБД, размера и заполнения справочника и регистра, размеров списков фильтров &СписокНоменклатурныхГрупп и &ПозицииИсключения. Для MS SQL и версии 8.2 оба ваши запроса и запрос из (7) почти гарантированно дадут одинаковый план выполнения и одинаковое время выполнения. Есть несколько нюансов, типа "внутреннего соединения" в (7), но с учетом остального, это вряд ли повлияет.
Для версии 8.3 (из-за возможностей итогов по РС) вариант из (7) или ваш вариант, но с условием на номенклатуру ВНЕ условий виртуальных таблиц может оказаться быстрее (из-за лишних соединений). Это наконец-то задокументировала 1С в ИТС: http://its.1c.ru/db/metod81#content:5457:1
(12) а) в срезе последних вроде бы нет тэта соединений, есть только соединения по эквивалентности, б) если регистр сведений не делать с точностью до момента документа, то "соединений в подзапросах, генерируемых ВТ СрезПоследних" ровно 1 штука, хотя, да с одной стороны там подзапрос с MAX(), что не сильно радует оптимизатор, в) вред от того, то условие, что написано выше на таблице итогов может дать до 3 лишних соединений обычно выше, чем от соединения с подзапросом, г) ваша рекомендация "дополнить отбором к ВТ среза последних" для MS SQL почти гарантированно вредна.
Посмотрите генерируемые запросы и их планы.
17 х86
 
30.12.13
17:27
все ушли смотреть генерируемые запросы и их планы
18 dj_serega
 
30.12.13
17:29
(17) ну я ушел параметризировать ВТ в (7) =)
19 х86
 
30.12.13
17:32
(18)имхо послушай Нуфа, только упорядочивание исключи
20 MrStomak
 
30.12.13
18:31
(16) Посмотрел - 1С сейчас строит запрос к скулю, где подзапрос к регистру с  условием только на активность и период соединяется иннер джойном той же таблице, но уже с условиями на измерения. Скуль эту ситуацию интерпретирует так, что подзапрос при исполнении физических операторов уже использует предикаты будущего соединения. Т.е., грубо говоря, "условие в параметрах ВТ" дописывается планировщиком. Как минимум для MS SQL можно рекомендовать использовать вариант из (7). Для файловой базы - не уверен.
21 vvp91
 
31.12.13
19:17
>> (15) Кто ты, дружище?
Я могу себе позволить пойти наперекор всем. ;-)

>> (20) Как минимум для MS SQL можно рекомендовать использовать вариант из (7)
Для Oracle и PostgreSQL тоже. Про DB2 не скажу.

Всех с наступающим Новым годом!!!
22 КонецЦикла
 
31.12.13
20:15
(19) +1
23 echo77
 
02.01.14
09:25
Как человек видавший что происходит при срезепоследних у РС с тремя измерениями, с количеством записей в 600К абсолютно не рекомендую накладывать условия на измерения в разделе ГДЕ

Если в РС комбинаций значений измерений больше 10К то торомоза при выборе среза из такого регистра будут жуткие.
Я это все проверял на файловой базе. Думаю на серверной будет немного получше
24 Rebelx
 
02.01.14
10:35
(0) в данном случае - второй оптимальнее. Ибо запрос с использованием виртуальной таблицы сильно оличается если в параметрах использовать любые условия кроме обращения к полям этой таблицы. Т.е. добавление условия "Истина" уже повлияет на структуру запроса. Так же нельзя использовать обращение через точку.
25 MrStomak
 
02.01.14
13:45
(21) Проверил на PostgreSQL - вы неправы.
Вот план подобного запроса без условий вт:
"Merge Join  (cost=20887.43..22562.34 rows=1 width=27) (actual time=404.476..404.499 rows=1 loops=1)"
"  Output: t5._idrref, t4._fld5111"
"  Merge Cond: ((t3._fld5108rref = t4._fld5108rref) AND (t3._fld5109rref = t4._fld5109rref) AND (t3._fld5110rref = t4._fld5110rref))"
"  Join Filter: (t4._period = (max(t3._period)))"
"  Buffers: shared hit=299 read=3160, temp read=1647 written=1651"
"  ->  GroupAggregate  (cost=18479.05..19958.65 rows=10761 width=68) (actual time=300.121..402.552 rows=15089 loops=1)"
"        Output: t3._fld5108rref, t3._fld5109rref, t3._fld5110rref, max(t3._period)"
"        Buffers: shared hit=91 read=3160, temp read=1647 written=1651"
"        ->  Sort  (cost=18479.05..18748.07 rows=107607 width=68) (actual time=300.110..367.821 rows=107253 loops=1)"
"              Output: t3._fld5108rref, t3._fld5109rref, t3._fld5110rref, t3._period"
"              Sort Key: t3._fld5108rref, t3._fld5109rref, t3._fld5110rref"
"              Sort Method:  external merge  Disk: 7832kB"
"              Buffers: shared hit=91 read=3160, temp read=1647 written=1651"
"              ->  Seq Scan on public._inforg5107 t3  (cost=0.00..5068.57 rows=107607 width=68) (actual time=0.037..54.856 rows=108266 loops=1)"
"                    Output: t3._fld5108rref, t3._fld5109rref, t3._fld5110rref, t3._period"
"                    Filter: (t3._active AND (t3._period <= '2013-01-01 00:00:00'::timestamp without time zone))"
"                    Buffers: shared hit=91 read=3160"
"  ->  Sort  (cost=2408.38..2410.12 rows=698 width=95) (actual time=0.452..0.459 rows=113 loops=1)"
"        Output: t4._fld5111, t4._fld5108rref, t4._fld5109rref, t4._fld5110rref, t4._period, t5._idrref"
"        Sort Key: t4._fld5108rref, t4._fld5109rref, t4._fld5110rref"
"        Sort Method:  quicksort  Memory: 40kB"
"        Buffers: shared hit=208"
"        ->  Nested Loop  (cost=4.81..2375.41 rows=698 width=95) (actual time=0.054..0.348 rows=113 loops=1)"
"              Output: t4._fld5111, t4._fld5108rref, t4._fld5109rref, t4._fld5110rref, t4._period, t5._idrref"
"              Buffers: shared hit=208"
"              ->  Index Scan using _referenc63_byfield784_rr on public._reference63 t5  (cost=0.00..48.01 rows=11 width=20) (actual time=0.019..0.042 rows=24 loops=1)"
"                    Output: t5._idrref, t5._version, t5._marked, t5._ismetadata, t5._parentidrref, t5._folder, t5._code, t5._description, t5._fld749, t5._fld750, t5._fld751, t5._fld752rref, t5._fld753rref, t5._fld754rref, t5._fld755, t5._fld756rref, t5._fld757, t5._fld758rref, t5._fld759rref, t5._fld760rref, t5._fld761rref, t5._fld762rref, t5._fld763, t5._fld764rref, t5._fld765rref, t5._fld766rref, t5._fld767, t5._fld768rref, t5._fld769, t5._fld770rref, t5._fld771, t5._fld772rref, t5._fld773, t5._fld774, t5._fld775, t5._fld776rref"
"                    Index Cond: (t5._fld765rref = '\\230Z\\000@\\364\\230|\\253\\021\\332ai\\320\\317\\331R'::bytea)"
"                    Buffers: shared hit=22"
"              ->  Bitmap Heap Scan on public._inforg5107 t4  (cost=4.81..210.79 rows=63 width=75) (actual time=0.006..0.008 rows=5 loops=24)"
"                    Output: t4._period, t4._recordertref, t4._recorderrref, t4._lineno, t4._active, t4._fld5108rref, t4._fld5109rref, t4._fld5110rref, t4._fld5111, t4._fld5112rref, t4._fld5113rref"
"                    Recheck Cond: (t4._fld5109rref = t5._idrref)"
"                    Buffers: shared hit=186"
"                    ->  Bitmap Index Scan on _inforg5107_bydims5115_rtrr  (cost=0.00..4.79 rows=63 width=0) (actual time=0.005..0.005 rows=5 loops=24)"
"                          Index Cond: (t4._fld5109rref = t5._idrref)"
"                          Buffers: shared hit=73"
"Total runtime: 406.465 ms"

Мы видим отрабатывание SeqScan на таблице РС с предикатом на активность и период (что соответствует тексту вложенного запроса), он возвращает больше 100000 записей, в то время как нам нужна по условию только 1. Время выполнения запроса (7) медленнее второго запроса из (0) на 1 порядок.
26 КонецЦикла
 
02.01.14
14:18
(25) То что написал нуф-нуф еще быстрее будет имхо
27 х86
 
02.01.14
14:41
(21)есть рекомендации 1С по пользованию ВТ это факт

и если сейчас запрос транслируется в скуль как-то то не факт что на следующем релизе он будет также транслироваться. Получаем что на данном релизе(а какой у автора???) возможно запрос в (7) выполняется более-менее как-то, то возможно что уже на следующем релизе уже будут жуткие тормоза, потому-что не соблюли стандарты разработки
28 MrStomak
 
02.01.14
15:14
(26) Нет, дополнительная ВТ здесь не ускорит ничего, только затраты на создание лишние. Во всяком случае, количество записей, возвращаемое подзапросом, очень четко предсказывается планировщиком, т.к. условие очень простое и легко накладывается на статистику.

Несколько экспериментов на postgre показывают где-то 30% замедление при создании ВТ номенклатуры с 1 строкой, чме больше там строк, тем больше рызрыв по сравнению с подзапросом.
29 MrStomak
 
02.01.14
15:22
(27) Других рекомендаций 1С и быть не может, т.к. отсутствие условия в виртуальной таблице заставляет её генерировать даже на вид неоптимальный запрос, оптимальное выполнение которого уже будет зависеть исключительно от версии СУБД и от её загруженности (при сильной нагрузке и отключении некоторых ступеней оптимизации MS SQL может и не отванговать будущее соединение подзапроса). Временной выигрыш запроса из (7) на MS SQL равен времени выполнения подзапроса в условии ВТ из (0), а это, как правило, достаточно быстро, а возможная потеря, если подзапрос отберет все записи, может увеличить время выполнения на порядки.

Применять подобное ускорение можно только на свой страх и риск, мне кажется.
30 КонецЦикла
 
02.01.14
16:18
(28) Да, значит пока все ОК. Вдруг 1С как-то не так "транслирует" :)
Вообще автор бы сам мог проверить, создав миллион записей в пустой базе и выложить результат
31 dj_serega
 
03.01.14
20:13
(27) Кстати да... забыл эту важную деталь: 8.2.19.76.
(30) Думал можно сразу ответить на вопрос =)

Придется тестировать. На сервере будет MS SQL. Получается для MS SQL либо ВТ либо внутреннее соединение.
На текущий момент оставил (7), и на глаз быстрее работало =))) после праздников, позамеряю и отпишусь.
Вариантов вижу 4. Может придумаются еще :)