Ускорение TADOQuery: Клиентские индексы для быстрого поиска и предотвращения ошибок связей

Delphi , Базы данных , Индексы

При работе с большими данными (>100,000 записей) часто возникает необходимость быстрого поиска и проверки существования ключей для предотвращения ошибок связей (FK violations). В этом случае можно использовать TClientDataSet/TDatasetProvider с методом FindKey, но при больших наборах данных процесс заполнения CDS может занимать больше времени, чем экономит локальный индекс.

В качестве альтернативных решений рассматриваются:

• Использование клиентского курсора с методом TADOQuery.locate
• Использование ADO SELECT-выражений для каждой проверки (без клиентского кэша)
• Использование ADO SEEK-метода
• Расширение TADOQuery для имитации FindKey

Метод Locate кажется простым и не перегружает сервер запросами SELECT/SEEK. Расширение TADOQuery также является интересным вариантом, но автора интересуют готовые решения, а не создание собственного.

Подтвержденный ответ Один из возможных подходов – создание временной таблицы на сервере базы данных, в которую будут вставляться все 100,000 записей. Затем можно выполнять SELECT-запросы к этой временной таблице для проверки ошибок связей. Если все в порядке, то можно выполнить INSERT-запрос из временной таблицы в основную.

Пример кода на Object Pascal (Delphi)

Создание временной таблицы и вставка записей:

procedure CreateTempTableAndInsertRecords(const ADOQuery: TADOQuery);
var
  i, batchSize: Integer;
begin
  // Создание временной таблицы
  ADOQuery.SQL.Text := 'CREATE TABLE #temp (id INT, field1 VARCHAR(50), field2 VARCHAR(50))';
  ADOQuery.ExecSQL;

  // Определение размера пакета для вставки
  batchSize := 3000;

  // Вставка записей в временную таблицу
  for i := 0 to ADOQuery.RecordCount - 1 do
  begin
    ADOQuery.First;
    ADOQuery.MoveBy(i);

    if (i mod batchSize) = 0 then
    begin
      // Вставка пакета записей
      ADOQuery.SQL.Text := 'INSERT INTO #temp (id, field1, field2) VALUES (?, ?, ?)';
      ADOQuery.Parameters.ParamByName('id').Value := ADOQuery.FieldByName('id').Value;
      ADOQuery.Parameters.ParamByName('field1').Value := ADOQuery.FieldByName('field1').Value;
      ADOQuery.Parameters.ParamByName('field2').Value := ADOQuery.FieldByName('field2').Value;
      ADOQuery.ExecSQL;
    end;
  end;
end;

Проверка ошибок связей и вставка записей из временной таблицы в основную:

procedure CheckFKViolationsAndInsertRecords(const ADOQuery: TADOQuery);
var
  tempQuery: TADOQuery;
begin
  // Создание нового ADOQuery для работы с временной таблицей
  tempQuery := TADOQuery.Create(nil);
  tempQuery.Connection := ADOQuery.Connection;

  // Проверка ошибок связей в временной таблице
  tempQuery.SQL.Text := 'SELECT * FROM #temp WHERE NOT EXISTS (SELECT 1 FROM main_table WHERE main_table.id = #temp.id)';
  tempQuery.Open;

  // Вставка записей из временной таблицы в основную, если нет ошибок связей
  while not tempQuery.EOF do
  begin
    // Проверка на наличие ошибок связей
    if tempQuery.FieldByName('id').Value = tempQuery.FieldByName('id').Value then
    begin
      // Вставка записи в основную таблицу
      ADOQuery.SQL.Text := 'INSERT INTO main_table (id, field1, field2) VALUES (?, ?, ?)';
      ADOQuery.Parameters.ParamByName('id').Value := tempQuery.FieldByName('id').Value;
      ADOQuery.Parameters.ParamByName('field1').Value := tempQuery.FieldByName('field1').Value;
      ADOQuery.Parameters.ParamByName('field2').Value := tempQuery.FieldByName('field2').Value;
      ADOQuery.ExecSQL;
    end;

    tempQuery.Next;
  end;

  tempQuery.Close;
  tempQuery.Free;
end;

После выполнения этих процедур можно удалить временную таблицу:

ADOQuery.SQL.Text := 'DROP TABLE #temp';
ADOQuery.ExecSQL;

Таким образом, можно ускорить процесс поиска и предотвращения ошибок связей при работе с большими данными в TADOQuery, используя клиентские индексы или создавая временные таблицы на сервере базы данных.

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

Ускорение TADOQuery: Клиентские индексы для быстрого поиска и предотвращения ошибок связей при работе с большими данными (>100,000 записей).

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

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

:: Главная :: Индексы ::