Оптимизация запроса к виртуальной таблице регистра накопления

Программирование - Практика программирования

Удачный ответ на собеседовании. Оптимизация запроса. Программистам пригодится. ))

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

ВЫБРАТЬ
...
ИЗ РегистрНакопления.Продажи.Обороты() КАК Продажи
Где &Условие

При этом экзаменатор ждет ответа, что &Условие лучше помещать не в секции "ГДЕ", а параметром виртуальной таблицы "Обороты".

Не нужно его разочаровывать.

Хотя в статье https://its.1c.ru/db/metod8dev/content/5457 указано, что это не всегда так. Вкратце, условие должно быть простым и эффективным - то есть отсеивать значительную часть данных. Других условий нужно избегать или даже их помещать в секцию "ГДЕ".

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

Слово "индексировать" вызывает положительные эмоции даже у тех, кто равнодушен к фотографиям котиков и собачек. Даже у меня вызывает.

Для индексирования нужно создать дополнительную временную таблицу. В итоге получилось как-то так:

ВЫБРАТЬ
    Номенклатура.Ссылка КАК Ссылка
ПОМЕСТИТЬ тТовары
ИЗ Справочник.Номенклатура КАК Номенклатура
ГДЕ Номенклатура.Ссылка В (&СписокНоменклатуры)
ИНДЕКСИРОВАТЬ ПО Ссылка
;
//////////////////////////////////////////////////////////
ВЫБРАТЬ
    Продажи.Номенклатура,
    Продажи.КоличествоОборот,
    Продажи.ДокументПродажи
ИЗ
    РегистрНакопления.Продажи.Обороты(
    &Дата1,&Дата2,Регистратор,
    Номенклатура В (ВЫБРАТЬ тТовары.Ссылка ИЗ тТовары КАК тТовары)
) КАК Продажи

Собеседование прошло в позитивном ключе.

Когда я вернулся на свое рабочее место, решил проверить на своих данных. В качестве &Условие я взял "Номенклатура в Иерархии(&Товары)" которое согласно статье https://its.1c.ru/db/metod8dev/content/5457  является сложным (генерирует много подзапросов при трансляции в SQL) и неэффективным (сюда попадает 99% справочника). Но оно необходимо - исключить Материалы. Это условие у меня дает список 10 тысяч элементов.

Ниже замеры времени на моих данных, которые показывают порядок разницы в скорости. Если кто-то сочтет пример неподробным - может проверить на своих данных.

1. Условие в секции "ГДЕ" - 94 сек.
2. Условие внутри виртуальной таблицы - 94 сек.
3. Предварительное индексирование временной таблицей, в секции "ГДЕ" 3 сек.
4. Предварительное индексирование временной таблицей, внутри виртуальной таблицы 3 сек.

Удачи всем !

См. также

