Многие задачи импорта/экспорта данных эффективно решаются стандартными
средствами той или иной СУБД (например, 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 были использованы:
CursorLocation | adUseServer |
CursorType | adOpenForwardOnly |
LockType | adLockOptimistic |
Options | adCmdTable (CommandType для Recordset-а) |
Сохранение записей производилось в пакетном режиме с интервалом в 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
Прикрепленный файл | Размер |
---|---|
![]() | 270.93 KB |
Комментарии
Дубликаты
Пишет Гость (не проверено),
Проверил Ваш метод импорта данных - замечательно работает. Но есть одно Большое НО.
Как бороться с дубликатами?
Допустим в таблице уже есть несколько записей. По одному из полей построен уникальный индекс.
Теперь пробуем заимпортировать, скажем, 10000 записей. И среди этих записей попадается одна,
которая приведет к появлению дубликата индекса.
Как скипнуть эту запись, чтобы остальные 9999 вставились?
Два решения
Пишет st,
Есть 2 основных решения:
Проверку во время операции исключаем, т.к. она ухудшит скорость пакетной вставки.
Проверка до вставки - это, например, запрос к источнику данных, в котором вы исключаете дубликаты
Проверка после вставки (сама вставка может оказаться более быстрой): нужно просто удалить временно ключ (ограничение целостности или уникальный индекс), а после вставки проанализировать записи, исключить дубликаты и воссоздать ключ.