gifts2017

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

Опубликовал Evil Grym (Evilgrym) в раздел Программирование - Практика программирования

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

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

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

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

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.Код ";
            
    Если ИспользоватьВременныеТаблицы=Истина ТОгда
        Запрос.Выполнить();        
    Иначе
        Возврат Запрос.Выполнить().Выгрузить();
    КонецЕсли;    
КонецФункции    

См. также

Подписаться Добавить вознаграждение
Комментарии
1. Алексей 1 (AlX0id) 28.09.13 13:05
мм.. А если добавить реквизит, аналогичный коду с указанием индексации - не прокатит?
Сам не пробовал на таких объемах - просто спрашиваю )
2. Armando Armando (Armando) 28.09.13 16:02
Меня вот это смущает:
Соединение таблицы справочника и временной таблицы с кодами не привело к использованию индекса
3. Владислав Рожевский (vvr908) 29.09.13 13:29
4. Иван Иванов (Famza) 30.09.13 09:37
(0)
Соединение таблицы справочника и временной таблицы с кодами не привело к использованию индекса.

А какой был первоначальный запрос? Ладно бы для 7.7 проблема субд и запросов, а для 8.2...
5. Evil Grym (Evilgrym) 30.09.13 16:42
(4) Famza,

Первоначальный вариант был такой:
//------------------------------------------
Выбрать
тз.код
Поместить СписокКодов
из &tz как ТЗ
Индексировать по Код
;

Выбрать
Спр.код,
Спр.ссылка
из
Справочник.какойтотам как Спр
ВНУТРЕННЕЕ СОЕДИНЕНИЕ СписокКодов как СписокКодов
по Спр.код=СписокКодов.код
//---------------------------------------------
6. Evil Grym (Evilgrym) 30.09.13 16:43
7. Evil Grym (Evilgrym) 30.09.13 16:46
(1) AlX0id,
Не прокактит. 1С-ка создает индекс, типа

CREATE UNIQUE INDEX _reference2_byfield2510_rr
ON _reference2
USING btree
(_fld2508rref, _idrref);

Т.е. в этом индексе 2 колонки - интересующее нас поле и ссылка на элемент справочника.
Но в запросе мы используем условие всего лишь по одному полю из двух. В результате Postgre SQL считает этот индекс неподходящим и лезет перебором по всей таблице.

Да, и так, для статистики - в справочнике без малого 14 млн записей. Записи постоянно добавляются и используется по большей части последняя, самая свежая, треть. Поэтому самодельный индекс сделан убывающим, чтоб свежие данные были в более быстром доступе. 1С-ка так не умеет.
8. Михаил Ражиков (tango) 30.09.13 16:54
новосозданный индекс потом какой жизнью живет?
1с его не знает.
БД обновляет?
если нет - то это как бы разовая фича, время на ее создание надо учитывать
если да - то шаманство в пофигураторе может дать такой же эффект?
9. Evil Grym (Evilgrym) 30.09.13 17:08
(8) tango,

Субд сама обновляет. В принципе пофигуратор индексы только создает, вернее дает команду субд их создать, а дальше это уже дело субд с ними возюкаться.

Нюанс может возникнуть при создании новой базы и при изменении в структуре данных. Индекс может пропасть, но это дело поправимое.

Создавать индекс можно и не выгоняя всех из базы, используя команду CREATE INDEX CONCURRENTLY. Думает подольше конечно.
10. Evil Grym (Evilgrym) 30.09.13 17:36
И вообще, по наблюдениям, все индексы, которые создает 1С-ка содержат в себе как минимум 2 поля, индексируемое и _idrref, а это приводит к тому что в реальной жизни, полной извращенных ситуаций, субд отказывается их использовать.

Например в справочнике на 82 колонки имеется 65 индексов созданных конфигуратором, но выборки в основном делаются с помощью построителя запросов с отбором по нескольким колонкам и из 65 индексов хоть как-то используется только 18. Остальные вообще бесполезны. Ну ладно там в таблице всего 80 тысяч записей... Место-то жрется...
11. Михаил Ражиков (tango) 30.09.13 17:42
(10) Evilgrym,
вернее дает команду субд их создать

мы ведь тоже тольк дали команду...
почему индекс через пофигуратор содержит второе поле?
может быть, это не совсем те индексы, которые БД создает по нашему криту?
12. Михаил Ражиков (tango) 30.09.13 17:43
стремно как-то в типовых убивать индексы от разрабов
13. Михаил Ражиков (tango) 30.09.13 17:47
вообще, любопытная картинка:
ЗУП, СотрудникиОрганизаций
ДатаДоговора - индекс
ДатаПриемаНаРаботу - не индекс

приколисты там, у флагмана, сидят
14. Evil Grym (Evilgrym) 30.09.13 17:48
(12) tango,
Ну я и не агитирую за их убийство.
Просто, когда база под 150 гиг, как-то мысли всякие начинают в голову приходить, где б такое сэкономить да подускорить...
15. Михаил Ражиков (tango) 30.09.13 17:49
(14) Evilgrym, я тут бегло глянул на ЗУП - "убивать", конечно, не комильфо. скажем так: "убрать"
16. aspirator 23 (aspirator23) 02.10.13 07:01
(10)Evilgrym, 82 колонки имеется 65 индексов - имеет смысл?
С одной стороны запрос столько индексов не может использовать. С другой стороны получаем задержки при записях из-за расходов на записи индексов.
17. Ruslan (rus128) 02.10.13 10:17
"Задача усложнялась теще"
а тёща тут при чем? :-)
18. Юрий (madkat) 02.10.13 13:04
У Вячеслава Гилева есть хороший инструмент, по анализу индексов. Вот я ему предлагал написать подсистему к любой стандарной конфе, в которой можно было указать для какого объекта и по каким полям проверять наличие инексов, и в случает отсутствия их создавать.