Многие разработчики и системные администраторы сталкиваются с парадоксальной ситуацией: после перехода с файловой базы данных на серверный вариант с использованием MS SQL Server Express скорость выполнения массовых операций (например, загрузка документов) существенно падает. В некоторых случаях разница может быть трехкратной не в пользу SQL-версии. Разберем детально, почему это происходит, и проанализируем шаги, которые помогут сократить это отставание.
Для начала проанализируем ситуацию: файловая база данных 1С — это, по сути, прямой доступ к локальному файлу 1Cv8.1CD. В монопольном режиме (когда работает один пользователь) файловая версия работает крайне быстро, так как отсутствуют накладные расходы на сетевые протоколы, проверку прав доступа СУБД и ведение сложных журналов транзакций. Процесс 1С сам управляет данными в памяти.
В случае с MS SQL Server, даже если он установлен на том же компьютере, мы имеем дело с полноценной клиент-серверной архитектурой. Каждый запрос проходит через несколько уровней: сервер 1С, протокол передачи данных, оптимизатор запросов SQL и механизм записи в журнал транзакций. Как показывает практика, производительность серверной базы в один поток (один сеанс) часто составляет лишь 50–60% от скорости файловой. Однако преимущество SQL-сервера проявляется при одновременной работе множества пользователей, где файловая база начинает «тормозить» из-за блокировок всего файла.
Одной из самых частых причин «торможения» SQL-версии при массовой загрузке данных являются настройки авторасширения файлов .mdf (данные) и .ldf (лог транзакций). Рассмотрим, как это работает: по умолчанию в MS SQL часто стоит прирост файла на 1 Мб. При загрузке 4000 документов база постоянно увеличивается в размере. Если прирост стоит 1 Мб, SQL-сервер вынужден останавливать запись данных, обращаться к операционной системе за выделением нового места, «занулять» его и только потом продолжать запись. Это происходит сотни раз за одну загрузку.
Выясним, как правильно настроить эти параметры в SQL Server Management Studio (SSMS):
ROWS Data) установим авторасширение не в процентах, а в мегабайтах. Оптимальным значением для небольших баз будет 200–500 Мб.Log) установим расширение 50–100 Мб.Как показал опыт автора темы, только это действие позволило сократить время загрузки с 45 минут до 28 минут, что составляет прирост производительности на 40%.
Для баз данных 1С, особенно если вы используете версию Express, критически важно проверить модель восстановления. По умолчанию SQL Server может создавать базу в режиме Full (Полная). В этом режиме СУБД записывает в лог транзакций абсолютно все изменения, чтобы обеспечить возможность восстановления на любую секунду времени. Это создает огромную нагрузку на дисковую подсистему при массовой загрузке документов.
Рассмотрим переход на модель Simple (Простая):
В этой модели лог транзакций автоматически очищается после завершения операций, и в него пишется гораздо меньше служебной информации. Для большинства задач небольших организаций этого достаточно, при условии регулярного создания полных бэкапов. Чтобы изменить модель, зайдите в свойства базы данных, раздел Options (Параметры), и выберите Recovery model: Simple.
Если сервер 1С и MS SQL Server Express крутятся на одной рабочей машине (как в рассматриваемом случае), необходимо убедиться, что они общаются через Shared Memory (общую память), а не через TCP/IP. Работа через TCP/IP даже внутри одной ОС заставляет пакеты проходить через весь сетевой стек, что добавляет микрозадержки к каждой команде INSERT или UPDATE.
Проверим настройки в SQL Server Configuration Manager:
SQL Server Network Configuration.Protocols for [Имя_Вашего_Экземпляра].(local) или . (точку), что часто форсирует использование локального протокола.Проанализируем еще один системный фактор. По умолчанию Windows при расширении файла заполняет новое место нулями. Мы можем разрешить SQL Server пропускать этот этап. Это называется Instant File Initialization.
Для активации выполним следующие шаги:
secpol.msc).NT Service\MSSQL$SQLEXPRESS).Теперь при авторасширении файлов базы данных (AutoGrowth) задержек практически не будет.
Если вы пишете самописную конфигурацию, критически важно, как именно организован код загрузки. Файловая версия прощает отсутствие явных транзакций, так как она блокирует всю таблицу целиком. SQL-сервер же на каждый отдельный объект ДокументОбъект.Записать() инициирует отдельную транзакцию, если вы не открыли ее явно.
Посмотрим на пример того, как делать нельзя (это будет работать очень медленно на SQL):
Для Каждого СтрокаДанных Из ТаблицаДокументов Цикл
НовыйДокумент = Документы.ПростойДокумент.СоздатьДокумент();
// Заполнение полей...
НовыйДокумент.Записать(); // Здесь SQL каждый раз фиксирует транзакцию на диск
КонецЦикла;
Рассмотрим, как оптимизировать этот код с помощью явного управления транзакциями:
НачатьТранзакцию();
Попытка
Для Каждого СтрокаДанных Из ТаблицаДокументов Цикл
НовыйДокумент = Документы.ПростойДокумент.СоздатьДокумент();
// Заполнение полей...
НовыйДокумент.Записать();
КонецЦикла;
ЗафиксироватьТранзакцию();
Исключение
ОтменитьТранзакцию();
Сообщить("Ошибка при загрузке: " + ОписаниеОшибки());
КонецПопытки;
В этом случае SQL Server запишет все 4000 документов в рамках одной операции записи в журнал транзакций, что может ускорить процесс в 10–20 раз.
Не забываем, что MS SQL Express имеет жесткие ограничения, которые невозможно обойти программно:
Если ваша база данных или объем кэшируемых данных превышает 1 Гб, SQL Server начинает постоянно обращаться к диску (свопинг данных в tempdb), что катастрофически замедляет работу по сравнению с файловой версией, которая может использовать всю доступную оперативную память системы через кэш Windows. Если ресурсы позволяют, рассмотрите использование версии Developer Edition (бесплатно для разработки и тестирования) или Standard, чтобы увидеть реальную разницу в производительности.