Быстрая индексированная выборка элементов справочника по коду

Столкнулся с такой проблемой — при поиске запросом ссылок на список элементов справочника по кодам операция происходит крайне медленно. Разбор статистики по индексам показал что поиск по коду индексы не использует, а при большом количестве записей в справочнике ( порядка нескольких миллионов) это становится проблемой.

Категория: Публикации 1С

Подробнее

Описание
Подробнее

Описание

Задача усложнялась теще и тем, что требовалось искать пачку записей порядка нескольких тысяч штук среди нескольких миллионов. Соединение таблицы справочника и временной таблицы с кодами не привело к использованию индекса.

проблема решилась таким образом:

1) Создается индекс в субд типа

CREATE INDEX ids_ap_index_by_code
ON _reference2
USING btree
(_code);

2) Выборка осуществляется запросом с использованием конструкции

ГДЕ НекийСправочник.Код В(&СписокКодовХ)

В результате в субд выполняется запрос, в котором коды из списка кодов перечислены через запятую, что в результате приводит к задействованию индекса .

Поскольку имеется некий лимит на длину строки с кодами через запятую, список кодов делится на несколько частей, и вся выборка выполняется запросом через UNION.

В результате выло получено ускорение выборки с 11 сек. до 0,15 сек.

Была написана такая функция:

Функция БыстраяИндексированнаяВыборкаЭлементовСправочникаПоКоду(ВидСправочника,Запрос,МассивКодов,ИспользоватьВременныеТаблицы,ЛимитКодовНаОдинЗапрос=300,СтруктураДопреквизитов=неопределено)
   // Функция расчитана на использование самодельного индекса по одной колонке «код» справочника.
   // Предполагается что: коды в справочнике уникальны
   //                     МассивКодов желательно передавать без дублей
   //                     На выходе ожидается список уникальных значений
   //                     Индекс создан вручную в субд запросом типа : CREATE INDEX ids_ap_index_by_code ON _reference2 USING btree (_code);
   // На входе получает:
   //                   Имя вида справочника,
   //                   объект Запрос,
   //                   массив кодов для поиска,
   //                   Указание использовать в запросе вывод во временную таблицу ( объекту запрос должен быть назначен менеджер временных таблиц) или нет.
   //                   ЛимитКодовНаОдинЗапрос для условия код в (массивкодов_х), по умолчанию не более 300 штук, при слишком большом значении индексированный поиск может не сработать
   //                   Структуру доп реквизитов, где ключ — псевдоним колонки, значение- текст для выборки данных вида НекийСправочник.ВидПоступленияТовара
   //                                                        или более сложный вариант к примеру: case when НекийСправочник.ВидПоступленияТовара = Значение(Перечисление.ВидыПоступленияТоваров.Комиссия) then НекийСправочник.Поставщик else null end
   // На выходе дает:
   //                Если ИспользоватьВременныеТаблицы=ложь то таблицу значений   [код,колонкидопреквизитов_если_запрашивались,Ссылка]
   //                если ИспользоватьВременныеТаблицы=истина булево Результат запроса Пустой() ,
   //                        а в менеджере временных таблиц создается временная таблица ВидСправочника_БИВПК [код,колонкидопреквизитов_если_запрашивались,Ссылка], Индексированная по коду
   //——————————————// GtG // 26.09.2013 10:31:54
   ДопРеквизиты=»»;

   Если СтруктураДопреквизитов=Неопределено ТОгда
   Иначе
       // пришел массив с текстовкой допреквизитов
       Для каждого стр из СтруктураДопреквизитов цикл
          ДопРеквизиты=ДопРеквизиты+стр.Значение+» как «+Стр.Ключ+»,»+Символы.ПС;
       КонецЦикла;
   КонецЕсли;




   ЗапросТекст=»ВЫБРАТЬ
                  |   НекийСправочник.Код КАК Код,»+ДопРеквизиты+»
                  |   НекийСправочник.Ссылка
                  |ИЗ
                  |   Справочник.»+ВидСправочника+» КАК НекийСправочник
                  |ГДЕ
                  |   НекийСправочник.Код В(&СписокКодовХ)»;

      Х=0;
      СписокКодов=Новый Массив;
      МассивСписковКодов=Новый Массив;
      Для Каждого Стр Из МассивКодов Цикл
          Х=Х+1;
          СписокКодов.Добавить(Стр);
          Если Х%ЛимитКодовНаОдинЗапрос=0 Тогда
              МассивСписковКодов.Добавить(СписокКодов);
              СписокКодов=Новый Массив;
          КонецЕсли;
      КонецЦикла;
      Если СписокКодов.Количество()<>0 Тогда
          МассивСписковКодов.Добавить(СписокКодов);
      КонецЕсли;

      Х=0;

      Запрос.Текст=»»;
      ДЛя Каждого СписокКодовПоиска Из МассивСписковКодов Цикл
          Х=Х+1;

          Запрос.Текст=Запрос.Текст+Символы.ПС;
          Запрос.Текст=Запрос.Текст+СтрЗаменить(ЗапросТекст,»&СписокКодовХ»,»&СписокКодов»+Формат(Х,»ЧГ=»));
          Запрос.Текст=Запрос.Текст+Символы.ПС;
          Запрос.Текст=Запрос.Текст+?(Х=МассивСписковКодов.Количество(),»»,»Union All»);

          Запрос.УстановитьПараметр(«СписокКодов»+Формат(Х,»ЧГ=»),СписокКодовПоиска);
      КонецЦикла;

      Если ИспользоватьВременныеТаблицы=Истина ТОгда
          ИмяВременнойТаблицы=» Into «+ВидСправочника+»_БИВПК»;
          СортировкаИндексирование=» INDEX «;
      Иначе
          ИмяВременнойТаблицы=»»;
          СортировкаИндексирование=» ORDER «;
      КонецЕсли;


      ДопРеквизиты=»»;

      Если СтруктураДопреквизитов=Неопределено ТОгда
      Иначе
          // пришла структура с текстовкой допреквизитов
          Для каждого стр из СтруктураДопреквизитов цикл
              ДопРеквизиты=ДопРеквизиты+Стр.Ключ+» как «+Стр.Ключ+»,»+Символы.ПС;
          КонецЦикла;
      КонецЕсли;



      Запрос.Текст=»Select distinct Base.Код,»+ДопРеквизиты+» Base.Ссылка «+ ИмяВременнойТаблицы+» From («+Символы.ПС+
                   Запрос.Текст+ Символы.ПС+
                   » ) as Base «+СортировкаИндексирование+» by Base.Код «;

   Если ИспользоватьВременныеТаблицы=Истина ТОгда
       Запрос.Выполнить();
   Иначе
       Возврат Запрос.Выполнить().Выгрузить();
   КонецЕсли;
КонецФункции

Каталог решений - Быстрая индексированная выборка элементов справочника по коду