Комментарии
1. Арман Бегов (Dream_kz) 13.08.17 11:22 Сейчас в теме
Индексирование не панацея, иногда время на создание индекса увеличивает время выполнения запроса
AlexGroovy; NN2P; Andromancer; davydoff; +4 Ответить
2. Николай Больсунов (boln) 927 13.08.17 14:38 Сейчас в теме
При этом экзаменатор ждет ответа, что &Условие лучше помещать не в секции "ГДЕ", а параметром виртуальной таблицы "Обороты".
Это правильно, если условие накладывается на ключевые поля. Если условие накладывается на поле результата (например, СуммаОборот > 100000), то воспользоваться параметром Условие просто невозможно.
3. Николай Больсунов (boln) 927 13.08.17 14:47 Сейчас в теме
Кстати, мало кто знает, что СКД, реализуя отбор, иногда формирует в запросе секцию ГДЕ с условием на измерение виртуальной таблицы... И мы ломаем голову, с чего это отчёт так тормозит.
4. Николай Васильев (vasilev2015) 276 13.08.17 16:07 Сейчас в теме
(2) Конечно, если поле не ключевое, то его не удастся поместить в параметры виртуальной таблицы. Однако некоторые условия с ключевыми полями тоже неправильно помещать туда. Посмотрите упомянутую статью ИТС.
5. Николай Васильев (vasilev2015) 276 13.08.17 16:09 Сейчас в теме
(3) Для СКД последнее время для размещения условий использую предусмотренную конструкцию { .......КАК ПолеОтбора}, с использованием отборов. Это позволяет сделать исполнение более предсказуемым.
6. Павел Жихарев (palsergeich) 14.08.17 13:17 Сейчас в теме
Скажем так, на хайлоаде Индексировать рекомендуется не использовать, потому что создание индекса временной таблицы - дополнительная и весьма существенная нагрузка, а самое главное абсолютно бессмысленная, как в этом примере, нагрузка.
В моей практике только на одном запросе индексировать как то увеличило быстродействие, есть примеры, когда эта операция значительно увеличило время выполнения запроса.
Но это ответ уже совсем на другие зарплатные ожидания.
7. Павел Чистов (GROOVY) 2284 14.08.17 13:30 Сейчас в теме
Мне нравятся такие статьи. Но если честно, тем, что они заставляют/побуждают, наконец, открыть профайлер и посмотреть как работает запрос на физическом уровне.
cleaner_it; Chernika80; ifilll; NoRazum; +4 Ответить 2
8. Павел Жихарев (palsergeich) 14.08.17 13:39 Сейчас в теме
А уж если совсем придиратся, то периодичность - регистратор в таблице оборотов - это по сути дела двойной вложенный запрос в таблицу движений без использований таблиц итогов, в данной ситуации для этого отчета было бы правильнее сделать простейший запрос к реальной таблице, он был бы в данной ситуации оптимальнее. (Просто потому что периодичность регистратор не использует итоги и оптимизатору будет проще подобрать оптимальный план запроса для запроса который не использует вложенные подзапросы).
А это ответ еще на более высокие зарплатные ожидания)
sergelemon; PLAstic; kabanoff; Sergey.Noskov; +4 Ответить 2
9. Николай Васильев (vasilev2015) 276 14.08.17 13:56 Сейчас в теме
(7) я начинал изучение 1С с просмотра курсов Павла. Спасибо ему.
10. Николай Васильев (vasilev2015) 276 14.08.17 14:05 Сейчас в теме
(6) Есть нормативы по скорости операций SQL. Если количество строк в одной таблице Х, а в другой таблице Y, то например

1. Индексация первой таблицы потребляет времени порядка Х
2. Индексация второй таблицы потребляет времени порядка Y
3. Соединение двух индексированных таблиц потребляет времени порядка (Х+Y)
4. Соединение двух НЕ индексированных таблиц потребляет времени порядка (Х*Y)

о чем я писал https://infostart.ru/public/534444/

Поэтому почему "на хайлоаде Индексировать рекомендуется не использовать" мне не понятно.

