Волею судеб мне по работе пришлось посмотреть несколько занятий по подготовка к сертификации 1С:Специалист. И от одного лектора вдруг услышал, что использование в условиях виртуальной таблицы массивов, когда можно применить таблицу, — это плохо и медленно. А на сомнения слушателя он ответил: «Я гарантирую это». Думаю, ошибается человек, чего не бывает. И тут в другом занятии другой лектор говорит то же самое. Тут уже волей-неволей задумаешься: а вдруг я чего не помню уже? Но ведь не раз ускорял запросы, меняя таблицы на массивы. И захотелось проверить. Чем не повод для первой статьи на Хабре?
Итак, цель: выяснить, настолько ли категорично два условия в виртуальной таблице
ЗначИзмерения1 В (&Прм1) И ЗначИзмерения2 В (&Прм2)
медленнее одного сложного
(ЗначИзмерения1, ЗначИзмерения2) В (ВЫБРАТЬ Т.Кол1, Т.Кол2 ИЗ ТабПрм КАК Т)
Допущения и пояснения
Естественно, эти запросы разные по логике, и результаты могут сильно различаться, но при подготовке данных я постарался, чтобы больших различий не было.
Для подготовки параметров программе тоже требуется разное количество времени, и оно не учитывалось в эксперименте.
Замеры делались в 1С. Я понимаю, что в СУБД они будут технологически точнее, но дополнительные затраты 1С, я считаю, правильнее учитывать, так как реальное выполнение тоже идёт с ними. К тому же не забывайте, что 1С может работать и без внешней СУБД.
Многие для расследований сразу лезут в СУБД или технологический журнал, но мой подход другой: постараться сначала разобраться в 1С. В подавляющем большинстве случаев это получается. Для статьи я всё же использовал техжурнал для получения планов запросов консолью, но они приведены больше для информации.
Для эксперимента использовал базу 1С на MS SQL. В специально добавленный регистр накопления с тремя ссылочными измерениями и одним ресурсом я добавил около 6 миллионов записей с повторяющимися по два раза комбинациями. Итого в таблице остатков было около 3 миллионов записей.
Первый раунд
В первом раунде в параметры вошло по одному значению из второго и третьего измерения. Эти измерения не индексируются автоматически. В результате должно быть выбрано около 300 записей.
Тексты запросов:
С двойной проверкой:
Запрос.Текст = "ВЫБРАТЬ | ТЕСТОстатки.Сотр КАК Сотр, | ТЕСТОстатки.Долж КАК Долж, | ТЕСТОстатки.Страна КАК Страна, | ТЕСТОстатки.ЦифраОстаток КАК ЦифраОстаток |ИЗ | РегистрНакопления.ТЕСТ.Остатки( | , | Сотр В (&Сотр) | И Страна В (&Страна)) КАК ТЕСТОстатки";
С проверкой по таблице:
Запрос.Текст = "ВЫБРАТЬ | ТЕСТОстатки.Сотр КАК Сотр, | ТЕСТОстатки.Долж КАК Долж, | ТЕСТОстатки.Страна КАК Страна, | ТЕСТОстатки.ЦифраОстаток КАК ЦифраОстаток |ИЗ | РегистрНа��опления.ТЕСТ.Остатки( | , | (Сотр, Страна) В | (ВЫБРАТЬ | Т.Сотр, | Т.Страна | ИЗ | ВТ_СотрСтрана КАК Т)) КАК ТЕСТОстатки";
При беглом анализе сравнения производительности вариантов запросов я обычно думаю, а как бы я сделал эти варианты кодом и какой из них будет быстрее выполняться? Так вот, в этом случае всё практически одинаково: перебор регистра, внутри цикла — перебор массивов или таблицы из одного элемента. Сравнения — те же самые. Вот и СУБД подтверждает одинаковость планом запроса (кроме одной строки). Я в эту строку поместил рядом два варианта:
SELECT T1.Период, T1._UseTotals, T1._ActualPeriod, T1._UseSplitter, T1._MinPeriod, T1._MinCalculatedPeriod FROM T1 WHERE ((T1.ОбластьДанныхОсновныеДанные = ?)) AND (T1._RegID = ? AND T1.ОбластьДанныхОсновныеДанные = ?) p_0: 0N p_1: 0x688664E7FC784D4AA95363FC95100460 p_2: 0N
SELECT T1.Fld73188RRef, T1.Fld73189RRef, T1.Fld73190RRef, T1.Fld73191Balance_ FROM (SELECT T2.Сотр AS Fld73188RRef, T2.Долж AS Fld73189RRef, T2.Страна AS Fld73190RRef, CAST(SUM(T2.Цифра) AS NUMERIC(16, 0)) AS Fld73191Balance_ FROM T2 (массивы): 🡺 WHERE ((T2.ОбластьДанныхОсновныеДанные = ?)) AND (T2.Период = ? AND ((T2.Сотр IN (0x00000000000000000000000000000000)) AND (T2.Страна IN (0x00000000000000000000000000000000))) AND (T2.Цифра <> ?) AND (T2.Цифра <> ?))🡸 (таблица): 🡺 WHERE ((T2.ОбластьДанныхОсновныеДанные = ?)) AND (T2.Период = ? AND (EXISTS(SELECT 1 FROM ВременнаяТаблица16 T3 WITH(NOLOCK) WHERE (T2.Сотр = T3._Q_001_F_000RRef) AND (T2.Страна = T3._Q_001_F_001RRef))) AND (T2.Цифра <> ?) AND (T2.Цифра <> ?)) 🡸 GROUP BY T2.Сотр, T2.Долж, T2.Страна HAVING (CAST(SUM(T2.Цифра) AS NUMERIC(16, 0))) <> 0.0) T1 p_0: 0N p_1: 59991101000000 p_2: 0N p_3: 0N
В варианте с массивами в этой строке только проверка, а в варианте с таблицей — маленький подзапрос. Казалось бы, разницы быть не должно. Но…
Вариант с массивами 🡺 23 мс
Вариант с таблицей 🡺 43 мс
Неожиданно! Тот вариант, который «гарантированно» лучший, — проиграл вдвое! Видимо, построение запроса таки съедает время?
Выберу в параметрах уникальные ссылки из единственной записи.
Вариант с массивами 🡺 11 мс
Вариант с таблицей 🡺 22 мс
Разница сказалась, но не на соотношении. Может, я под свою правоту угадал контекст?
Второй раунд
Добавлю «НЕ» к этой единственной записи. Результаты огромны, условие «плохое», нагрузка серьёзная… Текст условия с массивами:
Текст условия с массивами:
НЕ Сотр В (&Сотр) ИЛИ НЕ Страна В (&Страна) И сразу попробую и такой вариант — а вдруг разница будет? НЕ(Сотр В (&Сотр) И НЕ Страна В (&Страна))
Текст условия с таблицей:
НЕ (Сотр, Страна) В (ВЫБРАТЬ Т.Сотр, Т.Страна ИЗ ВТ_СотрСтрана КАК Т)
Результаты:
Вариант с массивами (И) 🡺 12589 мс
Вариант с таблицей 🡺 11757 мс
Ну вот, наконец-то победа таблицы! Но…
Вариант с массивами (ИЛИ) 🡺 11213 мс!
Но тут уже почти равенство! Попробуем уйти в сторону индексов и усиления параметра.
Третий раунд
Сделал выборку в 500 записей, из неё сделал массивы и три таблицы по две колонки: взял каждую пару измерений. Напомню, что измерения индексируются по очереди единым индексом (новых возможностей в подручной платформе не было). Кроме того, — а вдруг поможет? — в дополнительном варианте я проиндексировал и таблицу-параметр.
Старт!
Вариант с массивами (И12) 🡺 19 мс
Вариант с массивами (И13) 🡺 103 мс
Вариант с массивами (И23) 🡺 828 мс
Вариант с таблицей (И12 индекс) 🡺 31 мс
Вариант с таблицей (И12) 🡺 39 мс
Вариант с таблицей (И13) 🡺 172 мс
Вариант с таблицей (И23) 🡺 6464 мс
Итоги опять печальны для таблиц. Но неожиданно для меня сработал индекс таблицы-параметра. Разница достаточно велика (31 и 39), чтобы предположить, что индекс всё-таки работает. А индекс по измерениям особенно сильно работает в варианте с таблицами.
Я не ставил себе целью разобраться во всех вариантах применения условий с таблицами и массивами, имя им — легион. Возможно, знания результатов разных вариантов даже принесут практическую пользу. Я также не исследовал причины полученных результатов — да, они тоже могут быть полезны, и, зная их, я наверняка бы нашёл ситуации, когда применение таблиц выигрывает по производительности. После трёх раундов статистика однозначна, а время ограничено, и приходится для себя запомнить только
Выводы
Таблицы в условиях всё же проигрывают массивам. Всегда или почти всегда.
Надо помнить, что в общем случае условия с таблицей и с массивами — разные условия, дающие разные результаты.
Никто не отменял контекст: иногда применение таблицы даст готовый результат, а массивов — промежуточный, обработка которого нивелирует все преимущества.
Если у вас есть уточнения, дополнения и пожелания — буду рад увидеть в комментариях.
