Excel temps de réponse

[xeration]

Salut c'est (encore) moi !

Cette fois-ci j'ai un problème assez inquiétant !

J'ai réaliser une application excel qui va chercher sur différent serveur des données dans des bases de données via requête SQL

Cependant Excel lag énormément et met du temps à effectuer des truc basique (juste le faite d'utiliser la roulette de la souris met du temps à s'effectuer)

Je voulais savoir d'où pouvais venir ce lag plutôt inquiétant ... et surtout si c'était possible de le combattre é_è

 

[tbft]

Re : Excel temps de réponse

Coucou

Vu le peu d'information, c'est pas facile...
Serait il possible d'avoir un peu plus d'info???

 

[tototiti2008]

Re : Excel temps de réponse

Bonjour xeration, Bonjour tbft,

Comment as-tu fait le lien entre Excel et tes bases de données ?
En VBA avec ADO ? avec des données externes ?
Quelles sont les bases de données auxquelles tu te connectes ? SQL Server ? Oracle ?
Combien de bases de données ?
Le code SQL envoyé est-il complexe, avec des relations ou des critères complexes ?

Bref, d'accord avec tbft sur sa demande de précisions 😉

 

[xeration]

Re : Excel temps de réponse

Salut, oui bien sûr.

Il s'agit d'une application avec pas mal de requête SQL, qui prends des infos dans des bases de données MySQL qui sont sous différents serveurs. Les requêtes SQL sont de type SELECT ect ...
Apres je ne vois pas trop quoi comme info supplémentaire je peux donner :s


Edit :

voici un exemple de connexion de bases de données (il y en a en tout 2, sous divers tables)

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As New ADODB.Command
Dim sSQL As String
Dim qt As QueryTable

Set conn = New ADODB.Connection


conn.ConnectionString = "xxxxxxxxx;" & _
         "Data Source=xxxxxxxxxxx;" & _
         "Initial Catalog=xxxxxxxx;" & _
         "User Id=xxxxxxxx;" & _
         "Password=xxxxxxxx"
conn.Open

 

[tototiti2008]

Re : Excel temps de réponse

Re,

Quand tu as fini d'interroger ton SQL, tu fermes bien tes connections ? tes recordset ?
Tu réinitialises bien tes variables objets connection, recordset, command à Nothing ?

 

[xeration]

Re : Excel temps de réponse

voici la fin de chaque connexion :

Set rs = New ADODB.Recordset
Set rs.ActiveConnection = conn
Call rs.Open(sSQL, conn, adOpenForwardOnly, adLockReadOnly)



Set qt = Sheets("Detail Job").QueryTables.Add(Connection:=rs, Destination:=Range("V4"))


qt.Refresh

If rs.State <> adStateClosed Then
    rs.Close
End If

Code mise en forme

conn.Close
If Not rs Is Nothing Then Set rs = Nothing
If Not conn Is Nothing Then Set conn = Nothing
End If

End Sub

 

[tototiti2008]

Re : Excel temps de réponse

Re,

ça me semble correct
Le temps de latence est lors des interrogations des bases de données ou en permanence ?
Peut-être du côté de la propriété BackgroundQuery de ton objet Querytable ?

 

[xeration]

Re : Excel temps de réponse

Les temps de latence commence on va dire apres 2 minutes de connexion sur ce fichier excel, même sans lancer de macro

Au final Excel ne répond plus et je suis obliger dans 80% des cas de fermer et de r'ouvrir le fichier

 

[tototiti2008]

Re : Excel temps de réponse

Re,

tu as essayé

qt.backgroundquery = false

?

Les objets QueryTable sont supprimés après coup ?

 

[xeration]

Re : Excel temps de réponse

Salut,

Je place ça à la fin de chaque déconnexion ?

 

[tototiti2008]

Re : Excel temps de réponse

Re,

Pas trop facile à l'aveugle, même si je comprend que tu ne puisse pas nous fournir ton fichier (de toute façon on a pas les bases MySQL qui vont avec)
BackgroundQuery signifie que la requête tourne en arrière plan, j'avais eu des soucis avec des données externes qui n'arrêtaient pas de se mettre à jour et qui prenaient toutes les ressources d'Excel car en arrière plan.
Dans l'absolu, il faudrait mettre la ligne là avant

qt.refresh

mais je ne sais pas ce que tu fais de ta querytable après ça, si tu as besoin qu'elle se mette à jour encore après, etc....
dans l'absolu, si c'est une interrogation à un moment t qui ne doit plus être mise à jour, je te conseille de supprimer ta querytable, genre "qt.delete" après le Refresh

edit :
Ensuite je me demande pourquoi faire une connexion ADO si au bout on en fait un Querytable, si je me connecte en ADO, j'écris le résultat dans la feuille de calcul avec une méthode Copyfromrecordset par exemple
Ou alors je crée un DSN vers ma base et j'utilise directement une QueryTable, mais je ne comprend pas bien pourquoi les deux ?

 

[xeration]

Re : Excel temps de réponse

Bah après avoir fait ça, je n'utilise plus la querytable, donc je met :
qt.backgroundquery = false
qt.refresh
qt.delete

?

Edit :

Pour le double connexion j'avais repris un ancien code d'un autre fichier excel, ça marchait bien donc je me suis pas vraiment posé de question :/

 

[tototiti2008]

Re : Excel temps de réponse

Re,

Oui, essaye déjà ça et dis-nous si ça arrange un peu les choses

 

[xeration]

Re : Excel temps de réponse

ça ne change pas grand chose malheureusement :/

 

[tototiti2008]

Re : Excel temps de réponse

Re,

Plus trop d'idée, comme ça
Il nous faudrait ton classeur (sans données confidentielles) ou au moins l'ensemble du code qui va chercher tes données dans tes bases (change login, password, etc... comme tu l'avais déjà fait dans le message 4)