Индексирование временных таблиц в запросе 1С 8.3 (8.2)
Разработчики 1С очень часто игнорируют использование конструкции «ИНДЕКСИРОВАТЬ ПО» в запросе.
Зачем нужно индексировать поля в запросе 1С 8.3, я расскажу ниже.
Как работает ИНДЕКСИРОВАТЬ ПО?
Индексация в запросе нужна для более быстрого формирования результате запроса. Как это работает? Система строит индекс для временной таблицы, чтобы быстрее найти нужное значение.
Т.е. система работает точно так же, как и обычные индексы 1С, только для временной таблицы.
Конструкцию рекомендуется использовать по полям временных таблиц, по которым эта временная таблица будет соединяться с другими таблицами баз данных. Это существенно повышает скорость выполнения соединения таблиц.
Однако следует учесть один момент. Построение индекса временной таблицы также требует времени на выполнение. Поэтому целесообразно использовать конструкцию «ИНДЕКСИРОВАТЬ ПО», только если Вы знаете, что во временной таблице будет не 1-2 записи. В противном случае эффект может быть обратным — быстродействие от индексированных полей не компенсирует времени построения индекса.
К сожалению, мы физически не можем проконсультировать бесплатно всех желающих, но наша команда будет рада оказать услуги по внедрению и обслуживанию 1С. Более подробно о наших услугах можно узнать на странице Услуги 1С или просто позвоните по телефону +7 (499) 350 29 00. Мы работаем в Москве и области.
Поддержите нас, расскажите друзьям!
СПРОСИТЕ в комментариях!
1. Что значит «рекомендуется использовать по полям временных таблиц, по которым эта временная таблица будет соединяться с другими таблицами баз данных»? У меня есть левое соединение где в правой части — таблица базы данных. Зачем индексировать временную таблицу если при выборке будет проводиться поиск в правой таблице? То есть индекс временной таблицы в данном случае не функционален.
2. Если я связываю 2 временные таблицы левым соединением, какую таблицу я должен индексировать?
3. В приведённом примере — какой смысл индексировать таблицу по единственному полю, если в индексе получается такое же количество записей как в таблице и (следовательно) обход индекса при выборке занимает примерно столько же времени?
Такое впечатление, что писали статью «чтоб было», не думая.
1. Вы просто индексы готовить не умеете, потому как у Вас поверхностные знания общих принципов применения и работы индексов на платформах БД. Вообще есть определенные стандарты, согласно которым проектировщики платформ БД (вменяемые) следуют, иначе спроса на их платформу не будет.
Так вот ответ на ваш вопрос — раз у вас в левом соединении временная таблица и по ней в самом запросе не идут поиск — значит у вас данные из левой таблицы выводятся в результат запроса (иначе зачем тогда Вам вообще левое соединение). А соответственно соединение по имеющимся записям в левой таблице может идти по ее (левой таблицы) индексу или по поиску (перебору) всех записей этой левой таблицы. И так по каждой строке правой. Да, если записей мало — то результат не так заметен, а то и даже… но когда записей много… Поймите — соединение, какое оно бы ни было — это соединение. И в данном случае можно всю таблицу перебирать, а можно по индексу сработать.
2. Если нужно получить максимально быстро результат выборки, особенно если многократно с этими временными таблицами — то обе. При соединении (не важно каком именно) платформа БД при наличии индекса на поле таблице — работает всегда по индексу. Это всегда быстрее.
3. Автор как раз понимает тему, это Вы не в теме и совершенно не представляете как работают индексы. Да, записей в индексе столько же будет, но принцип работы по индексу совершенно другой. Возможно, если у вас обе таблицы абсолютно аналогично отсортированы по полям соединения — то может и не такой эффект будет — но ведь это как правило не так…
В общем вывод — статью писали думая — это Вы ответили, не подумав…
ИНДЕКСИРОВАТЬ ПО в запросе в системе 1С Предприятие 8.3
1. Что значит «индексировать»?
В данной статье будет рассмотрена конструкция «ИНДЕКСИРОВАТЬ ПО» в запросах системы 1С:Предприятие 8.3, варианты её использования, ошибки разработчиков и методы индексирования. Все вышеперечисленное будет проиллюстрировано на реальных примерах.
Вначале для качественного понимания конструкции стоит заложить общие понятия. Итак, что значит «индексировать» в 1С? Индекс – это некоторый объект внутри базы данных, который создаётся ради увеличения эффективности отбора (поиска) данных. В базах данных встречаются довольно большие таблицы с тысячами, а то и миллионами строк, которые находятся в случайном порядке. Чтобы осуществить запрос по какому-то критерию и найти нужную строку нужно прождать немало времени, пока строки одна за другой будут перебираться. Формирование индекса происходит по нумерации столбцов в таблице, а также благодаря указателям. Повышение скорости в работе через индексы производится из-за его структуры, которая имеет оптимизацию для поиска (например, сбалансированное дерево (АВЛ-дерево – сбалансированное дерево, балансировка ведётся по высоте и дерево является двоичным, для любой из его вершин высота двух нижестоящих деревьев отличается на 1 или меньше, показано на рисунке ниже). Таким образом имеем, что индексация – это процесс поиска элемента по индексу, который существенно сокращает время запроса.
Рис. 1 Сбалансированное дерево
2. В каких случаях используется индексированные данные?
Разработчики в 1С довольно редко используют конструкцию «ИНДЕКСИРОВАТЬ ПО». Возможно, это связано с тем, что большинство не знает её точного механизма работы. Принцип поиска «ИНДЕКСИРОВАТЬ ПО» стоит начинать использовать именно внутри полей временных таблиц, по этим полям раннее указанная таблица будет искать соединение с другими таблицами из различных баз данных. Как раз такая схема и повысит эффективность и скорость выполнения операции по соединению двух таблиц.
Примечание: стоит учитывать, что построение и написание участка кода, который включает индексированные данные – это тоже затрата времени, поэтому нецелесообразно использовать процедуру «ИНДЕКСАЦИЯ ПО», если строк большое количество (как минимум, должно быть тысяч 5 строк). В случае, когда эта конструкция была использована на маленькое количество строк, то временной эффект будет обратным – время для построения индекса окажется существенно больше, чем последующее быстродействие конструкции.
3. Работа с методом индексирования
Вначале система 1С:Предприятие 8.3 формирует для временной таблицы индекс как и обычно, только именно для временной таблицы.
Приведём пример, как выглядит данная конструкция в коде программы 1С. В нашем случае идёт поиск по базе с физическими лицами, где нас интересует их доход, смотреть скриншот ниже:
Рис. 2 Конструкция метода индексирования в коде
Как видим, требования соблюдены: база с физическими лицами – это временная таблица, при этом база довольно большая, так что время будет сэкономлено.
В данной статье была рассмотрена конструкция «ИНДЕКСИРОВАТЬ ПО» в системе 1С:Предприятие 8.3, принцип индексации, случаи её необходимости и достаточности, а также пример написания данной процедуры внутри кода программы 1С, обоснована её уместность там.
Как правильно проиндексировать поля для запроса
Значительная часть проблем, приводящих к не оптимальной работе запросов, может быть обнаружена путем анализа кода конфигурации и структуры метаданных. Имеется перечень типичных ошибок в коде и структуре данных, последствия которых достаточно хорошо изучены и легко предсказуемы. Анализ кода с использованием этого перечня позволяет решить большую часть проблем с производительностью запросов, не углубляясь в детальную техническую информацию (текст запроса на языке SQL, план запроса и т.д.).
Основные причины не оптимальной работы запросов, диагностируемые на уровне кода конфигурации и структуры метаданных:
соединения с подзапросами;
соединения с виртуальными таблицами;
несоответствие индексов и условий запроса;
использование подзапросов в условии соединения;
получение данных через точку от полей составного типа;
фильтрация виртуальных таблиц без использования параметров.
В настоящей статье рассматриваются перечисленные причины неоптимальной работы запросов и даются рекомендации по их оптимизации.
Cоединения с подзапросами
Рекомендации
При написании запросов не следует использовать соединения с подзапросами. Следует соединять друг с другом только объекты метаданных или временные таблицы. Если запрос использует соединения с подзапросами, то его следует переписать с использованием временных таблиц.
Обратите внимание на то, что возможность использования временных таблиц появилась в 1С:Предприятии начиная с версии 8.1. Если вы используете версию 8.0, то для решения проблемы производительности такого запроса следует перейти на 8.1.
Для оптимизации запроса следует разбить его на несколько отдельных запросов (по числу подзапросов, используемых в соединениях). Эти запросы рекомендуется поместить в один пакетный запрос.
Внимание! Не забудьте проиндексировать созданную временную таблицу. В качестве индексных полей следует указать все поля, которые используются в условии соединения.
Оптимизатор сервера СУБД (независимо от того, какую СУБД вы используете) не всегда может правильно оптимизировать подобный запрос. В данном случае, проблемой для оптимизатора является выбор правильного способа соединения. Существуют несколько алгоритмов соединения двух выборок. Выбор того или иного алгоритма зависит от того, сколько записей будет содержаться в одной и в другой выборке. В том случае, если вы соединяете две физические таблицы, СУБД может легко определить объем обоих выборок на основании имеющейся статистики. Если же одна из соединямых выборок представляет собой подзапрос, то понять, какое количество записей она вернет, становится очень сложно. В этом случае СУБД может ошибиться с выбором плана, что приведет к катастрофическому падению производительности запроса.
Переписывание запроса по приведенной выше методике имеет своей целью упростить работу оптимизатору СУБД. В переписанном запросе все выборки, участвующие в соединениях будут представлять собой физические таблицы, и СУБД сможет легко определить размер каждой выборки. Это позволит СУБД гарантированно выбрать самый быстрый из всех возможных планов. Причем, СУБД будет делать правильный выбор независимо ни от каких условий. Переписанный подобным образом запрос будет работать одинаково хорошо на любых СУБД, что особенно важно при разработке тиражных решений. Кроме того, переписанный подобным образом запрос лучше читается, проще для понимания и отладки.
Если в запросе используется соединение с виртуальной таблицей языка запросов 1С:Предприятия (например, «РегистрНакопления.Товары.Остатки()») и запрос работает с неудовлетворительной производительностью, то рекомендуется вынести обращение к виртуальной таблице в отдельный запрос с сохранением результатов во временной таблице.
То есть, следует использовать ту же рекомендацию, что и в случае соединения с подзапросом.
Пояснения
Виртуальные таблицы, используемые в языке запросов 1С:Предприятия, могут разворачиваться в подзапросы при трансляции в язык SQL. Это связано с тем, что виртуальная таблица часто (но не всегда) получает данные из нескольких физических таблиц СУБД. Если вы используете соединение с виртуальной таблицей, то на уровне SQL оно может быть в некоторых случаях реализовано, как соединение с подзапросом. В этом случае оптимизатор СУБД может точно так же выбрать неоптимальный план, как при работе с подзапросом, использованным в языке 1С:Предприятия в явном виде.
Несоответствие индексов и условий запроса
Рекомендации
Убедитесь в том, что для всех условий, использованных в запросе, имеются подходящие индексы.
Условия используются в следующих секциях запроса:
ВЫБРАТЬ … ИЗ … ГДЕ
СОЕДИНЕНИЕ … ПО
ВЫБРАТЬ … ИЗ (, )
ИМЕЮЩИЕ
Для каждого условия должен существовать подходящий индекс. Подходящим является индекс, удовлетворяющий следующим требованиям:
1. Индекс содержит все поля перечисленные в условии;
2. Эти поля находятся в самом начале индекса;
3. Эти поля идут подряд, то есть между ними не «вклиниваются» поля, не участвующие в условии запроса;
При создании объекта метаданных 1С:Предприятие автоматически создает индексы, которые должны подходить для работы большинства запросов.
Основные идексы, создаваемые 1С:Предприятием:
индекс по уникальному идентификатору (ссылке) для всех объектных сущностей (справочники, документы и т.д.);
индекс по регистратору (ссылке на документ) для таблиц движений регистров, подчиненных регистратору;
индекс периоду и значениям всех измерений для итоговых таблиц регистров накопления;
индекс периоду, счету и значениям всех измерений для итоговых таблиц регистров бухгалтерии.
Детальная информация по индексам, автоматически создаваемым 1С:Предприятием содержится в статье «Индексы таблиц базы данных 1С:Предприятия 8.1».
В тех случаях, когда автоматически созданных индексов недостаточно, можно дополнительно проиндексировать реквизиты объекта метаданных. Информация о том, какие индексы при этом будут созданы, содержится в этой же статье. Следует иметь в виду, что создание индекса ускоряет процесс поиска информации, но может несколько замедлить процесс ее изменения (добавления, редактирования и удаления). Поэтому индексы следует создавать осознанно и только в том случае, если точно известен запрос, для которого такой индекс необходим. Не следует создавать индексы «на всякий случай» или заведомо избыточные индексы. Например никогда не следует дополнительно индексировать первое измерение регистра, поскольку для поиска по значению первого измерения подходит основной индекс таблицы итогов, который автоматически создаст платформа.
Если в структуре базы данных отсутствует индекс, удовлетворяющий всем перечисленным условиям, то для получения результата СУБД будет вынуждена сканировать таблицу или один из ее индексов. Это приведет к увеличению времени выполнения запроса, а так же к возможному снижению параллельности системы, поскольку возрастет количество установленных блокировок.
ИНДЕКСИРОВАТЬ ПО в запросе в системе 1С Предприятие 8.3
1. Что значит «индексировать»?
В данной статье будет рассмотрена конструкция «ИНДЕКСИРОВАТЬ ПО» в запросах системы 1С:Предприятие 8.3, варианты её использования, ошибки разработчиков и методы индексирования. Все вышеперечисленное будет проиллюстрировано на реальных примерах.
Вначале для качественного понимания конструкции стоит заложить общие понятия. Итак, что значит «индексировать» в 1С? Индекс – это некоторый объект внутри базы данных, который создаётся ради увеличения эффективности отбора (поиска) данных. В базах данных встречаются довольно большие таблицы с тысячами, а то и миллионами строк, которые находятся в случайном порядке. Чтобы осуществить запрос по какому-то критерию и найти нужную строку нужно прождать немало времени, пока строки одна за другой будут перебираться. Формирование индекса происходит по нумерации столбцов в таблице, а также благодаря указателям. Повышение скорости в работе через индексы производится из-за его структуры, которая имеет оптимизацию для поиска (например, сбалансированное дерево (АВЛ-дерево – сбалансированное дерево, балансировка ведётся по высоте и дерево является двоичным, для любой из его вершин высота двух нижестоящих деревьев отличается на 1 или меньше, показано на рисунке ниже). Таким образом имеем, что индексация – это процесс поиска элемента по индексу, который существенно сокращает время запроса.
Рис. 1 Сбалансированное дерево
2. В каких случаях используется индексированные данные?
Разработчики в 1С довольно редко используют конструкцию «ИНДЕКСИРОВАТЬ ПО». Возможно, это связано с тем, что большинство не знает её точного механизма работы. Принцип поиска «ИНДЕКСИРОВАТЬ ПО» стоит начинать использовать именно внутри полей временных таблиц, по этим полям раннее указанная таблица будет искать соединение с другими таблицами из различных баз данных. Как раз такая схема и повысит эффективность и скорость выполнения операции по соединению двух таблиц.
Примечание: стоит учитывать, что построение и написание участка кода, который включает индексированные данные – это тоже затрата времени, поэтому нецелесообразно использовать процедуру «ИНДЕКСАЦИЯ ПО», если строк большое количество (как минимум, должно быть тысяч 5 строк). В случае, когда эта конструкция была использована на маленькое количество строк, то временной эффект будет обратным – время для построения индекса окажется существенно больше, чем последующее быстродействие конструкции.
3. Работа с методом индексирования
Вначале система 1С:Предприятие 8.3 формирует для временной таблицы индекс как и обычно, только именно для временной таблицы.
Приведём пример, как выглядит данная конструкция в коде программы 1С. В нашем случае идёт поиск по базе с физическими лицами, где нас интересует их доход, смотреть скриншот ниже:
Рис. 2 Конструкция метода индексирования в коде
Как видим, требования соблюдены: база с физическими лицами – это временная таблица, при этом база довольно большая, так что время будет сэкономлено.
В данной статье была рассмотрена конструкция «ИНДЕКСИРОВАТЬ ПО» в системе 1С:Предприятие 8.3, принцип индексации, случаи её необходимости и достаточности, а также пример написания данной процедуры внутри кода программы 1С, обоснована её уместность там.
ИНДЕКСИРОВАТЬ ПО в запросе в системе 1С Предприятие 8.3
1. Что значит «индексировать»?
В данной статье будет рассмотрена конструкция «ИНДЕКСИРОВАТЬ ПО» в запросах системы 1С:Предприятие 8.3, варианты её использования, ошибки разработчиков и методы индексирования. Все вышеперечисленное будет проиллюстрировано на реальных примерах.
Вначале для качественного понимания конструкции стоит заложить общие понятия. Итак, что значит «индексировать» в 1С? Индекс – это некоторый объект внутри базы данных, который создаётся ради увеличения эффективности отбора (поиска) данных. В базах данных встречаются довольно большие таблицы с тысячами, а то и миллионами строк, которые находятся в случайном порядке. Чтобы осуществить запрос по какому-то критерию и найти нужную строку нужно прождать немало времени, пока строки одна за другой будут перебираться. Формирование индекса происходит по нумерации столбцов в таблице, а также благодаря указателям. Повышение скорости в работе через индексы производится из-за его структуры, которая имеет оптимизацию для поиска (например, сбалансированное дерево (АВЛ-дерево – сбалансированное дерево, балансировка ведётся по высоте и дерево является двоичным, для любой из его вершин высота двух нижестоящих деревьев отличается на 1 или меньше, показано на рисунке ниже). Таким образом имеем, что индексация – это процесс поиска элемента по индексу, который существенно сокращает время запроса.
Рис. 1 Сбалансированное дерево
2. В каких случаях используется индексированные данные?
Разработчики в 1С довольно редко используют конструкцию «ИНДЕКСИРОВАТЬ ПО». Возможно, это связано с тем, что большинство не знает её точного механизма работы. Принцип поиска «ИНДЕКСИРОВАТЬ ПО» стоит начинать использовать именно внутри полей временных таблиц, по этим полям раннее указанная таблица будет искать соединение с другими таблицами из различных баз данных. Как раз такая схема и повысит эффективность и скорость выполнения операции по соединению двух таблиц.
Примечание: стоит учитывать, что построение и написание участка кода, который включает индексированные данные – это тоже затрата времени, поэтому нецелесообразно использовать процедуру «ИНДЕКСАЦИЯ ПО», если строк большое количество (как минимум, должно быть тысяч 5 строк). В случае, когда эта конструкция была использована на маленькое количество строк, то временной эффект будет обратным – время для построения индекса окажется существенно больше, чем последующее быстродействие конструкции.
3. Работа с методом индексирования
Вначале система 1С:Предприятие 8.3 формирует для временной таблицы индекс как и обычно, только именно для временной таблицы.
Приведём пример, как выглядит данная конструкция в коде программы 1С. В нашем случае идёт поиск по базе с физическими лицами, где нас интересует их доход, смотреть скриншот ниже:
Рис. 2 Конструкция метода индексирования в коде
Как видим, требования соблюдены: база с физическими лицами – это временная таблица, при этом база довольно большая, так что время будет сэкономлено.
В данной статье была рассмотрена конструкция «ИНДЕКСИРОВАТЬ ПО» в системе 1С:Предприятие 8.3, принцип индексации, случаи её необходимости и достаточности, а также пример написания данной процедуры внутри кода программы 1С, обоснована её уместность там.