Мы сталкиваемся с распространенной проблемой: большой и "тяжелый" запрос в 1С, состоящий из множества пакетов (в нашем случае более 70), работает слишком долго на базе MS SQL Server. Возникает вопрос, что будет быстрее и эффективнее: оптимизировать сам запрос 1С или вынести сложную логику в SQL-представление на стороне базы данных и подключить его как внешний источник? Разберем эту ситуацию по шагам, рассмотрим преимущества и недостатки каждого подхода, а также дадим практические рекомендации по оптимизации.
Наш первоначальный вопрос сводится к выбору между двумя подходами к оптимизации сложного запроса. Давайте рассмотрим каждый из них подробнее и выясним, в чем заключаются их особенности и потенциальные "подвохи".
Механизм запросов 1С разработан таким образом, чтобы эффективно извлекать данные из больших массивов. Однако, если запрос написан неоптимально, он может работать медленно из-за огромных объемов данных, неверной логики отбора, сортировки, некорректных условий соединения (JOIN), а также неэффективных планов выполнения и блокировок, возникающих на уровне СУБД.
Прежде чем прибегать к кардинальным изменениям, таким как внешние источники, всегда следует начать с анализа и оптимизации самого запроса 1С. Часто можно добиться значительного ускорения, переписав запрос с учетом рекомендаций 1С и общих принципов оптимизации.
Идея создания сложного SQL-представления в самой базе данных MS SQL и подключения его к 1С как внешнего источника данных звучит привлекательно, поскольку позволяет перенести основную нагрузку по формированию данных непосредственно на СУБД. Это может быть быстрее, если SQL-запрос представления хорошо оптимизирован, и СУБД эффективно использует индексы и планы выполнения. Данный подход часто применяется в интеграционных решениях или для получения данных из разнородных источников.
VIEW, а табличные функции или хранимые процедуры, что еще больше усложнит процесс.
Таким образом, хотя использование SQL-представления имеет потенциал для повышения производительности, оно вносит свои сложности и не всегда является панацеей, особенно для отчетов с динамическими отборами.
Исходя из обсуждения на форуме и нашего опыта, рассмотрим конкретные шаги для оптимизации медленных запросов 1С.
Первым делом, если в вашем запросе присутствуют команды Индексировать, убедитесь, что они действительно нужны, а лучше всего – удалите их. В большинстве случаев платформа 1С и СУБД сами эффективно управляют индексами, и явное указание индексации в запросе 1С может даже навредить, приводя к созданию временных таблиц и ненужным операциям.
Крайне важно следовать рекомендациям 1С по написанию запросов. Мы знаем, что бывает не только "Г..код", но и "Г..запрос". Ссылки на актуальные методические материалы можно найти на портале ИТС, например:
Мы настоятельно рекомендуем изучить эти материалы, чтобы понимать, как платформа 1С взаимодействует с СУБД и какие конструкции запросов наиболее эффективны.
Важным аспектом является структура вашего запроса, особенно если он состоит из 70+ пакетов.
В ИЕРАРХИИ во внутренних пакетах: Использование оператора В ИЕРАРХИИ может быть очень ресурсоемким, особенно если он применяется к большим справочникам или вложенным запросам. Постарайтесь минимизировать его использование, а если без него никак, то применяйте его только на самом первом этапе, получая плоский список элементов.
Номенклатура, Аналитика, Склады, Партнеры и т.д. Это значительно уменьшит объем данных, который будет передаваться по цепочке внутренних пакетов, и ускорит выполнение всего запроса.
В ИЕРАРХИИ, мы можем сначала получить список нужных элементов в одной временной таблице, а затем использовать ее для отборов в последующих пакетах. Например:
ВЫБРАТЬ
Т.Склад
ИЗ
ВашаТаблицаСОтборами КАК Т
ГДЕ
Т.Склад В ИЕРАРХИИ (&СписокСкладовДляОтбора);
;
ВЫБРАТЬ
// ... другие поля
ИЗ
ВашаОсновнаяТаблица КАК ОТ
ГДЕ
ОТ.Склад В (ВЫБРАТЬ ВТ.Склад ИЗ ВТСписокСкладов КАК ВТ)
Этот подход часто используется в типовых конфигурациях 1С, например, для запросов по сегментам в УТ или ЕРП.
Очень сложные и большие запросы часто указывают на то, что мы идем не совсем правильным путем в получении данных. Мы должны подумать, нельзя ли упростить процесс получения информации путем:
Такой подход помогает избежать тяжелых запросов "на лету" и использовать уже подготовленные данные.
Для выявления узких мест в запросе 1С и на стороне SQL Server, активно используйте следующие инструменты:
ВПри большом количестве значений оператор В в SQL может сильно замедлять запрос, так как значение колонки каждой строки поочередно сравнивается с каждым из вариантов. Для составных типов в 1С использование оператора В может приводить к неочевидным проблемам. Если список значений для оператора В очень большой, рассмотрите альтернативы, например, передачу списка значений во временную таблицу и последующее соединение (JOIN) с ней.
В SQL можно использовать JOIN с виртуальной таблицей, созданной с помощью команды VALUES, чтобы избежать полного сканирования таблицы для большого списка значений в IN. Однако в 1С прямой аналог VALUES не всегда удобен, и часто проще работать с временными таблицами 1С.
Помимо оптимизации самого запроса, критически важно, чтобы СУБД MS SQL Server была правильно настроена для работы с 1С. Рассмотрим ключевые параметры.
Мы рекомендуем разнести СУБД (исполняемые файлы SQL Server) и файлы базы данных (.mdf, .ndf) на разные физические диски. Файлы логов (.ldf) также стоит вынести на отдельный быстрый диск. Это значительно повышает скорость работы за счет параллельного ввода-вывода.
Отформатируйте файловую систему дисков (где будут находиться файлы базы данных, логи и системные базы данных) с размером кластера 64 КБ. Это особенно полезно для HDD, но может быть актуально и для SSD, поскольку соответствует размеру страниц данных SQL Server.
Maximum server memory), используемый SQL Server. Мы должны исходить из потребления памяти процессами кластера серверов 1С в часы пиковой нагрузки, добавляя 1 ГБ для "тяжелых" операций. Оставьте достаточно памяти для операционной системы и других приложений.
Lock pages in memory: Включите эту опцию для пользователя, от имени которого работает SQL Server. Это позволит SQL Server резервировать оперативную память и предотвратит ее выгрузку в файл подкачки, что критически важно для производительности.
Параметр Max degree of parallelism (MAXDOP) в MS SQL Server определяет, в сколько потоков может выполняться один запрос. Для баз данных с характерной для 1С нагрузкой (много небольших транзакций) мы рекомендуем установить этот параметр в значение 1. Это может положительно сказаться на работе запросов, особенно если нагрузка на процессор стабильно высокая, уменьшая конкуренцию за ресурсы. Однако на современных серверах с большим количеством ядер и низкой загрузкой процессоров отключение параллелизма может быть не всегда оптимальным; всегда проводите тестирование.
Если сервер 1С и сервер СУБД расположены на одном компьютере (что не рекомендуется для высоконагруженных систем, но встречается), включите протокол Shared Memory. Это самый быстрый способ подключения, поскольку данные передаются через оперативную память, минуя сетевые интерфейсы.
Настройте автоувеличение размера файлов базы данных (.mdf) по 200 МБ, а логов (.ldf) по 50 МБ. Это позволит избежать замедлений при росте файлов, так как операции увеличения файла будут происходить чаще, но меньшими порциями, не блокируя работу базы данных надолго.
Отключайте службы SQL Server, которые не используются 1С. Например, FullText Search (у 1С есть собственный механизм полнотекстового поиска), Integration Services, Reporting Services, если они не задействованы в работе 1С или других систем на этом же сервере. Это освободит системные ресурсы.
Неактуальная статистика SQL Server может привести к тому, что оптимизатор запросов будет строить неоптимальные планы выполнения. Мы должны регулярно обновлять статистику вручную или настроить автоматическое обновление, но с учетом специфики нагрузки 1С (лучше по расписанию в нерабочее время).
Мы должны помнить, что ускорение работы 1С — это комплексная задача, затрагивающая все уровни системы.
Таким образом, хотя идея использования SQL-представления как внешнего источника для большого запроса 1С имеет свои преимущества в части переноса нагрузки на СУБД и возможности тонкой настройки индексов на стороне SQL, она требует очень внимательного подхода к передаче параметров и может значительно усложнить процесс отладки. Часто более эффективным оказывается комплексная оптимизация самого запроса 1С в сочетании с грамотной настройкой MS SQL Server, учитывающей специфику работы платформы 1С. Мы должны тестировать каждый шаг оптимизации, чтобы убедиться в его эффективности.