В данной статье я постараюсь дать развернутый анализ наиболее интересных решений с технологической точки зрения решений, присланных на конкурс разработчиков от компании "Первый БИТ" делаемпроекты.рф.
Данный конкурс проводился в рамках первой всероссийской конференции INFOSTART EVENT 2012 (ссылка на отчет //infostart.ru/public/162790/). В конкурсе было две номинации – «знание механизма РАУЗ в УПП» и «разработка в системе 1С:Предприятие». Результаты конкурса опубликованы здесь и здесь. Конкурс проходил в два этапа. Первый этап - соревнование на скорость проходил в режиме реального времени, а второй этап - конкурс на самое оптимальное решение проходил уже в оффлайн режиме и решение присылались на мою на электронную почту. В статье проводится разбор решений присланных на второй этап.
Для начала, давайте посмотрим еще раз на задание из конкурса:
Требуется разработать внешнюю обработку, которая сможет найти все характеристики номенклатуры, которые полностью соответствуют указанному набору свойств и их значениям (Характеристика должна одновременно содержать все указанные свойства и их значения и кроме них не должна содержать других свойств с заполненными значениями).
Работать мы будем со справочником "Характеристики" в Управление Торговлей 11 для России (это важно). Ниже показан скриншот со структурой справочника
Полностью задание звучит следующим образом (полное описание задания можно скачать в файлах статьи):
Требуется разработать внешнюю обработку, которая будет содержать две табличные части и кнопку"Найти". Пример обработки указан на скриншоте ниже (саму обработку шаблон можно скачать в файлах статьи).
- В первой табличной части необходимо задать условия поиска.
- Во второй нужно выводить результат поиска.
В результате поиска нужно найти все характеристики номенклатуры, которые полностью соответствую указанному набору свойств и их значениям. Характеристика должна одновременно содержать все указанные свойства и их значения и кроме них не должна содержать других свойств с заполненными значениями. Предусматривается следующие ограничения:
- при вводе параметров поиска (в левой таблице) не должно быть свойств без указания значений.
- нельзя использовать сравнение на количество строк
SIC! Прежде чем читать дальше, еще раз внимательно прочитайте условие задачи и постарайтесь ее мысленно решить. На ней погорело очень много кандидатов, считающих себя хорошими программистами с высоким уровнем логического мышления.
Исходя из задания нам нужно работать с двумя таблицами - эталонной и табличной частью "ДополнительныеРеквизиты" справочника характеристики номенклатуры. Основная хитрость задачи состоит в том, что нужно выбрать все характеристики попадающие под наши условия и проверить, чтобы в них не было лишних заполненных свойств.
Для тестирования присланных решений была создана специальная база - были растиражированы следующие элементы:
1) Характеристика с 4 свойствами: размер - 35, цвет – зеленый, тип кожи – натуральная, полнота – 5 . Количество 20 000 элементов. (Тест №1)
2) Характеристика с 4 пустыми свойствами: размер, цвет, тип кожи и полнота и дополнительно к ним 100 свойств строкового типа со значением номера свойства (т.е. Свойство1 – значение «1», Свойство2 - значение «2»). Количество 5 000 элементов. (Тест №2)
Все запросы приведены «как есть». Время выполнения тестов для каждого из решений приведены для моего личного ноутбука и локального SQL сервера:
Технические характеристики ноутбука - Процессор Intel® Core™ i5-430M 2,26 ГГц, RAM - 4GB DDR3, HDD Sata 320 Gb
Версия SQL - Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64) (Build 7601: Service Pack 1)
Версия 1С - 8.2.17.368 (клиент-серверная - x86-64)
P.S. Предвосхищая вопрос по коду :) - метод ТекущаяУниверсальнаяДатаВМиллисекундах() появился в 8.2.17
Решение №1.
СписокПустыхЗначений = Новый СписокЗначений;
МассивТипов = Новый Массив(1);
Для каждого Тип Из Метаданные.ПланыВидовХарактеристик.ДополнительныеРеквизитыИСведения.Тип.Типы() Цикл
Если Тип = Тип("Булево") Тогда
Продолжить; // Для типа булево нет пустых значений
КонецЕсли;
МассивТипов[0] = Тип;
ОписаниеТипа = Новый ОписаниеТипов(МассивТипов);
СписокПустыхЗначений.Добавить(ОписаниеТипа.ПривестиЗначение());
КонецЦикла;
СписокПустыхЗначений.Добавить(Неопределено);
Запрос = Новый Запрос;
Запрос.Текст = "
|ВЫБРАТЬ
| Свойство КАК Свойство
| ,Значение КАК Значение
|
|ПОМЕСТИТЬ
| ЗаданныеСвойства
|
|ИЗ
| &Свойства КАК Свойства
|
|;
|
|ВЫБРАТЬ
| Характеристики.Ссылка КАК Характеристика
|
|ИЗ
| Справочник.ХарактеристикиНоменклатуры КАК Характеристики
|
|ЛЕВОЕ СОЕДИНЕНИЕ
| ПланВидовХарактеристик.ДополнительныеРеквизитыИСведения КАК План
|
|ПО
| ИСТИНА
|
|ЛЕВОЕ СОЕДИНЕНИЕ
| Справочник.ХарактеристикиНоменклатуры.ДополнительныеРеквизиты КАК Реквизиты
|
|ПО
| План.Ссылка = Реквизиты.Свойство
| И Характеристики.Ссылка = Реквизиты.Ссылка
|
|ЛЕВОЕ СОЕДИНЕНИЕ
| ЗаданныеСвойства
|
|ПО
| План.Ссылка = ЗаданныеСвойства.Свойство
|
|СГРУППИРОВАТЬ ПО
| Характеристики.Ссылка
|
|ИМЕЮЩИЕ
| МИНИМУМ(
| ВЫБОР
| КОГДА ЕСТЬNULL(Реквизиты.Значение, ЗаданныеСвойства.Значение) ЕСТЬ NULL ТОГДА Истина
| КОГДА ЗаданныеСвойства.Значение ЕСТЬ NULL И Реквизиты.Значение В (&СписокПустыхЗначений) ТОГДА Истина
| КОГДА Реквизиты.Значение = ЗаданныеСвойства.Значение ТОГДА Истина
| ИНАЧЕ Ложь
| КОНЕЦ) = Истина
|";
Запрос.УстановитьПараметр("Свойства", Объект.Свойства.Выгрузить());
Запрос.УстановитьПараметр("СписокПустыхЗначений", СписокПустыхЗначений);
НачДата=ТекущаяУниверсальнаяДатаВМиллисекундах();
Результат = Запрос.Выполнить();
КонДата=ТекущаяУниверсальнаяДатаВМиллисекундах();
ВремяВыполнения = КонДата-НачДата;
Сообщить("Время выполнения - "+ВремяВыполнения+" миллисекунд");
Разбор решения №1.
Решение построено на следующем принципе:
(1) Получаем декартово произведение справочника характеристики номенклатуры и ПВХ "ДопРеквизитыИСведения". У нас к каждой строке из справочника характеристики соединяются все свойства из ПВХ "ДопРеквизитыИСведения". Т.е. в таблице получаем все возможные комбинации характеристики и ее возможных свойств. Ориентировочное число строк таблицы ~ 3 125 000 (примерно 25000 элементов справочника на примерно 125 свойств)
(2) К этой таблице (1) присоединяем табличную часть каждой характеристики. Ориентировочное число строк таблицы не меняется ~ 3 125 000
(3) К полученной таблице (2) присоединяем эталонную табличную часть со связью по свойствам из (1)
Дальше группируем по характеристике с определенными условиями:
- Оба свойства (в эталонной и в табличной части) отсутствуют (NULL), т.е. не заданы
- Когда в эталонной таблице значение отсутствует (NULL), а в табличной части не задано (проверяется на соответствие пустых типов) (&СписокПустыхЗначений)
- Оба значения равны по значению
1) Общий ПВЗ (здесь и далее план выполнения запроса).
1.а) Разберем подробно первую часть ПВЗ:
Здесь оптимизатор SQL берет нашу НЕ индексированную эталонную таблицу (#tt7) и сливает с ПВХ "ДопРеквизитыИСведения". Всего свойств у нас 119 в #tt7 для 2 теста загоняется 100 свойств - итого на выходе получаем таблицу в 119 свойств. Пока все еще нормально :)
1.б) Вторая часть
Здесь оптимизатор SQL загоняет во временную таблицу результат действия из 1.а) и далее делает совершенно логичный Nested Loops, т.е. как раз наше декартово произведение с итоговым результатом в 2 985 115 строк (25085 элементов на 119 свойств). Вообщем-то что было написано, то и получили :)
1.в) Ну и напоследок тоже достаточно интересный кусок, когда мы к нашей трехмиллионой таблице добавляем данные с табличным частями
Попадаем в кластерный индекс при поиске, но это нас не спасает - все равно на это левое соединение слиянием тратим 41% всего запроса. На выходе получаем опять 3 миллионную таблицу (с еще большим количеством колонок - размер таблицы на выходе 325 Мб). Ну и потом 4% времени тратим на расчет агрегата (на трех миллионах строк-то :))
В итоге данное решение задачи при всей своей неоднозначности на 2 тесте показывает очень достойные 30,614 секунд, но к сожалению, данное решение полностью проваливается на 1 тесте - 38,798 секунд (что вполне логично, так как первое декартово произведение делается без всякого условия).
Решение №2.
Запрос = Новый Запрос;
Запрос.УстановитьПараметр("ЗаданныеСвойства", Объект.Свойства.Выгрузить());
Запрос.Текст =
"ВЫБРАТЬ
| ЗаданныеСвойства.Свойство КАК Свойство,
| ЗаданныеСвойства.Значение КАК Значение
|ПОМЕСТИТЬ ВТЗаданныеСвойства
|ИЗ
| &ЗаданныеСвойства КАК ЗаданныеСвойства
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| ДополнительныеРеквизитыИСведения.Ссылка КАК Ссылка,
| ДополнительныеРеквизитыИСведения.ТипЗначения
|ПОМЕСТИТЬ ВТНенужныеСвойства
|ИЗ
| ПланВидовХарактеристик.ДополнительныеРеквизитыИСведения КАК ДополнительныеРеквизитыИСведения
|ГДЕ
| НЕ ДополнительныеРеквизитыИСведения.Ссылка В
| (ВЫБРАТЬ
| ВТЗаданныеСвойства.Свойство
| ИЗ
| ВТЗаданныеСвойства)
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| ХарактеристикиНоменклатуры.Ссылка КАК Ссылка
|ПОМЕСТИТЬ ВТХарактеристикиДляАнализа
|ИЗ
| Справочник.ХарактеристикиНоменклатуры КАК ХарактеристикиНоменклатуры
|ГДЕ
| НЕ ХарактеристикиНоменклатуры.ДополнительныеРеквизиты.Свойство В
| (ВЫБРАТЬ
| ВТНенужныеСвойства.Ссылка
| ИЗ
| ВТНенужныеСвойства)
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| ХарактеристикиНоменклатурыДополнительныеРеквизиты.Свойство,
| ХарактеристикиНоменклатурыДополнительныеРеквизиты.Значение,
| ВТХарактеристикиДляАнализа.Ссылка
|ПОМЕСТИТЬ ВТСвойстваДляАнализа
|ИЗ
| ВТХарактеристикиДляАнализа КАК ВТХарактеристикиДляАнализа
| ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.ХарактеристикиНоменклатуры.ДополнительныеРеквизиты КАК ХарактеристикиНоменклатурыДополнительныеРеквизиты
| ПО ВТХарактеристикиДляАнализа.Ссылка = ХарактеристикиНоменклатурыДополнительныеРеквизиты.Ссылка
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| ВТЗаданныеСвойства.Свойство,
| ВТЗаданныеСвойства.Значение,
| ВТХарактеристикиДляАнализа.Ссылка КАК Характеристика
|ПОМЕСТИТЬ ВТЭталонныеХарактеристики
|ИЗ
| ВТЗаданныеСвойства КАК ВТЗаданныеСвойства
| ЛЕВОЕ СОЕДИНЕНИЕ ВТХарактеристикиДляАнализа КАК ВТХарактеристикиДляАнализа
| ПО (ИСТИНА)
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| ВЛОЖЕННЫЙ.Характеристика КАК Характеристика,
| ВЛОЖЕННЫЙ.Счетчик
|ИЗ
| (ВЫБРАТЬ
| СУММА(ВЫБОР
| КОГДА ВТСвойстваДляАнализа.Свойство ЕСТЬ NULL
| ТОГДА 1
| ИНАЧЕ 0
| КОНЕЦ) КАК Счетчик,
| ВТЭталонныеХарактеристики.Характеристика КАК Характеристика
| ИЗ
| ВТЭталонныеХарактеристики КАК ВТЭталонныеХарактеристики
| ЛЕВОЕ СОЕДИНЕНИЕ ВТСвойстваДляАнализа КАК ВТСвойстваДляАнализа
| ПО ВТЭталонныеХарактеристики.Свойство = ВТСвойстваДляАнализа.Свойство
| И ВТЭталонныеХарактеристики.Значение = ВТСвойстваДляАнализа.Значение
| И ВТЭталонныеХарактеристики.Характеристика = ВТСвойстваДляАнализа.Ссылка
|
| СГРУППИРОВАТЬ ПО
| ВТЭталонныеХарактеристики.Характеристика) КАК ВЛОЖЕННЫЙ
|ГДЕ
| ВЛОЖЕННЫЙ.Счетчик = 0";
НачДата=ТекущаяУниверсальнаяДатаВМиллисекундах();
Результат = Запрос.Выполнить();
КонДата=ТекущаяУниверсальнаяДатаВМиллисекундах();
ВремяВыполнения = КонДата-НачДата;
Сообщить("Время выполнения - "+ВремяВыполнения+" миллисекунд");
Разбор решения №2.
Решение построено на следующем принципе:
(1) Сначала формируем временную таблицу со всеми свойствами характеристиками НЕ заданными в эталонной таблице
(2) Затем формируем вторую временную таблицу с характеристиками у которых свойства НЕ попадают в таблицу 1
(3) Формируем третью временную таблицу с табличной частью характеристик для характеристик из таблицы 2
(4) В таблице 4 опять наше любимое декартово произведение выбранных характеристик (2) и эталонной таблицы (опять 5000 * 100 ~ 500 000 строк)
(5) Далее соединяем (3) и (4) таблицы во ВЛОЖЕННОЙ таблице и сворачиваем
В запросе очевидные проблемы с производительностью. Во-первых, ни одна из временных таблиц не индексирована (это заставляет оптимизатор делать table scan'ы временных таблиц, см. далее). Во-вторых, опять декартово произведение.
Посмотрим на ПВЗ. Вообще 1С отправляет каждую инструкцию запроса отдельно (sic!), поэтому понять стоимость каждого отдельного запроса по отношению к пакету тяжело.
(1) Для меня было опять же небольшим открытием, что временная таблица заполняемая по таблице значений формируется построчно, но SQL Profiler все расставил на свои места и действительно первая временная таблица формируется построчно (#tt14)
(2) Во ПВЗ второго запроса - во первых есть Nested Loops в виде Left Anti Semi Join (Оператор Left Anti Semi Join возвращает каждую строку первого (верхнего) входа, для которой нет соответствующей строки во втором (нижнем) входе. Если в столбце Argument предиката соединения нет, то каждая строка будет считаться подходящей. Left Anti Semi Join is a logical operator.">Left Anti Semi Join является логическим оператором. - http://msdn.microsoft.com/ru-ru/library/ms191158.aspx). У нас предиката нет - поэтому будет по факту nested loops. Сортировка в нижней части запроса с моей точки зрения вызвана тем, что временная таблица #tt14 не проиндексирована. Сам по себе запрос копеечный, так что бог с ним.
(3) Тут все нормально.
(4) Декартово произведение (nested loops в виде left outer join) на 500 000 строк. Что удивительно дольше всего занимает вставка во временную таблицу (sic!)
(5) Завершающий аккорд. Два TABLE SCAN на 500 000 с каждой стороны с сортировкой (индекса то нет) и соединением этих таблиц. Я где-то упустил ПВЗ для заполнения #tt18 тоже на 500 000 строк, но сути дела это не поменяет.
В итоге задачка решена верно, но есть проблемы с производительностью на 2 тесте. Разовое время выполнения 2 теста - 61,170 секунд, время выполнения 1 теста - 9,526 секунд.
Решение №3
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| СвойстваЗначения.Свойство,
| СвойстваЗначения.Значение
|ПОМЕСТИТЬ СвойстваЗначения
|ИЗ
| &СвойстваЗначения КАК СвойстваЗначения
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| ХарактеристикиНоменклатуры.Ссылка КАК Характеристика,
| МИНИМУМ(ХарактеристикиНоменклатуры.Исключаем) КАК Исключаем
|ИЗ
| (ВЫБРАТЬ РАЗЛИЧНЫЕ
| ХарактеристикиНоменклатурыДополнительныеРеквизиты.Ссылка КАК Ссылка,
| ВЫБОР
| КОГДА Свойства.Свойство ЕСТЬ NULL
| ИЛИ Свойства.Значение ЕСТЬ NULL
| ТОГДА ""Исключаем""
| ИНАЧЕ ""Оставляем""
| КОНЕЦ КАК Исключаем
| ИЗ
| Справочник.ХарактеристикиНоменклатуры.ДополнительныеРеквизиты КАК ХарактеристикиНоменклатурыДополнительныеРеквизиты
| ЛЕВОЕ СОЕДИНЕНИЕ СвойстваЗначения КАК Свойства
| ПО ХарактеристикиНоменклатурыДополнительныеРеквизиты.Свойство = Свойства.Свойство
| И (ХарактеристикиНоменклатурыДополнительныеРеквизиты.Значение = Свойства.Значение
| ИЛИ ХарактеристикиНоменклатурыДополнительныеРеквизиты.Значение ЕСТЬ NULL
| ИЛИ ХарактеристикиНоменклатурыДополнительныеРеквизиты.Значение = НЕОПРЕДЕЛЕНО
| ИЛИ ХарактеристикиНоменклатурыДополнительныеРеквизиты.Значение = ЗНАЧЕНИЕ(Справочник.ЗначенияСвойствОбъектов.ПустаяСсылка))
|
| ОБЪЕДИНИТЬ ВСЕ
|
| ВЫБРАТЬ
| СвойстваЗначения.Ссылка,
| ВЫБОР
| КОГДА ХарактеристикиНоменклатуры.Свойство ЕСТЬ NULL
| ТОГДА ""Исключаем""
| ИНАЧЕ ""Оставляем""
| КОНЕЦ
| ИЗ
| (ВЫБРАТЬ РАЗЛИЧНЫЕ
| ХарактеристикиНоменклатуры.Ссылка КАК Ссылка,
| СвойстваЗначения.Свойство КАК Свойство,
| СвойстваЗначения.Значение КАК Значение
| ИЗ
| СвойстваЗначения КАК СвойстваЗначения
| ЛЕВОЕ СОЕДИНЕНИЕ Справочник.ХарактеристикиНоменклатуры КАК ХарактеристикиНоменклатуры
| ПО (ИСТИНА)) КАК СвойстваЗначения
| ЛЕВОЕ СОЕДИНЕНИЕ Справочник.ХарактеристикиНоменклатуры.ДополнительныеРеквизиты КАК ХарактеристикиНоменклатуры
| ПО (ХарактеристикиНоменклатуры.Ссылка = СвойстваЗначения.Ссылка)
| И СвойстваЗначения.Свойство = ХарактеристикиНоменклатуры.Свойство
| И (СвойстваЗначения.Значение = ХарактеристикиНоменклатуры.Значение
| ИЛИ ХарактеристикиНоменклатуры.Значение ЕСТЬ NULL
| ИЛИ ХарактеристикиНоменклатуры.Значение = НЕОПРЕДЕЛЕНО
| ИЛИ ХарактеристикиНоменклатуры.Значение = ЗНАЧЕНИЕ(Справочник.ЗначенияСвойствОбъектов.ПустаяСсылка))) КАК ХарактеристикиНоменклатуры
|
|СГРУППИРОВАТЬ ПО
| ХарактеристикиНоменклатуры.Ссылка
|
|ИМЕЮЩИЕ
| МИНИМУМ(ХарактеристикиНоменклатуры.Исключаем) = ""Оставляем""";
ЭтотОбъект = РеквизитФормыВЗначение("Объект");
Запрос.УстановитьПараметр("СвойстваЗначения", ЭтотОбъект.Свойства.Выгрузить());
НачДата=ТекущаяУниверсальнаяДатаВМиллисекундах();
Результат = Запрос.Выполнить();
КонДата=ТекущаяУниверсальнаяДатаВМиллисекундах();
ВремяВыполнения = КонДата-НачДата;
Сообщить("Время выполнения - "+ВремяВыполнения+" миллисекунд");
Разбор решения №3.
Решение №3 определенно похоже на решение №1, но объединяет результаты двух запросов:
(1) Левое соединение ТЧ характеристик с эталонной таблице по свойству и значению
(2) "Привычное" декартово произведение эталонной таблицы и ВСЕХ характеристик с их табличными частями. Потом к этой таблице присоединяем табличную часть характеристик и определяем характеристики с лишними характеристиками. Ориентировочная оценка количества строк в таблице ~ 2 500 000 (100 строк эталонной ТЧ * 25 000 элементов справочника).
Как обычно смотрим ПВЗ.
(2а)
(2б)
В ПВЗ (2а) и (2б) видно как раскладывает выполнение этих запросов SQL. В (2а) вверху 1 запрос - затраты на него нулевые, внизу 2 запрос объединения - 50% затрат уходят на сортировку "огромной" (2 508 500 строк по профайлеру) таблицы полученной декартовым произведением эталона со справочником характеристик для соединения с табличной частью свойств (_Reference114_VT1317). 25% уходит на распараллеливание потоков связанных с обработкой этой таблицы. И еще 11% уже в (2б) на расчет агрегата после объединения. Что удивительно NESTED LOOPS декартова произведения достаточно мало затратен - 1%. Ну а в итоге решение №3 показывает достаточно слабый результат во 2 тесте - 66,292 секунд. Но (!) показывает впечатляющий результат на 1 тесте - 1,648 секунд!
P.S. Не удержался и решил все-таки посмотреть ПВЗ решения №3 на 1 тесте, чтобы понять откуда все-таки берется эта 1 секунда.
Первое, что необычное бросилось в глаза - это создание Bitmap индекса, а все остальное вроде ожидаемо - 580 000 строк в первой таблице (5 000 элементов * 100 свойств для 2 теста + 20 000 элементов * 4 свойства для 1 теста) и 100 000 строк во второй таблице (4 строки эталонной на 25 000 характеристик). Не очень понятно пока за счет чего 1 секунда, но факт остается фактом - 1 секунда :)
Решение №4
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| ТЗСвойств.Свойство КАК Свойство,
| ТЗСвойств.Значение КАК Значение
|ПОМЕСТИТЬ СВОЙСТВА
|ИЗ
| &ТЗСвойств КАК ТЗСвойств
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ РАЗЛИЧНЫЕ
| Характеристики.Ссылка КАК Характеристика
|ПОМЕСТИТЬ ВозможныеХарактеристики
|ИЗ
| Справочник.ХарактеристикиНоменклатуры.ДополнительныеРеквизиты КАК Характеристики
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| ВозможныеХарактеристики.Характеристика,
| СВОЙСТВА.Свойство,
| СВОЙСТВА.Значение
|ПОМЕСТИТЬ НАБОР
|ИЗ
| СВОЙСТВА КАК СВОЙСТВА,
| ВозможныеХарактеристики КАК ВозможныеХарактеристики
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| ВозможныеХарактеристики.Характеристика
|ИЗ
| ВозможныеХарактеристики КАК ВозможныеХарактеристики
| ЛЕВОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ РАЗЛИЧНЫЕ
| Свойства.Ссылка КАК Характеристика
| ИЗ
| Справочник.ХарактеристикиНоменклатуры.ДополнительныеРеквизиты КАК Свойства
| ЛЕВОЕ СОЕДИНЕНИЕ НАБОР КАК НАБОР
| ПО Свойства.Ссылка = НАБОР.Характеристика
| И Свойства.Свойство = НАБОР.Свойство
| И Свойства.Значение = НАБОР.Значение
| ГДЕ
| НАБОР.Характеристика ЕСТЬ NULL ) КАК СЛишнимиСвойствами
| ПО ВозможныеХарактеристики.Характеристика = СЛишнимиСвойствами.Характеристика
| ЛЕВОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ РАЗЛИЧНЫЕ
| НАБОР.Характеристика КАК Характеристика
| ИЗ
| НАБОР КАК НАБОР
| ЛЕВОЕ СОЕДИНЕНИЕ Справочник.ХарактеристикиНоменклатуры.ДополнительныеРеквизиты КАК Свойства
| ПО НАБОР.Характеристика = Свойства.Ссылка
| И НАБОР.Свойство = Свойства.Свойство
| И НАБОР.Значение = Свойства.Значение
| ГДЕ
| Свойства.Ссылка ЕСТЬ NULL ) КАК СНедостающимиСвойствами
| ПО ВозможныеХарактеристики.Характеристика = СНедостающимиСвойствами.Характеристика
|ГДЕ
| СЛишнимиСвойствами.Характеристика ЕСТЬ NULL
| И СНедостающимиСвойствами.Характеристика ЕСТЬ NULL ";
Обработка = РеквизитФормыВЗначение("Объект");
Запрос.УстановитьПараметр("ТЗСвойств", Обработка.Свойства.Выгрузить(, "Свойство, Значение"));
НачДата=ТекущаяУниверсальнаяДатаВМиллисекундах();
Результат = Запрос.Выполнить();
КонДата=ТекущаяУниверсальнаяДатаВМиллисекундах();
ВремяВыполнения = КонДата-НачДата;
Сообщить("Время выполнения - "+ВремяВыполнения+" миллисекунд");
Разбор решения №4
Пройдемся по технологии:
(1) Эталонная таблица
(2) Все характеристики - таблица 25 тыс. строк
(3) Декартово произведение всех характеристик и свойств - таблица 2,5 млн. строк - НАБОР
(4) Левое соединение таблицы (2) с двумя подзапросами (4-1) - Табличные части характеристик левое соединение с (3) таблицей и (4-2) - (3) таблица левое соединение с табличной частью характеристик. В итоге в (4) таблице получаем данные какие свойства для характеристик лишние и недостающие и получаем нужный результат. Интересно, что в обоих подзапросах работает НЕ индексированная таблица (3) размером 2,5 млн. строк
Данное решение в итоге выливается в длинный ПВЗ, который не влазит на экран, но мы рассмотрим самую правую часть ПВЗ (его начало), так как в ней сосредоточены основные затраты.
На ПВЗ одинарным подчеркиванием отмечены действия с таблицей в 2,5 млн строк, а двойным подчеркиванием с таблицей в 0,5 млн строк. Опять привычные 40% на сортировку таблицы в 2,5 млн строк. В итоге решение собирает в себе недостатки производительности решений, рассмотренных до этого, и показывает время на 2 тесте - 120,145 секунд! Но(!), как было сказано вначале статьи, текущие замеры времении показываются по моему ноутбуку, а оценка результатов происходила на тестовом сервере. Каждое решение запускалось 10 раз и выбирались 3 минимальных времени выполнения каждого, так вот это решение на 2 тесте несколько раз показывало уровень быстродействия решения №1, я предполагаю, что SQL в тот момент как-то использовало свой кэш,но увы - получить ПВЗ для быстрого варианта выполнения решения №4 мне не удалось. А на первом тесте решение показывает очень достойный результат в 5,321 секунд.
Решение №5
Запрос = Новый Запрос;
//Считаем, что если свойство не указано - тогда строчка не вноситься в табличную часть "Дополнительные реквизиты".
Запрос.Текст =
"ВЫБРАТЬ
| Таблица.Свойство КАК Свойство,
| Таблица.Значение
|ПОМЕСТИТЬ Таблица
|ИЗ
| &Свойства КАК Таблица
|
|ИНДЕКСИРОВАТЬ ПО
| Свойство
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| Спр.Ссылка КАК Ссылка,
| Спр.Свойство КАК Свойство,
| Спр.Значение
|ПОМЕСТИТЬ Справочник
|ИЗ
| Справочник.ХарактеристикиНоменклатуры.ДополнительныеРеквизиты КАК Спр
|
|ИНДЕКСИРОВАТЬ ПО
| Свойство,
| Ссылка
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| ХарактеристикиНоменклатуры.Ссылка КАК Характеристика
|ИЗ
| Справочник.ХарактеристикиНоменклатуры КАК ХарактеристикиНоменклатуры
|ГДЕ
| НЕ 1 В
| (ВЫБРАТЬ ПЕРВЫЕ 1
| 1
| ИЗ
| Таблица ЛЕВОЕ СОЕДИНЕНИЕ Справочник
| ПО
| Таблица.Свойство = Справочник.Свойство
| И Справочник.Ссылка = ХарактеристикиНоменклатуры.Ссылка
| ГДЕ
| Справочник.Ссылка ЕСТЬ NULL
| ИЛИ Таблица.Значение <> ЕСТЬNULL(Справочник.Значение, НЕОПРЕДЕЛЕНО)
|
| ОБЪЕДИНИТЬ ВСЕ
|
| ВЫБРАТЬ ПЕРВЫЕ 1
| 1
| ИЗ
| Справочник ЛЕВОЕ СОЕДИНЕНИЕ Таблица
| ПО
| Таблица.Свойство = Справочник.Свойство
| ГДЕ
| Справочник.Ссылка = ХарактеристикиНоменклатуры.Ссылка
| И ЕСТЬNULL(Таблица.Значение, НЕОПРЕДЕЛЕНО) <> Справочник.Значение)";
Запрос.УстановитьПараметр("Свойства", Объект.Свойства.Выгрузить());
НачДата=ТекущаяУниверсальнаяДатаВМиллисекундах();
Результат = Запрос.Выполнить();
КонДата=ТекущаяУниверсальнаяДатаВМиллисекундах();
ВремяВыполнения = КонДата-НачДата;
Разбор решения №5
Достаточно оригинальное решение задания. Во-первых, это было первое решение, где временные таблицы были проиндексированы. Во-вторых, подход достаточно интересен (использование конструкции НЕ 1 В (...) ).
Что получается:
(1) Эталонная
(2) Справочник
(3) Выбираем все характеристики, у которых выполняется правило, что не найдено ни одной строки лишней и ни одной строки недостающей. Не часто вообще в 1С можно встретить коррелированные вложенные запросы.
И стандартно смотрим на ПВЗ запроса, но перед тем как смотреть, нужно понимать, что данная конструкция
| ВЫБРАТЬ ПЕРВЫЕ 1
| 1
| ИЗ
| Таблица ЛЕВОЕ СОЕДИНЕНИЕ Справочник
| ПО
| Таблица.Свойство = Справочник.Свойство
| И Справочник.Ссылка = ХарактеристикиНоменклатуры.Ссылка
| ГДЕ
| Справочник.Ссылка ЕСТЬ NULL
| ИЛИ Таблица.Значение <> ЕСТЬNULL(Справочник.Значение, НЕОПРЕДЕЛЕНО)
|
| ОБЪЕДИНИТЬ ВСЕ
|
| ВЫБРАТЬ ПЕРВЫЕ 1
| 1
| ИЗ
| Справочник ЛЕВОЕ СОЕДИНЕНИЕ Таблица
| ПО
| Таблица.Свойство = Справочник.Свойство
| ГДЕ
| Справочник.Ссылка = ХарактеристикиНоменклатуры.Ссылка
| И ЕСТЬNULL(Таблица.Значение, НЕОПРЕДЕЛЕНО) <> Справочник.Значение
будет выполняться для КАЖДОГО элемента справочника характеристики. Ну и как обычно - ПВЗ
(1) Первая таблица из объединения внутри конструкции. Проблема в следующем: индекс НЕ работает! На это указывает Nested Loops с левым внешним соединением и Rid Lookup (msdn и blogs.msdn(англ) ). Не работает он из-за колоники "значение", которую нельзя проиндексировать, так как она содержит строки неограниченной длины. Для первой таблицы оптимизатор использовует Table Scan (это выгоднее чем Index Seek + Rid Lookup).
(2) Вторая часть внутренней конструкции, все тоже самое, только еще загоняет в буфер эталонную
(3) Ну и последняя часть - как итог (1) и (2).
Время выполнения 2 теста - 20,110 секунд. Время выполнения 1 теста - 10,176. Я не оценивал запросы на показатели быстродействия с точки зрения СУБД (Reads,Writes,CPU), так как их тяжело просуммировать и вычленить нужные, но мне кажется этот запрос один из самых оптимальных по показателям быстродействия.
Итоги
| № |
1 тест, с |
2 тест, с |
| Решение №1 | 38,798 | 30,614 |
| Решение №2 | 9,526 | 61,170 |
| Решение №3 | 1,648 | 66,292 |
| Решение №4 | 5,321 | 120,145 |
| Решение №5 | 10,176 | 20,110 |
На этом я заканчиваю рассмотрение присланных решений (за рамками статьи осталось рассмотрение еще нескольких похожих решений). В своей статье я постарался не просто привести решения, а показать планы выполнения каждого запроса. Чтобы продемонстировать следующие тезисы: во-первых, всегда нужно думать о быстродействии, даже при решении маленьких задач, так как при масштабировании и росте базы - время выполнения одной и той же операции может измениться кардинально (свеж пример одного из решений, которое до тиражирования характеристики для теста №2 сильно опережало остальные по времени выполнения теста №1, а после заполнения базы характеристиками для теста №2 откатилось по времени выполнения на 4-5 место); во-вторых, я постарался показать, что происходит при сложных операциях в запросах на уровне СУБД; в-третьих, нужно стараться всегда помнить о необходимости индексировать временные таблицы, так как отсутствие индекса приводит к table scan'ам и сортировкам, хотя в этом решении индексирование никак не помогает из-за наличия колонки со строкой неограниченной длины (что опять же видно только в SQL Profiler).
Надеюсь, что данный труд был не бесполезен и кому-то поможет в будущем - готов обсудить содержание статьи.
-----------
С уважением, Королев Алексей
Руководитель отдела внедрения / Технический руководитель проектов
Офис «м. Савеловская» (Москва) - http://делаемпроекты.рф/
Компания «Первый БИТ» (1С:Бухучет и Торговля)
