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

27.09.13

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

быстрый поиск большие объемы данных функция

См. также

Поинтегрируем: сервисы интеграции – новый стандарт или просто коннектор?

Обмен между базами 1C Администрирование СУБД Механизмы платформы 1С Платформа 1С v8.3 Бесплатно (free)

В платформе 8.3.17 появился замечательный механизм «Сервисы интеграции». Многие считают, что это просто коннектор 1С:Шины. Так ли это?

11.03.2024    4443    dsdred    53    

70

Как готовить и есть массивы

Механизмы платформы 1С Платформа 1С v8.3 Бесплатно (free)

Все мы используем массивы в своем коде. Это один из первых объектов, который дают ученикам при прохождении обучения программированию. Но умеем ли мы ими пользоваться? В этой статье я хочу показать все методы массива, а также некоторые фишки в работе с массивами.

24.01.2024    5275    YA_418728146    25    

63

Планы обмена VS История данных

Обмен между базами 1C Механизмы платформы 1С Платформа 1С v8.3 Бесплатно (free)

Вы все еще регистрируете изменения только на Планах обмена и Регистрах сведений?

11.12.2023    6394    dsdred    36    

111

1С-ная магия

Механизмы платформы 1С Бесплатно (free)

Язык программирования 1С содержит много нюансов и особенностей, которые могут приводить к неожиданным для разработчика результатам. Сталкиваясь с ними, программист начинает лучше понимать логику платформы, а значит, быстрее выявлять ошибки и видеть потенциальные узкие места своего кода там, где позже можно было бы ещё долго медитировать с отладчиком в поисках источника проблемы. Мы рассмотрим разные примеры поведения кода 1С. Разберём результаты выполнения и ответим на вопросы «Почему?», «Как же так?» и «Зачем нам это знать?». 

06.10.2023    18464    SeiOkami    46    

118

Дефрагментация и реиндексация после перехода на платформу 8.3.22

Механизмы платформы 1С Платформа 1С v8.3 Бесплатно (free)

Начиная с версии платформы 8.3.22 1С снимает стандартные блокировки БД на уровне страниц. Делаем рабочий скрипт, как раньше.

14.09.2023    12074    human_new    27    

74

Валидация JSON через XDTO (включая массивы)

WEB-интеграция Универсальные функции Механизмы платформы 1С Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

При работе с интеграциями рано или поздно придется столкнуться с получением JSON файлов. И, конечно же, жизнь заставит проверять файлы перед тем, как записывать данные в БД.

28.08.2023    8799    YA_418728146    6    

141

Внешние компоненты Native API на языке Rust - Просто!

Механизмы платформы 1С Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

Внешние компоненты для 1С можно разработывать очень просто, пользуясь всеми преимуществами языка Rust - от безопасности и кроссплатформенности до удобного менеджера библиотек.

20.08.2023    6271    sebekerga    54    

94

Все скопируем и вставим! (Буфер обмена в 1С 8.3.24)

Механизмы платформы 1С Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

Рассмотрим новую возможность 8.3.24 и как её можно эффективно использовать

27.06.2023    15965    SeiOkami    31    

103
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. AlX0id 28.09.13 13:05 Сейчас в теме
мм.. А если добавить реквизит, аналогичный коду с указанием индексации - не прокатит?
Сам не пробовал на таких объемах - просто спрашиваю )
7. Evilgrym 191 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С-ка так не умеет.
2. Armando 1399 28.09.13 16:02 Сейчас в теме
Меня вот это смущает:
Соединение таблицы справочника и временной таблицы с кодами не привело к использованию индекса
3. vvr908 446 29.09.13 13:29 Сейчас в теме
А какая была СУБД?
ShantinTD; +1 Ответить
6. Evilgrym 191 30.09.13 16:43 Сейчас в теме
4. Famza 84 30.09.13 09:37 Сейчас в теме
(0)
Соединение таблицы справочника и временной таблицы с кодами не привело к использованию индекса.

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

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

Выбрать
Спр.код,
Спр.ссылка
из
Справочник.какойтотам как Спр
ВНУТРЕННЕЕ СОЕДИНЕНИЕ СписокКодов как СписокКодов
по Спр.код=СписокКодов.код
//---------------------------------------------
8. tango 506 30.09.13 16:54 Сейчас в теме
новосозданный индекс потом какой жизнью живет?
1с его не знает.
БД обновляет?
если нет - то это как бы разовая фича, время на ее создание надо учитывать
если да - то шаманство в пофигураторе может дать такой же эффект?
9. Evilgrym 191 30.09.13 17:08 Сейчас в теме
(8) tango,

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

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

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

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

мы ведь тоже тольк дали команду...
почему индекс через пофигуратор содержит второе поле?
может быть, это не совсем те индексы, которые БД создает по нашему криту?
16. aspirator23 339 02.10.13 07:01 Сейчас в теме
(10)Evilgrym, 82 колонки имеется 65 индексов - имеет смысл?
С одной стороны запрос столько индексов не может использовать. С другой стороны получаем задержки при записях из-за расходов на записи индексов.
12. tango 506 30.09.13 17:43 Сейчас в теме
стремно как-то в типовых убивать индексы от разрабов
14. Evilgrym 191 30.09.13 17:48 Сейчас в теме
(12) tango,
Ну я и не агитирую за их убийство.
Просто, когда база под 150 гиг, как-то мысли всякие начинают в голову приходить, где б такое сэкономить да подускорить...
15. tango 506 30.09.13 17:49 Сейчас в теме
(14) я тут бегло глянул на ЗУП - "убивать", конечно, не комильфо. скажем так: "убрать"
13. tango 506 30.09.13 17:47 Сейчас в теме
вообще, любопытная картинка:
ЗУП, СотрудникиОрганизаций
ДатаДоговора - индекс
ДатаПриемаНаРаботу - не индекс

приколисты там, у флагмана, сидят
17. rus128 2 02.10.13 10:17 Сейчас в теме
"Задача усложнялась теще"
а тёща тут при чем? :-)
18. madkat 02.10.13 13:04 Сейчас в теме
У Вячеслава Гилева есть хороший инструмент, по анализу индексов. Вот я ему предлагал написать подсистему к любой стандарной конфе, в которой можно было указать для какого объекта и по каким полям проверять наличие инексов, и в случает отсутствия их создавать.
Оставьте свое сообщение