|
Оптимизация запроса при СрезПоследних | ☑ | ||
---|---|---|---|---|
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. Может придумаются еще :) |
Форум | Правила | Описание | Объявления | Секции | Поиск | Книга знаний | Вики-миста |