VBA: Interrogation d'Accès avec Excel. Pourquoi est-elle si lente?
J'ai trouvé ce code en ligne de requête d'Accès et de saisie de données dans excel (2003), mais il est beaucoup plus lent que ce qu'elle devrait être:
Sub DataPull(SQLQuery, CellPaste)
Dim Con As New ADODB.Connection
Dim RST As New ADODB.Recordset
Dim DBlocation As String, DBName As String
Dim ContractingQuery As String
If SQLQuery = "" Then
Else
DBName = Range("DBName")
If Right(DBName, 4) <> ".mdb" Then DBName = DBName + ".mdb"
DBlocation = ActiveWorkbook.Path
If Right(DBlocation, 1) <> "\" Then DBlocation = DBlocation + "\"
Con.ConnectionString = DBlocation + DBName
Con.Provider = "Microsoft.Jet.OLEDB.4.0"
Con.Open
Set RST = Con.Execute(SQLQuery)
Range(CellPaste).CopyFromRecordset RST
Con.Close
End If
End Sub
Le problème est que ce code est très longue. Si j'ouvre l'Accès et il suffit d'exécuter la requête en elle, il faut environ 1/10e de l'heure. Est-il de toute façon à accélérer le processus? Ou toute raison de ce qui pourrait être si long? Toutes mes questions sont simples requêtes select avec de simples où les états et pas de jointures. Même un "select * from [test]" requête prend beaucoup plus de temps qu'il ne le devrait.
EDIT: je dois préciser que la ligne "de la Gamme(CellPaste).CopyFromRecordset TVD" est celui qui prend un temps considérable.
Gamme(CellPaste).CopyFromRecordset TVD
Combien de disques avez-vous récupérer?
Hmm. Cela change tout. Je ne pense pas que vous aurez plus vite que le
CopyFromRecorset
. Aussi, vous devriez sérieusement envisager de renommer ce fil et la suppression de tous les sans pertinence des trucs dans votre post. L'accès n'a clairement rien à voir avec le problème, à mon humble avis.
OriginalL'auteur Dan | 2009-10-15
Vous devez vous connecter pour publier un commentaire.
Je ne suis pas expert, mais je exécuter presque exactement le même code avec de bons résultats. Une différence est que j'utilise le
Command
objet ainsi que laConnection
objet. Où vousJe
Je ne sais pas si ou pourquoi cela pourrait aider, mais peut-être qu'il le fera? 🙂
OriginalL'auteur Ryan Shannon
Depuis que vous utilisez Access 2003, vous utilisez DAO au lieu de cela, il sera plus rapide avec le moteur Jet.
Voir http://www.erlandsendata.no/english/index.php?d=envbadacexportdao pour un exemple de code.
Notez que vous ne devez jamais utiliser le "Comme neuf" mot-clé, car cela conduira à des résultats inattendus.
La même chose pourrait s'appliquer à d'ADO. Ma compréhension est que DAO est en partie de, ou étroitement liées, le moteur Jet utilisé par Access 2003. Il est donc très peu probable qu'il ne soit pas présent le long de l'Accès. Mais vous pouvez toujours vérifier l'emplacement suivant: C:\Program Files\Fichiers Communs\Microsoft Shared\DAO\dao360.dll (en supposant que DAO 3.6) vous aussi, Vous avez besoin de convertir votre code à la fin de liés, et d'utiliser uniquement le code DAO si le fichier est présent.
Il est également présent avec Excel, comme le Jet du moteur db est un composant de microsoft Office et Excel est assez étroitement intégré avec elle.
L'OP a dit, "la requête en elle, il faut environ 1/10e de l'heure". Vous n'avez pas vraiment penser à commutation de DAO rendement de 1000% de gain en performances, pensez-vous?
Non, mais toutes choses étant égales par ailleurs, il sera plus rapide.
OriginalL'auteur JimmyPena
Je vous recommande de créer le
Recordset
explicitement plutôt que de façon implicite à l'aide de laExecute
méthode.Lors de la création explicitement, vous pouvez définir ses CursorType et LockType propriétés qui ont un impact sur les performances.
De ce que je vois, vous êtes en train de charger des données dans Excel, puis fermer le jeu d'enregistrements. Vous n'avez pas besoin de mise à jour, compter le nombre d'enregistrements, etc... Donc mon conseil serait de créer un
Recordset
avecCursorType = adOpenForwardOnly & LockType = adLockReadOnly
:Objet Recordset ADO ()
OriginalL'auteur manji
J'ai utilisé votre code et tirée d'un tableau de 38 colonnes et 63780 lignes en moins de 7 secondes sur ce que j'avais d'attendre et petits jeux d'enregistrements à la fin presque instantanément.
Est-ce le genre de performance que vous rencontrez? Si oui, c'est cohérent avec ce que je m'attend avec une connexion ADO à partir d'Excel dans un fichier MDB back-end.
Si vous voyez beaucoup plus lentement que cela, alors il doit y avoir certaines conditions environnementales locales qui affectent les choses.
OriginalL'auteur Lunatik
Je ne pense pas que la comparaison de comme-avec-comme.
Dans Access, lorsque vous affichez une Requête dataview ce qui se passe est:
(et maintenue ouverte);
avec la première quelques lignes seulement (et
rester ouvert);
grille dédiée à la tâche et
optimisé pour les autochtones d'accès aux données
méthode d'Accès emploie (utilisation directe de
l'Accès au Moteur de Base de données Dll,
probablement).
Dans votre code VBA:
plus tard, fermée et publié);
à l'aide de toutes les lignes (puis plus tard fermé et
publié);
Excel génériques de l'INTERFACE utilisateur à l'aide de non-natifs
composants d'accès aux données.
Je pense que le point le plus important, c'est que la dataview dans l'Accès n'est pas extraire l'ensemble du jeu de résultats jusqu'à ce que vous lui demandez, généralement en accédant à la dernière ligne du jeu de résultats. ADO va toujours chercher toutes les lignes du jeu de résultats.
Deuxième plus important serait le temps de lire les lignes extraites (en supposant un plein jeu de résultats) dans l'élément de l'INTERFACE utilisateur, et le fait d'Excel n'est pas optimisé pour le travail.
D'ouverture, de clôture et de libérer les connexions et les jeux d'enregistrements doivent être insignifiants, mais qui sont toujours un facteur.
Je pense que vous avez besoin de faire quelques timings sur chaque étape du processus pour trouver le goulot d'étranglement. Lors de la comparaison d'Accès, de s'assurer que vous obtenez complet d'un jeu de résultats par exemple, de vérifier le nombre de lignes retournées.
OriginalL'auteur onedaywhen
Beaucoup de formules de référence de la requête. Essayez temporarially tournant sur manuel de calculer dans la macro et de l'éteindre lorsque toutes vos requêtes sont fait la mise à jour.
Cela devrait accélérer un peu, mais ne peut toujours pas résoudre le problème sous-jacent.
OriginalL'auteur Dan
Si vous récupérez un grand nombre d'enregistrements, ça expliquerait pourquoi le
Range(CellPaste)
prend tellement de temps. (Si vous exécutez la requête dans Access, il ne serait pas en mesure de récupérer tous les enregistrements, mais si vous ne le CopyFromRecordset il exige que tous les enregistrements).Il y a un MaxRows paramètre pour CopyFromRecordset:
Essayer si les paramètres de ce à un faible valeur (par exemple 10), des modifications de la performance.
OriginalL'auteur Thorsten
Que dire de la suite de redressements ou des améliorations:
OriginalL'auteur Philippe Grondier
Je ne sais pas si ca va aider, mais je suis de l'utilisation de VBA et ADO pour se connecter à une feuille de calcul Excel.
C'était la récupération des documents ultra-rapide (<5 secondes), mais alors, tout à coup, il était terriblement lent (15 secondes pour récupérer un enregistrement). C'est ce que me conduire à votre post.
J'ai réalisé que je accidentellement avait le fichier Excel ouvrir moi-même (j'avais d'édition).
Une fois que je l'ai fermé, tout s'accélère de nouveau.
OriginalL'auteur RIF
Le problème 9 fois sur 10 est à voir avec le Type de Curseur/Emplacement que vous utilisez.
À l'aide de curseurs dynamiques sur des connexions réseau peuvent ralentir la récupération de données, même si la requête exécutée très rapidement.
SI vous souhaitez obtenir de grandes quantités de données très rapidement, vous aurez besoin d'utiliser CursorLocation = adUseClient sur votre connexion. Ce que cela signifie est que vous n'avez qu'un local statique curseur, donc vous n'aurez pas mis à jour en direct à partir d'autres utilisateurs.
Cependant - si vous vous contentez de lire les données, vous économiserez de l'ADO de revenir à la base de données pour chaque enregistrement individuel pour vérifier les changements.
J'ai récemment changé de ce que j'ai eu une simple boucle, le remplissage d'un élément de la liste, et à chaque tour de boucle a été prise autour de 0,3 s. Pour ne pas ralentir, mais même sur 1 000 dossiers cest de 30 secondes! Changer l'emplacement du curseur de laisser l'ensemble du processus complet en moins de 1 seconde.
OriginalL'auteur SwiftJr