Волею судеб мне по работе пришлось посмотреть несколько занятий по подготовка к сертификации 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), чтобы предположить, что индекс всё-таки работает. А индекс по измерениям особенно сильно работает в варианте с таблицами.

Я не ставил себе целью разобраться во всех вариантах применения условий с таблицами и массивами, имя им — легион. Возможно, знания результатов разных вариантов даже принесут практическую пользу. Я также не исследовал причины полученных результатов — да, они тоже могут быть полезны, и, зная их, я наверняка бы нашёл ситуации, когда применение таблиц выигрывает по производительности. После трёх раундов статистика однозначна, а время ограничено, и приходится для себя запомнить только

Выводы

• Таблицы в условиях всё же проигрывают массивам. Всегда или почти всегда.

• Надо помнить, что в общем случае условия с таблицей и с массивами — разные условия, дающие разные результаты.

• Никто не отменял контекст: иногда применение таблицы даст готовый результат, а массивов — промежуточный, обработка которого нивелирует все преимущества.

Если у вас есть уточнения, дополнения и пожелания — буду рад увидеть в комментариях.