Проблемы с производительностью SQL-запросов в Delphi после обновления ERP-системы на Bold для Delphi

Delphi , Базы данных , SQL

Введение

В данной статье мы рассмотрим реальный кейс из практики разработки крупной ERP-системы для транспортной логистики, написанной на Delphi с использованием фреймворка Bold для работы с базой данных. После очередного обновления системы разработчики столкнулись с резким ухудшением производительности SQL-запросов, что негативно сказалось на работе 200 пользователей системы.

Контекст проблемы

Команда из четырех разработчиков поддерживает ERP-систему объемом около 9 миллионов строк кода (включая компоненты Devexpress). Особенность системы - использование фреймворка Bold для Delphi, который генерирует 99% SQL-запросов на чтение и 100% запросов на запись.

После выпуска патча 29 апреля в первый день проблем не наблюдалось, но уже 30 апреля начали поступать жалобы на медленную работу системы. Количество медленных запросов (выполняющихся дольше 15 секунд) увеличилось с 80-90 до 200-400 в день, также выросло число deadlock-ов (с 15-25 до 30-40 в день).

Анализ проблемы

Рассмотрим пример одного из проблемных запросов:

SELECT PlanMission.bold_id FROM PlanMission 
WHERE (statePlanOpen > 0 OR stateInProcess > 0) 
AND PlanMission.bold_id NOT IN (SELECT Trailer FROM PlanMission)

Этот запрос, который должен возвращать около 1000 строк, стал выполняться до 58 секунд.

Возможные причины:

1. Изоляция транзакций READ COMMITTED: В системе используется этот уровень изоляции, что означает ожидание незавершенных транзакций при чтении данных.

2. Влияние патча: Хотя изменения в патче были незначительными (добавление нового столбца в таблицу), они могли повлиять на производительность.

3. Внешние факторы: Возможны проблемы на стороне Google Cloud Platform, где размещена система.

4. Конкуренция за ресурсы: Параллельная работа аналитического приложения, интенсивно читающего данные из основной БД.

Решения и рекомендации

1. Оптимизация запросов

Для начала можно попробовать переписать проблемный запрос в более оптимальной форме:

SELECT PM1.bold_id FROM PlanMission PM1
LEFT JOIN PlanMission PM2 ON PM1.bold_id = PM2.Trailer
WHERE (PM1.statePlanOpen > 0 OR PM1.stateInProcess > 0)
AND PM2.Trailer IS NULL

В Object Pascal (Delphi) с использованием Bold это может выглядеть так:

procedure TDataModule.GetActiveMissions;
var
  Query: TBoldUniDACQuery;
begin
  Query := TBoldUniDACQuery.Create(nil);
  try
    Query.SQL.Text := 
      'SELECT PM1.bold_id FROM PlanMission PM1 ' +
      'LEFT JOIN PlanMission PM2 ON PM1.bold_id = PM2.Trailer ' +
      'WHERE (PM1.statePlanOpen > 0 OR PM1.stateInProcess > 0) ' +
      'AND PM2.Trailer IS NULL';
    Query.Open;
    // Обработка результатов
  finally
    Query.Free;
  end;
end;

2. Использование NOLOCK (READ UNCOMMITTED)

Для операций чтения, где не критична абсолютная актуальность данных, можно использовать подсказку NOLOCK:

procedure TDataModule.GetActiveMissionsFast;
var
  Query: TBoldUniDACQuery;
begin
  Query := TBoldUniDACQuery.Create(nil);
  try
    Query.SQL.Text := 
      'SELECT PlanMission.bold_id FROM PlanMission WITH(NOLOCK) ' +
      'WHERE (statePlanOpen > 0 OR stateInProcess > 0) ' +
      'AND PlanMission.bold_id NOT IN (SELECT Trailer FROM PlanMission WITH(NOLOCK))';
    Query.Open;
    // Обработка результатов
  finally
    Query.Free;
  end;
end;

Важно: Этот подход может привести к чтению "грязных" данных, поэтому должен применяться осознанно.

3. Мониторинг и анализ

