Add new comment

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

La plupart de cas, l'import ou export des données volumineuses peut être effectué par les outils d'une SGBD, par exemple, "bulk copy" de MS SQL Server. Par contre, il se peut que vous avez besoin de copier les données de masse dans vos applications. Il s'agit souvent d'import/export ou de synchronisation de la base de données locale avec la base de données distante.

La puissance des ordinateurs contemporains de bureau et même portables est tellement fort que le traitement des données volumineuses contenant les tables de dizaines millions de lignes est devenu possible. Par contre l’import ou l’export de données de masse peuvent impacter fortement la performance de vos applications.

Souvent le moteur MS Access/JET sert la base de données locale (cette technologie Microsoft sera remplacée par SQL Server Compact ou Express). Il nous faut donc importer les données à partir de source de données externe. D’autre part, la cible de données locales peut être différent que JET (i.e. SQL Server Compact ou Firebird) et dans ce cas il nous faut utiliser l’abstraction de cette cible et même de la technologie d’accès de données puisque elle peut être spécifique pour la cible donnée.

Il existe plusieurs technologies d’accès de donnes sous Windows, par exemple :

  • ADO / ADO.Net
  • OLE DB
  • ODBC
  • DAO

En cas de MS Access / JET le choix de technologie ADO / ADO.Net est plus évident. ODBC a plus de surcharges et OLE DB a un niveau plus bas par rapport d’ADO qui est simplement l’ensemble des objets COM construits au dessus. Par contre, les composants DAO que vous peut-être ne connaissez même pas avant sont quasi « natifs » et les plus rapides.

Pour faire les tests de performance j’ai créé en Delphi 2007 une petite application qui utilise ADO et DAO. Vous pouvez le télécharger par ce lien : DAOvsADO.zip

Le test d’insertion de 500 000 lignes confirme que DAO est plus rapide, mais l’avantage est mesurée de 20-30% ce que n’est pas énorme. Pour ADO il est recommandé utiliser les paramètres suivants :

CursorLocation adUseServer
CursorType adOpenForwardOnly
LockType adLockOptimistic
Options adCmdTable (CommandType pour Recordset)

La transaction est validée après chaque 10 000 lignes insérées.

Les résultats sur la même PC pas trop puissant (AMD Athlon 3GHz, 1Go de mémoire vivé, disque dur 250 Go, Windows XP Pro) sont :

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

Il vous peut sembler que les composants DAO sont plus rapides et donc il faut les choisir. Mais attendez un instant pour voir les contraints existantes.

Premièrement, la rapidité d’ADO est assez grande déjà : dizaine milles de lignes par seconde environ
Deuxièmes, ce qu’est important, les composants DAO ne sont pas destiné pour travailler dans l’environnement de multithreading. Microsoft préconise n’utiliser DAO qu’en cadre du thread principale d’application (voir PRB: DAO
3.0 Must Be Used in Primary Thread
). Sinon vous aurez le comportement non prédictif d’application, i.e. le ralentissement et le blocage de debugger et même du système d’exploitation. Cela dit, vous ne pouvez pas gérer l’import ou export de données dans le thread autre que principal.

Appendice. Delphi et ADO

En Delphi le composant TCustomADODataSet qui encapsule les manipulations des composants ADO a un problème significatif de performance en cas de dizaines milles lignes de données et le curseur statique du coté client.

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

Dans ce cycle de traitement typique vous pouvez voir le ralentissement de passage vers la ligne suivante. Selon les tests l’abaissement de rapidité passe de manière logarithmique. Dans mon cas la vitesse n’a jamais dépassé 1000 lignes par seconde.

La solution est l’utilisation des composants ADO en direct. Dans ce cas la vitesse de passage vers la ligne suivante augmente à dizaines milles de lignes par seconde.

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