Можете привести источник ?
Saint13; Redokov; +2 Ответить 1
11. Павел Жихарев (palsergeich) 14.08.17 14:43 Сейчас в теме
(10) Устные рекомендации по опыту внедрений больших проектов на семинарах в 1с.
По факту одна из рекомендаций, если это возможно, то и временные таблицы не использовать, потому что их создание - затрата ресурсов, в частности запись в оперативную память, или сразу в темп дб, если она большая, и если за время до сброса она не уничтожится, то сброс в темпДБ..
Индексировать, это по факту создание таблицы с кластерным (в 83) или не кластерным индексом (не 83) в оперативной памяти или темпДБ. А при большой загрузке на это может просто не хватить ресурсов, начнет кончатсся место в памяти, будут постоянные сбросы на диск и тд.
Если таблица маленькая, то затраты на индексирование не окупят выигрыша.
Если большая - то тоже.
В Вашей статье не написано сколько пользователей работало в момент эксперимента. То что будет хорошо работать при одном пользователе, не факт что будет хорошо работать хотя бы при 100 пользователях.
12. Ivan Khorkov (vano-ekt) 878 14.08.17 15:42 Сейчас в теме
верните меня лет на 10 назад, плюсану оттуда
Serj1C; ZOMI; JohnConnor; +3 Ответить 1
13. Алексей Пентюхин (pentaleksei) 14.08.17 15:57 Сейчас в теме
(11) Рекомендации не использовать индексы и временные таблицы?
Легко проверяется, что эти методы дают отличные возможности по оптимизации. Ускорение в разы при использовании временных таблиц. Главное найти узкое место в запросе.
По поводу индексирования: тоже все давно известно, зависит от типа данных и от возможности разложить эти данные по страницам индекса.
Если это булево: смысла делать индекс нет, а если ссылка и много записей в таблице - однозначно нужно делать.
Без обид, но комментарий теоретика.
Можно получить ссылку на устные рекомендации, очень интересно послушать?
14. Геннадий Николаев (genayo) 14.08.17 16:06 Сейчас в теме
(13) По поводу индексирования больших временных таблиц не может быть никакой однозначной рекомендации, в каждом конкретном случае нужно смотреть планы запроса, чтобы определить, поможет индексирование, или наоборот...
Gang031; andron77777; Irwin; +3 Ответить
15. Павел Жихарев (palsergeich) 14.08.17 16:16 Сейчас в теме
(13) 1) Не использовать временные таблицы, ЕСЛИ ЭТО ВОЗМОЖНО, а не неиспользовать вообще.
2) Не не использовать индексы, а не использовать ключевое слово ИНДЕКСИРОВАТЬ в запросе.
3) У меня сейчас онлайн 250 человек, результаты в тестовой и боевой базе иногда кардинально отличаются. Всего одному запросу из нескольких тысяч помогло использование ключевого слова ИНДЕКСИРОВАТЬ. В остальных случаях изъятие этого компонента из запроса или немного улучшает или значительно улучшает скорость выполнения запроса, и так же после массового изъятия из запросов нагрузка на СУБД снизилась и это уже практика.
4) Так же немало примеров, когда изъятие разбиения на пакеты значительно ускоряло выполнение запроса, но тут же опять есть рекомендация - не больше 5-8 соединений (в зависимости от источника). Опять же чистая практика.
16. Алексей Пентюхин (pentaleksei) 14.08.17 16:46 Сейчас в теме
(15) попробуйте добавить индекс на поля, которые далее используются в соединениях.
Иногда важен порядок индексирования во временных таблицах.

Из моей практики: перенос вложенных запросов во временную таблицу с индексированием дал прирост на проведении по партиям (УПП 1.3). Снижение на 40% времени проведения.

По индексированию: т.к. набор данных не статичен, данные меняются со временем, и возможно через пару лет работы все изменится как раз наоборот: индексирование ускорит запрос.
Я предпочитаю проиндексировать данные в запросе.
17. Геннадий Николаев (genayo) 14.08.17 16:50 Сейчас в теме
(16) Ну да, лучше проиндексировать - авось, через 10 лет поможет. Не очень профессиональный подход, на мой взгляд.
18. Алексей Пентюхин (pentaleksei) 14.08.17 16:56 Сейчас в теме
(17) однозначно без индексов?
Более профессионально объясните?
19. Геннадий Николаев (genayo) 14.08.17 19:58 Сейчас в теме
(18) Нет, конечно. В каждом конкретном случае индекс либо ускорит запрос, либо нет :)
20. Евгений Кабанов (kabanoff) 39 14.08.17 20:19 Сейчас в теме
Ну правильно. Запрос с отбором по большому списку работает медленнее, потому что на СУБД он преобразуется в запрос с условием "Table.Field IN (&N1, &N2, ..., &Nk)". Представляете, какой длины он будет при k = 10000? В данном случае соединение с временной таблицей по индексному полю будет гораздо шустрее.

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


то его производительность на больших объемах данных возрастет, т.к. оптимизатору СУБД будет проще построить оптимальный план.

И, как уже писали выше, в этом примере периодичность "Регистратор" не позволит использовать таблицу итогов. А это значит, что такой запрос всегда будет обращаться к таблице движений. Поскольку по составу выбираемых полей этого не требуется, здесь правильно поставить периодичность "Период".
21. Павел Жихарев (palsergeich) 14.08.17 20:48 Сейчас в теме
(20) Если я не ошибаюсь, то начиная со списков более 256 элементов оптимизатор переносит список в таблицу и делает отбор по соединению, в любом случае неясно что придет в голову оптимизатору и такие ситуации лучше описывать декларативно заранее.
И на сколько я помню таблица итогов начинает использоваться с периода МЕСЯЦ и более
22. Николай Васильев (vasilev2015) 276 14.08.17 20:54 Сейчас в теме
(12) Отличный юмор. Да, я безнадежно устарел )).
23. Николай Васильев (vasilev2015) 276 14.08.17 21:04 Сейчас в теме
(8) Честно, не знал что периодичность "регистратор" не использует итоги. Но звучит правдоподобно. Я заинтригован. А вот Вашего пессимизма про индексирование не разделяю.
24. Sergey Andreev (starik-2005) 1039 14.08.17 23:51 Сейчас в теме
(6)
а самое главное абсолютно бессмысленная
А когда в индексировании временной таблицы появляется смысл? Всегда хотел узнать...