Для выявления узких мест необходимо:

1. Настроить мониторинг производительности SQL-сервера
2. Анализировать планы выполнения запросов
3. Проверить индексы на часто используемых таблицах

Пример кода для логирования медленных запросов в Delphi:

procedure TDataModule.ExecuteQueryWithLogging(Query: TBoldUniDACQuery);
var
  StartTime: TDateTime;
  Duration: Integer;
begin
  StartTime := Now;
  try
    Query.ExecSQL;
  finally
    Duration := MilliSecondsBetween(Now, StartTime);
    if Duration > 5000 then // Логируем запросы дольше 5 секунд
      LogSlowQuery(Query.SQL.Text, Duration);
  end;
end;

4. Оптимизация работы с Bold

Для систем, использующих Bold для Delphi, стоит рассмотреть:

1. Настройку кэширования объектов
2. Оптимизацию стратегий загрузки данных
3. Проверку корректности маппинга объектов на таблицы БД

Пример настройки кэширования:

procedure TDataModule.ConfigureBoldCache;
begin
  BoldSystemHandle1.System.BoldPersistentHandle.CacheData := True;
  BoldSystemHandle1.System.BoldPersistentHandle.CacheDataTime := 300; // 5 минут
end;

5. Разделение нагрузки

Как выяснилось в ходе анализа, отключение аналитического приложения, интенсивно читающего данные, значительно снизило количество конфликтов. Решением может быть:

1. Перенос аналитики на отдельный сервер репликации
2. Настройка зеркалирования или AlwaysOn Availability Groups
3. Оптимизация расписания выполнения ресурсоемких задач

Альтернативные подходы

Если проблемы сохраняются, можно рассмотреть:

1. Переход на более высокий уровень изоляции транзакций (например, SNAPSHOT ISOLATION)
2. Рефакторинг архитектуры - выделение сервисов для работы с часто изменяемыми данными
3. Внедрение кэширующего слоя (Redis, Memcached) для часто читаемых данных

Пример использования SNAPSHOT ISOLATION:

procedure TDataModule.SetSnapshotIsolation;
begin
  with TBoldUniDACQuery.Create(nil) do
  try
    SQL.Text := 'ALTER DATABASE YourDatabase SET ALLOW_SNAPSHOT_ISOLATION ON';
    ExecSQL;
  finally
    Free;
  end;
end;

procedure TDataModule.ExecuteInSnapshotIsolation;
begin
  with TBoldUniDACQuery.Create(nil) do
  try
    SQL.Text := 'SET TRANSACTION ISOLATION LEVEL SNAPSHOT';
    ExecSQL;
    SQL.Text := 'SELECT * FROM PlanMission WHERE ...';
    Open;
    // Обработка результатов
  finally
    Free;
  end;
end;

Заключение

Проблемы с производительностью SQL-запросов в крупных Delphi-приложениях, особенно с использованием ORM-фреймворков типа Bold, требуют комплексного подхода. В данном случае наиболее вероятной причиной стало увеличение конкуренции за ресурсы БД из-за параллельной работы аналитического приложения.

Рекомендуется:
1. Оптимизировать проблемные запросы
2. Пересмотреть стратегию изоляции транзакций
3. Разделить операционную и аналитическую нагрузку
4. Улучшить систему мониторинга производительности

Для долгосрочного решения стоит рассмотреть возможность постепенного рефакторинга системы с уменьшением зависимости от Bold и переходом на более современные подходы работы с данными.

Создано по материалам из источника по ссылке.

Проблемы с производительностью SQL-запросов в ERP-системе на Delphi после обновления, вызванные увеличением медленных запросов и deadlock-ов из-за конкуренции за ресурсы и уровня изоляции транзакций.

Комментарии и вопросы

Получайте свежие новости и обновления по Object Pascal, Delphi и Lazarus прямо в свой смартфон. Подпишитесь на наш Telegram-канал delphi_kansoftware и будьте в курсе последних тенденций в разработке под Linux, Windows, Android и iOS

:: Главная :: SQL ::