Проверка оптимальности регистра в 1С

Материал опубликован на сайте SoftPoint.ru/

АнтонГусев

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

Для скорости выборки SQL-сервером данных из таблицы важно наличие индекса. Особенно важно, чтобы индекс совпадал с условием отбора (фильтра). Тогда SQL-сервер быстро отберет нужные данные. Как правило в 1С устанавливаются сразу несколько фильтров, по складу, по списку товаров и т.д. Но SQL-сервер на самом деле будет использовать только 1 индекс по каждой таблице. В 1С устроено так, что для таблицы итогов регистра существует один «покрывающий» индекс. Что это значит? А это значит, что есть составной индекс, которые включает в себя период и все измерения регистра в порядке их следования в конфигураторе, так как порядок следования измерений в таблице данных совпадает с порядком следования их в конфигураторе.

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

Приведу пример проверки оптимальности регистра в 1С. В регистре есть измерение «Товар» и «Склад». В таблице итогов регистра находится 1000 записей на каждый период. Предположим, что это 2 склада и 500 товаров на остатке дают эту тысячу. Предположим, что у нас проводится документ, в котором 100 товаров. Тогда возможны следующие варианты. Если SQL-сервер будет фильтровать по складу, то быстро отберет 500 записей, а затем для каждой будет сравнивать на равенство одному из 100 товаров. Если же будет фильтр по товару, то SQL-сервер быстро отберет 200 записей и для каждой будет сравнивать равенству одному складу. Разница очевидна. В нашем случае селективность индекса «Период+Склад» будет равна 2 / 1000 * 100 % = 0,2 %. Селективность индекса «Период+Товар» будет равна 500 / 1000 * 100 % = 50 %. Таким образом мы приходим к выводу, что если у нас используется оба фильтра, то на первое место мы должны поставить товар, так как у получающегося индекса селективность больше.

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

Вот как выглядит отчет:

Вот результат его работы:

Для работы необходимо наличие библиотеки rainbow.dll в папке ИБ, которую можно взять здесь. Текст обработки представлен ниже:

Процедура Сформировать()
ТекРегистр=ВыбРегистр.ПолучитьЗначение(ВыбРегистр.ТекущаяСтрока());
ТЗ=СоздатьОбъект("ТаблицаЗначений");
ТЗ.НоваяКолонка("Название","Строка");
ТЗ.НоваяКолонка("Процент","Число");
ТЗ.НоваяКолонка("ЕстьПометка","Число");
ТЗ.НоваяКолонка("СтарыйНомер","Число");
//Общее количество записей в регистре
Мета=СоздатьОбъект("MetaDataWork");
ЗапросРадуги=СоздатьОбъект("ODBCQuery");
ИмяТаблицыИтогов=Мета.ИмяТаблицыИтогов(ТекРегистр);
ВсегоЗаписей=0;
ТекстЗапроса="SELECT COUNT(*) FROM "+ИмяТаблицыИтогов;
Если ЗапросРадуги.Prepare(ТекстЗапроса,0,0)=1 Тогда
Если ЗапросРадуги.Open()=1 Тогда
ЗапросРадуги.GotoNext();
ВсегоЗаписей=ЗапросРадуги.GetLong(0);
ЗапросРадуги.Close();
Иначе
Предупреждение("Ошибка открытия запроса!",10);
Возврат;
КонецЕсли;
ЗапросРадуги.Reset();
Иначе
Предупреждение("Ошибка выполнения запроса!",10);
Возврат;
КонецЕсли;
//Теперь измерения.
Для к=1 по СписокИзмерений.РазмерСписка() Цикл
//Проверяем по каждому измерению.
ТекНазвание=СписокИзмерений.ПолучитьЗначение(к);
ТекИмяИзмерения=Мета.ИДИзмеренияРегистра(ТекРегистр,ТекНазвание);
ТекстЗапроса="SELECT COUNT(DISTINCT CAST(PERIOD AS char(8)) + CAST(SP"+ТекИмяИзмерения+" AS varchar)) FROM "+ИмяТаблицыИтогов;
Если ЗапросРадуги.Prepare(ТекстЗапроса,0,0)=1 Тогда
Если ЗапросРадуги.Open()=1 Тогда
ЗапросРадуги.GotoNext();
ТЗ.НоваяСтрока();
ТЗ.Название=ТекНазвание;
ТЗ.Процент=Окр(ЗапросРадуги.GetLong(0)/ВсегоЗаписей*100,2);
ТЗ.ЕстьПометка=СписокИзмерений.Пометка(к);
ТЗ.СтарыйНомер=к;
ЗапросРадуги.Close();
Иначе
Предупреждение("Ошибка открытия запроса!",10);
Возврат;
КонецЕсли;
ЗапросРадуги.Reset();
Иначе
Предупреждение("Ошибка выполнения запроса!",10);
Возврат;
КонецЕсли;
КонецЦикла;
ЗапросРадуги="";
ТЗ.Сортировать("ЕстьПометка-,Процент-");
Таб=СоздатьОбъект("Таблица");
Таб.ИсходнаяТаблица("Таблица");
Таб.ВывестиСекцию("Заголовок");
ТЗ.ВыбратьСтроки();
Пока ТЗ.ПолучитьСтроку()=1 Цикл
Если ТЗ.НомерСтроки=1 Тогда
ТекИзмерение=ТЗ.Название;
ТекФильтр=ТЗ.ЕстьПометка;
КонецЕсли;
Таб.ВывестиСекцию("Строка");
КонецЦикла;
Если (Метаданные.Регистр(ТекРегистр).Измерение(ТекИзмерение).ОтборДвижений=0) И (ТекФильтр=1) Тогда
Таб.ВывестиСекцию("Рекомендации");
КонецЕсли;
Таб.Опции(0,0,0,0);
Таб.ТолькоПросмотр(1);
Таб.Показать();
КонецПроцедуры
//________________________________________________________
Процедура ОбрВыбРегистр()
Если ВыбРегистр.РазмерСписка()>0 Тогда
ТекИдРегистра=ВыбРегистр.ПолучитьЗначение(ВыбРегистр.ТекущаяСтрока());
СписокИзмерений.УдалитьВсе();
Для к=1 по Метаданные.Регистр(ТекИдРегистра).Измерение() Цикл
СписокИзмерений.ДобавитьЗначение(Метаданные.Регистр(ТекИдРегистра).Измерение(к).Идентификатор);
КонецЦикла;
Иначе
Форма.ВыбРегистр.Доступность(0);
КонецЕсли;
КонецПроцедуры
//________________________________________________________
Процедура ПриОткрытии()
Для к=1 по Метаданные.Регистр() Цикл
ВыбРегистр.ДобавитьЗначение(Метаданные.Регистр(к).Идентификатор);
КонецЦикла;
Если ВыбРегистр.РазмерСписка()>0 Тогда
ВыбРегистр.ТекущаяСтрока(1);
Иначе
Предупреждение("В конфигурации нет регистров!",10);
Форма.кнЗапуск.Доступность(0);
КонецЕсли;
ОбрВыбРегистр();
Если ЗагрузитьВнешнююКомпоненту("Rainbow.dll")=0 Тогда
Форма.кнЗапуск.Доступность(0);
Предупреждение("Не найдена внешняя компонента Rainbow.dll",10);
КонецЕсли;
КонецПроцедуры