ЗЫ: Индекс ведь зачем нужен? Чтобы быстрее найти данные в индексированной таблице. Если взять некую таблицу, то индекс по ней строится за (N * Log2(N))/2 итераций - просто формируем дерево btree, добавляя узел за количество итераций, не превышающих глубину дерева. дерево при этом медленно и верно растет, что в итоге выливается в то самое "/2" в среднем. Дальше по индексу мы найдем за Log2(N) в худшем случае, прочитав дерево на всю глубину. Т.е. в среднем тоже поделим на 2. В обычном неупорядоченном списке сканом мы будем искать за N/2 в среднем. Осталось найти разницу, преодолев которую индексировать становится дешевле. Есть мнение, что это 4 элемента. Уже при 4-х элементах время на индекс = 4 * 2 / 2 = 4, на поиск в среднем по 1 разу на элемент - еще 4. Т.е. 8 вычислений. Для обычного поиска мы найдем все значения за (1+2+3+4) = 10 раз. Т.е. 8 уже меньше 10 (но тут, фактически, паритет, т.к. мы еще потратим 4 на вставку во временную таблицу). Дальше обычный поиск растет линейно к размеру таблицы, а поиск по индексу - логарифмически. Это как бы намекает на то, что: во-первых, не все так радужно, как кажется, а, во-вторых, соединяться нужно по индексированным полям. Но если в соединяете что-то, выбрав все из временной таблицы, по полю, по которому во второй таблице нет индекса, то индексация временной таблицы Вам не поможет.
RailMen; ifilll; Dem1urg; DoctorRoza; +4 Ответить 1
25. Петр Базелюк (pbazeliuk) 1290 15.08.17 07:44 Сейчас в теме
(15) Пройдите очный курс 1С:Эксперт в Москве, они вам помогут разобраться в теме детально и давать обоснованные советы. Да и 250-500 пользователей одновременно онлайн это не хайлоад, платформа 1С отлично справляется.
JohnyDeath; baton_pk; +2 Ответить 1
26. Николай Васильев (vasilev2015) 276 15.08.17 09:56 Сейчас в теме
(24)
Но если в соединяете что-то, выбрав все из временной таблицы, по полю, по которому во второй таблице нет индекса, то индексация временной таблицы Вам не поможет.


