Хрусталева Е. Ю. Язык запросов 1С-Предприятия 8 (2013)
.pdf
Вданном запросе источником данных является таблица оборотов регистра накопления
Продажи, который имеет измерения Номенклатура и Контрагент, ресурсы Количество
и Сумма, а также реквизит ВидОперации. Измерения и реквизит регистра не проиндексированы, т. е. свойство Индексировать для них установлено в значение Не индексировать.
Вбазе данных для таблицы оборотов регистра накопления будет создан индекс: Период
+ Номенклатура + Контрагент.
Впараметре Условие виртуальной таблицы мы передаем условие отбора по полю Контрагент, по значению которого должны быть отобраны записи итоговой таблицы регистра накопления при построении виртуальной таблицы.
Для эффективного отбора записей в таблице оборотов должен существовать подходящий индекс, содержащий в самом начале поле Контрагент, на которое накладывается условие. Как мы видим, этот индекс в таблице отсутствует.
В этом случае для выполнения запроса серверу СУБД придется перебирать (сканировать) все записи таблицы. Время выполнения этой операции напрямую зависит от количества записей в таблице оборотов регистра и может быть очень большим.
Для решения проблемы возможны следующие варианты:
Проиндексировать измерение Контрагент. В этом случае в таблице оборотов регистра накопления будет создан дополнительный индекс: Контрагент + Период. Поставить измерение Контрагент первым в списке измерений. Однако при использовании этого метода нужно быть внимательным, так как в конфигурации могут присутствовать другие запросы, которые могут замедлиться в результате этой перестановки.
Пример 3
Пусть необходимо получить данные об остатке конкретного товара в определенной организации на указанный период из регистра накопления остатков ОстаткиТоваров. Для этого нужно выполнить следующий запрос (листинг 4.3).
Листинг 4.3. Вывод остатков конкретного товара на заданный период в определенной организации
В данном запросе источником данных является таблица остатков регистра накопления ОстаткиТоваров, который имеет непроиндексированные измерения Организация, Склад, Номенклатура. В базе данных для таблицы остатков регистра накопления будет создан индекс:
Период + Организация + Склад + Номенклатура.
Для решения поставленной задачи в запросе к таблице остатков регистра накопления задано условие отбора записей в параметрах виртуальной таблицы. Это условие отбора по полю Период (в параметре Период) и по полям Организация и Номенклатура (в
параметре Условие).
Таким образом, существующий индекс является неэффективным, так как между полями условия запроса Организация и Товар «вклинивается» поле Склад. В этом случае индекс может быть использован частично, и для поиска нужных данных, скорее всего, будет применено сканирование индекса.
Для решения проблемы возможны следующие варианты:
добавить в запрос условие по измерению Склад, поменять местами измерения Склад и Номенклатура.
Заметьте, что порядок следования условий в запросе не обязан совпадать с порядком следования полей в индексе. Главное, чтобы в индексе присутствовали все поля, использованные в условии, они находились в начале индекса и шли без пропусков друг за другом.
Пример 4
Пусть необходимо вывести данные обо всех товарах и их производителях с соблюдением иерархии справочника Товары. Для каждого товара из справочника нужно вывести дату его поступления и поставщика из приходных накладных. Для этого нужно выполнить следующий запрос (листинг 4.4).
Листинг 4.4. Вывод всех товаров в порядке иерархии справочника «Товары» с данными об их поступлении
Вданном запросе источником данных являются таблицы справочника Товары и документа ПриходнаяНакладная, связанные между собой левым соединением. Для эффективного поиска записей в таблицах поля, участвующие в условии соединения таблиц в запросе (ПО Товары.Ссылка = Поступление.Товар), также должны быть проиндексированы.
Втаблице справочника Товары автоматически создается индекс по полю Ссылка, а для документа ПриходнаяНакладная реквизит Товар нужно проиндексировать. В этом случае в таблице документа будет создан дополнительный индекс: Товар + Ссылка.
Врезультате при выполнении запроса для каждого товара из справочника СУБД будет сразу обращаться к записям о поступлении этого товара из таблицы документа, и связь данных в таблицах будет выполнена более оптимально.
Пример 5
Немного усложним условие предыдущего примера так, чтобы в результат запроса попадали все данные из справочника Товары вместе с данными о поступлении товаров за ноябрь. В этом случае самым оптимальным вариантом решения задачи будет использование пакетного запроса и временной таблицы с данными о поступлении товаров. Для этого нужно выполнить следующий запрос (листинг 4.5).
Листинг 4.5. Вывод всех товаров в порядке иерархии справочника «Товары» с данными об их поступлении за ноябрь
В данном пакетном запросе сначала данные о поступлении товаров за ноябрь помещаются во временную таблицу ПоступлениеТоваров. В следующем запросе справочник товаров связывается левым соединением с этой временной таблицей по ссылкам товаров.
Для быстрой и эффективной выборки данных из временной таблицы необходимо проиндексировать эту таблицу по полям, которые участвуют в условии соединения (в данном случае по полю Товар). Делается это с помощью предложения ИНДЕКСИРОВАТЬ ПО. В результате соединение с временной таблицей, проиндексированной по полям, участвующим в условии связи, будет выполнено более эффективно.
Если же вместо временной таблицы использовать вложенный запрос и в условии соединения использовать непроиндексированное поле, то такое соединение будет выполняться крайне медленно. Время сканирования таблицы, участвующей в соединении, увеличивается в разы. Поэтому таких ситуаций настоятельно рекомендуется избегать.
Общие рекомендации
Рекомендации по созданию дополнительных индексов в таблицах, приведенные выше, не следует воспринимать буквально. Их нужно критически переосмысливать в зависимости от специфики конкретной прикладной области, объема используемой информационной базы и приоритетов пользователей по решению тех или иных задач. Нужно понимать, что оптимизировать сразу все не получится. Какие-то отчеты пользователям необходимо получать часто и быстро, а в редких случаях они могут и подождать.
Поэтому не нужно нагружать систему индексами на все случаи жизни. Во-первых, чем больше индексов в таблице, тем больше тратится времени на запись данных в эту таблицу. А во-вторых, когда индексы слишком сложные и их много, то СУБД не может подобрать среди них подходящий индекс за ограниченное время и может выбрать неоптимальный план запроса.
Также следует иметь в виду, что на маленьких таблицах (порядка нескольких тысяч записей и меньше) СУБД практически всегда использует сканирование таблицы либо сканирование кластерного (автоматически созданного системой) индекса, так как это наиболее простое решение в данном случае. Так что нет смысла дополнительно индексировать заведомо небольшие таблицы. С другой стороны, часто трудно заранее предугадать размер таблицы.
То есть понятно, что создание дополнительных индексов может положительно сказаться на выборке данных из больших таблиц и не дать никакого эффекта на маленьких объемах данных (а иногда и замедлить быстродействие системы в целом).
Не следует также создавать индексы по низкоселективным полям. Селективность индекса отражает процент записей, которые по условию можно выбрать из таблицы.
Максимально высокая селективность индекса – у первичного ключа. Если реквизит имеет тип Булево, то индексировать его имеет смысл только в том случае, если незначительная часть записей таблицы всегда принимает одно значение (например, ЛОЖЬ) и нужно выбрать из таблицы записи с этим значением.
В целом к расстановке индексов в таблицах нужно подходить осмысленно, творчески и учитывать накопленный опыт, как личный, так и опыт функционирования подобных прикладных решений.
Причины неоптимальной работы запросов и основные направления их оптимизации
В данном разделе рассматриваются типичные ошибки при написании запросов, которые могут быть легко обнаружены при анализе кода конфигурации и структуры метаданных. В результате анализа этих ошибок даются рекомендации по их исправлению, направленные на повышение эффективности запросов.
Примеры, используемые в данном разделе, можно посмотреть в демонстрационной конфигурации «Учет движения средств», которая находится на прилагаемом компакт-
диске, в обработке Оптимизация запросов.
Общие рекомендации
Хотя может показаться, что ниже повторяются общеизвестные вещи, но все же подобные ошибки довольно часто встречаются:
Нужно стараться минимизировать объем выборки, то есть выбирать запросом только те данные, которые нужны.
Не нужно пытаться любой ценой перенести всю функциональность задачи в СУБД и сосредоточивать всю бизнес-логику в одном запросе. Часто это приводит к существенному усложнению запроса, и СУБД, скорее всего, ошибется при выборе плана такого запроса.
Нужно стараться писать сами запросы и условия соединения таблиц в них как можно более простыми. Особенно плохо использовать в соединении подзапросы.
Не следует использовать в запросе много источников (более 5–7 таблиц). При этом время на анализ запроса оптимизатором СУБД растет нелинейно, и в итоге за лимитированное время может быть выбран неоптимальный план запроса.
Нужно стараться по возможности не использовать запросы к базе данных, результат которых может неограниченно расти с ростом самой информационной базы, так как это может привести к снижению производительности и повышению вероятности аварийного завершения работы запроса. Для обработки данных потенциально неограниченного объема следует организовывать «курсорный» механизм получения результата запроса:
устанавливать уникальный порядок сортировки получаемых данных;
получать выборки по частям с помощью оператора ПЕРВЫЕ, используя данные последней полученной записи предыдущей выборки в качестве начального условия при получении очередной выборки.
Не использовать запросы в цикле
Многократное выполнение однотипных запросов в цикле является распространенной ошибкой новичков. Например, нужно получить остатки на складах для нескольких элементов номенклатуры, содержащихся в массиве номенклатуры
МассивНоменклатуры.
Для решения данной задачи можно написать запрос к таблице остатков регистра накопления ОстаткиНаСкладах и передать в качестве параметра в виртуальную таблицу условие отбора по конкретному элементу номенклатуры. И затем выполнять данный запрос в цикле для каждого элемента из массива номенклатуры. Это можно сделать с помощью следующего запроса (листинг 4.6).
Листинг 4.6. Выполнение запроса для получения остатков номенклатуры в цикле – неправильный вариант
Однако такое решение будет неоптимальным, так как в результате запрос будет выполнен столько раз, сколько элементов содержится в массиве номенклатуры. Многократное выполнение одного и того же или похожих запросов в цикле занимает гораздо больше времени, чем выполнение одного запроса, получающего нужную совокупность данных за один раз.
Поэтому такой фрагмент кода нужно переписать следующим образом (листинг 4.7):
Листинг 4.7. Выполнение запроса для получения остатков номенклатуры за один раз – правильный вариант
Таким образом, остатки всех элементов номенклатуры могут быть получены за одно обращение к таблице остатков регистра накопления.
Но иногда требуемое условие запроса, позволяющее отказаться от выполнения в цикле, сформулировать бывает затруднительно или очень сложно. В этом случае как минимум нужно вынести создание запроса за пределы цикла, а в цикле изменять только параметры запроса. Это позволит избежать многократной синтаксической проверки текста запроса (листинг 4.8).
Листинг 4.8. Выполнение запроса для получения остатков номенклатуры в цикле
Не использовать в запросе функции от параметров
В языке запросов существуют разнообразные функции – функции для работы со строками, функции для работы с датами и т. п. Нежелательно вычислять в запросе значение этих функций от переданных в запрос параметров.
Например, необходимо получить данные приходных накладных за определенный период. Для этого можно выполнить следующий запрос (листинг 4.9).
Листинг 4.9. Вывод документов «Поступление товаров» за определенный период – неправильный вариант
Казалось бы, все правильно – вся логика сосредоточена в одном месте. Но такой запрос будет работать неоптимально, так как СУБД «не поймет», что в условии запроса используется константа (значение параметра Дата).
Поэтому лучше сначала вычислить значение функции во встроенном языке и затем передать его в запрос как параметр (листинг 4.10).
Листинг 4.10. Вывод документов «Поступление товаров» за определенный период – правильный вариант
Использовать параметры виртуальных таблиц
Одним из основных условий оптимального выполнения запросов является максимальное использование параметров виртуальных таблиц во всех запросах, где это не искажает суть выполняемых действий.
Например, нужно получить остатки на складах для номенклатуры, переданной в параметре Номенклатура.
Для решения данной задачи можно написать запрос к таблице остатков регистра накопления ОстаткиНаСкладах и наложить условие отбора по конкретному элементу номенклатуры в предложении ГДЕ (листинг 4.11).
Листинг 4.11. Выполнение запроса для получения остатков номенклатуры с условием отбора в предложении «ГДЕ» – неправильный вариант
Однако такое решение будет неоптимальным, так как сначала виртуальная таблица выберет все записи из таблицы остатков, а затем уже на полученную выборку будет наложен отбор по значению номенклатуры. Это может привести к значительному замедлению работы запроса, хотя результат будет верным.
Для оптимального выполнения подобного запроса следует ограничивать объем выбираемых данных в самой виртуальной таблице. Поэтому такой фрагмент кода следует переписать следующим образом (листинг 4.12):
Листинг 4.12. Выполнение запроса для получения остатков номенклатуры с условием отбора в параметре виртуальной таблицы – правильный вариант
При этом не следует забывать о логике выполнения запроса. Например, в случае накладывания условия отбора на виртуальные таблицы регистра сведений (СрезПервых, СрезПоследних) оба рассмотренных выше варианта (накладывание условия отбора в предложении ГДЕ или в параметре виртуальной таблицы) дадут разный результат, так как в этих случаях прикладной смысл выполняемого запроса меняется.
подробнее
Подробнее этот вопрос рассмотрен в разделе "Получение данных из периодических регистров сведений".
Соответствие индексов и условий запроса
Для построения оптимального плана запроса очень важно наличие подходящих индексов в таблицах – источниках запроса по полям, которые используются в условиях запроса (в предложении ГДЕ, в условии соединения таблиц ПО, в параметрах виртуальных таблиц, в предложении ИМЕЮЩИЕ).
подробнее
Подробные рекомендации по эффективному использованию индексов рассматривались в разделе «Индексирование таблиц».
Не использовать соединения с вложенными запросами и с виртуальными таблицами
В запросах часто требуется получить данные из нескольких источников, связанных по
определенному условию. При этом источниками запроса могут выступать не только реальные таблицы информационной базы, но и виртуальные таблицы, и вложенные запросы.
Соединения с вложенными запросами
Вложенный запрос (подзапрос) часто бывает нужен для того, чтобы отобрать по условию или каким-то образом сгруппировать данные реальной таблицы, прежде чем использовать их в соединении.
При написании таких запросов нужно помнить, что соединение с вложенным запросом может крайне замедлить выполнение запроса, сделать работу запроса нестабильной (чувствительной к используемой СУБД и аппаратуре) и вообще является потенциально опасным моментом для быстродействия системы.
Например, требуется получить остатки товаров из регистра накопления ТоварыНаСкладах для позиций номенклатуры, присутствующих в конкретном документе РеализацияТоваров, которые не являются услугами и остаток которых меньше, чем требуемое количество в документе.
Для этого нужно выбрать номенклатуру из табличной части выбранного документа, исключить номенклатуру, являющуюся услугами, и сгруппировать состав документа по номенклатуре, чтобы для каждого товара получить его суммарное количество (на случай, если один товар включен в состав документа несколько раз). Затем данные этого вложенного запроса (подзапрос выделен в листинге 4.13 жирным шрифтом) нужно связать с данными таблицы остатков регистра накопления по ссылкам товаров и отобрать записи, в которых остаток товара меньше его количества в документе.
В итоге получается довольно громоздкий и запутанный запрос (листинг 4.13).
Листинг 4.13. Выполнение запроса для получения остатков номенклатуры, содержащихся в документе, – неправильный вариант