Столкнулся с такой проблемой - при поиске запросом ссылок на список элементов справочника по кодам операция происходит крайне медленно. Разбор статистики по индексам показал что поиск по коду индексы не использует, а при большом количестве записей в справочнике ( порядка нескольких миллионов) это становится проблемой.
Задача усложнялась теще и тем, что требовалось искать пачку записей порядка нескольких тысяч штук среди нескольких миллионов. Соединение таблицы справочника и временной таблицы с кодами не привело к использованию индекса.
проблема решилась таким образом:
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.Код ";
Если ИспользоватьВременныеТаблицы=Истина ТОгда
Запрос.Выполнить();
Иначе
Возврат Запрос.Выполнить().Выгрузить();
КонецЕсли;
КонецФункции