Для достаточно больших таблиц, если обе таблицы содержат X, Y записей и не индексированы то соединение стоит X*Y. Если одна из них индексирована, то соединение стоит X*logY. Если обе индексированы - соединение стоит X+Y. Поэтому на мой взгляд, индексирование имеет смысл.
Скажите, Сергей, в чем я не прав ?
27. A X (ditp) 49 15.08.17 10:01 Сейчас в теме
(26)
Если обе индексированы - соединение стоит X+Y
почему?
28. Геннадий Николаев (genayo) 15.08.17 10:01 Сейчас в теме
(26) В теории все так, но на практике затраты на построение индекса могут превышать полученное уменьшение стоимости соединения. Ну и не стоит забывать про оптимизатор, именно поэтому в спорных случаях надо смотреть план запроса, прежде чем принимать решение об индексировании.
29. Андрей Аввакумов (itriot11) 7 15.08.17 10:54 Сейчас в теме
(8)
регистратор в таблице оборотов - это по сути дела двойной вложенный запрос
скажите, пожалуйста, а где об этом можно почитать? Беглое гугление результатов не дало.
30. Sergey Andreev (starik-2005) 1039 15.08.17 10:59 Сейчас в теме
(26) похоже на то. Два упорядоченных списка (читай "индекса") можно соединить за Х + У. Фактически мы просто проверяем, равен ли первый элемент первого списка первому элементу второго. Если равен - двигаем "курсор" обоих списков дальше, если нет - двигаем курсор списка, элемент которого меньше. Это применяется, в частности, для Reduce-шага в механизмах MapReduce.
31. Sergey Andreev (starik-2005) 1039 15.08.17 11:03 Сейчас в теме
(28)
в спорных случаях надо смотреть план запроса
План одного и того же запроса при высокой селективности отбора и при низкой его селективности может быть разным. Т.е. план зависит от данных - не стоит это забывать. И для компенсации такого поведения обычно в запрос добавляют какое-то поле, например (чтобы для отборов с разной селективностью из процедурного кеша извлекались разные планы запросов, а не один и тот же).
32. Павел Жихарев (palsergeich) 15.08.17 11:23 Сейчас в теме
(25) А что не так с советами?
И да, курс пройден.
250 пользователей на слабой машине, после изменения запросов, архитектуры и прочего от виртуалки без потери функциональности отщипнули ресурсов для других целей, количество пользователей постоянно растет, а выбить новые ресурсы очень сложно, и пока справляемся.
Я знаю что 250 это не хайлоад, но там уже база ведет себя не так, как при 50-100 пользователях.
33. sanek sanek_gk (sanek_gk) 66 15.08.17 11:45 Сейчас в теме
очередной срач к очередному "ПОЛЕЗНО"посту: я думал что нада делать так, а вот замерял и сделал по другому получилось вот так, а вот статья на итс там написано то то ... хм иногда делайте так иногда сяк . .. херак херак. Очень полезная статья! Удачи всем в своих замерах и экспериментах (хотел сказать автор в итоге)
34. Петр Базелюк (pbazeliuk) 1290 15.08.17 11:48 Сейчас в теме
(32) В вашем случае, советы, возможно, имеют место на жизнь. Но не стоит забывать, что есть различные поставщики баз данных, наличие DBA так же кардинально может изменить ситуацию (как пример - сжатие БД, распараллеливание записи в tempdb в соответствии с количеством ядер\процессоров и т. п. ).
У меня бывает до 500 одновременных сеансов и использование индексов намного дешевле чем их не использование, есть действительно запросы которые без индексов выполняются по 30-40 минут, с индексами 2-3 секунды. Ваши советы, не думаю, что будут полезны с большими таблицами (от 100 млн. записей) и размером БД более 1ТБ.
Могу предположить, по вашим словам, что вы упираетесь в дисковую подсистему и\или ОЗУ. И улучшение железа - дешевле чем оптимизация.
35. Александр Решетников (alres) 15.08.17 11:52 Сейчас в теме
РегистрНакопления.Продажи.Обороты() КАК ТоварыНаСкладахОстатки


Меня одного смутила выборка из оборотного регистра продаж под псевдонимом остатка товаров?
36. Петр Базелюк (pbazeliuk) 1290 15.08.17 12:01 Сейчас в теме
(32) Интересно, было бы, увидеть аналитику по ключевым операциям и расчет денежных потерь, "отказов" обслуживания клиентов по этим ключевым операциям.
37. Николай Васильев (vasilev2015) 276 15.08.17 16:19 Сейчас в теме
(33) Нет, Вы не так поняли. Я сделал "как в учебнике" и получилось хорошо. Никаких разночтений и шатаний.
38. Николай Васильев (vasilev2015) 276 15.08.17 16:22 Сейчас в теме
(35) Исправил псевдоним. Спасибо за внимательность.
39. Александр ШМ (ifilll) 16.08.17 10:25 Сейчас в теме
(7) Хорошо когда доступ к телу есть, к SQL то ))
40. Алексей (alest) 16.08.17 12:32 Сейчас в теме
Отбор по номенклатуре выдает 99% справочника - зачем удивляться, что условие ГДЕ по скорости примерно равно условию в виртуальной таблице?
А то, что номенклатура в списке ищется медленнее, чем в индексированной временной таблице, вполне может зависеть от версии СУБД. Не понятно, кстати, почему автор не пишет больше о условиях тестирования.
Оставьте свое сообщение