Использование ADO и DAO для массированного импорта данных

Многие задачи импорта/экспорта данных эффективно решаются стандартными средствами той или иной СУБД (например, bulk copy). Однако, такая проблема может возникнуть и в вашем приложении, например, если используется схема с автономным рабочим местом, синхронизирующимся с центральной БД.

Так как мощность клиентских компьютеров сейчас высока, то миллионы строк в локальной БД уже не являются проблемой для обработки таких объемов, но могут создать узкое место при информационном обмене.

Частным примером является импорт данных в локальную БД MS Access из удаленного источника. По условиям задачи необходимо максимально абстрагироваться от источника данных. Мы заранее не знаем, каким способом пользователь будет синхронизировать свою БД. Выбор MS Access в качестве локальной БД также не является постулатом, несмотря на популярность "движка" и простоту развертывания, возможна миграция в сторону MS SQL Express или FireBird. Поэтому необходимо абстаргироваться и от приёмника.

Задача абстрагирования решается на уровне архитектуры приложения, оставляя, тем не менее, узкое место: собственно копирование данных. Предположим, что у нас имеется объект-адаптер, принимающий на вход табличный DataSet и заполняющий его содержанием таблицу в БД приемника. Адаптер должен быть реализован в соответствии со спецификой этой БД.

Для MS Access наиболее очевидный вариант - использование ADO/ADO.Net, как ключевой технологии доступа к данным в Windows. Менее очевиден ODBC. Еще менее "вспоминаемый" вариант - DAO, хотя он обеспечивает несколько более высокую скорость при работе с JET (MS Access) и Excel.

Для тестирования скорости импорта данных было создано простое приложение (Delphi 2007). Его можно загрузить по ссылке в конце заметки. Тест на 500 тысяч записей подтверждает: при оптимальных настройках, найденных согласно документации и методу научного тыка, ADO уступает DAO всего примерно на 20-30%. Для приёмника ADO/Access были использованы:

Сохранение записей производилось в пакетном режиме с интервалом в 10 тысяч записей.

Target.UpdateBatch(adAffectAll);

Результаты (вы их можете проверить, запустив тестовый пример на своем компьютере):

ADO
Time elapsed: 17,53 sec
Time elapsed per 1000 records: 0,04 sec

DAO
Time elapsed: 12,56 sec
Time elapsed per 1000 records: 0,03 sec

Казалось бы, если хочется выиграть еще несколько процентов - берем DAO. Но не спешите с выводами. Если кроме скорости у вас есть другие критерии, то выбор становится не таким очевидным.

Во-первых, скорость ADO на практике достаточно велика - десятки тысяч записей в секунду. Уверен, в большинстве случаев этого хватит в рамках задачи.

Во-вторых - это важно - компоненты DAO не являются многопоточными (thread safe). Более того, есть официальное ограничение Microsoft, предписывающее использовать DAO только в основном потоке приложения (статья PRB: DAO 3.0 Must Be Used in Primary Thread). В противном случае, вы получаете непредсказуемое поведение (unexpected behavior), которое, например, в нашем случае проявилось скоростью 200-300 записей в секунду (потери на присваиваниях Value полям записи) и периодическим "подвисанием" отладчика вместе с операционной системой (!).

В-третьих, сам источник может отдавать данные с гораздо меньшей скоростью, нежели та, с которой приёмник готов их обрабатывать. Такое возможно, например, при синхронизации через веб-сервис в интернет. То есть, узким местом, требующим оптимизации, будет не запись данных, а их пересылка.

Дополнение

В Delphi обертка над ADO в виде TCustomADODataSet и потомков (TADOQuery, TADOTable и т.п.) имеет неприятную особенность - она замедляет навигацию по записям и полям при относительно большом их количестве. К счастью, это проявляется лишь при больших размерах DataSet - десятки тысяч записей - и статическом курсоре на клиенте.

while not Source.EOF do
begin
    ...
    DoSomething(Source.Fields[i].Value);
    ...
    Source.Next;
end;

В таком типичном цикле обработки происходит существенное замедление, причем оно постепенно нарастает (по наблюдениям, логарифмически). В нашем случае скорость обработки не превышала 1000 записей в секунду. Выход из ситуации - использовать доступ к ADO recordset напрямую.

ADORecordset := TCustomADODataSet(Source).Recordset;
ADORecordset.MoveFirst;
while not ADORecordset.EOF do
begin
    ...
    DoSomething(ADORecordset.Fields[i].Value);
    ...
    ADORecordset.MoveNext;
end;

Скорость при этом возрастает на порядок до пролистывания десятков тысяч записей в секунду.

Сергей Тарасов, июнь 2007

DAOvsADO.zip