Discussion :

[apprenti46]

Bonjour,

Je travaille actuellement sur un projet ADP (ACC2007) connecté à SQL2005.

Par facilité car je ne connais pas T-SQL, j'ai développé mon code VBA avec des créations de table, insert,update...et cela fonctionne bien mais un peu lent.

Pourriez-vous me donner votre avis les points suivants:

. Est-ce que le code VBA est exécuté sur le poste client? Si oui, c'est donc lui qui fait tout le travail et pas le serveur?

. Est-ce qu'il faudrait utiliser des stored procedures à la place du VBA? Cela va-t-il augmenter la vitesse de traitement?

Merci pour votre avis.

A bientôt,

Frédéric.

[LedZeppII]

Bonjour,

VBA est effectivement exécuté par le poste client.

Les instructions SQL (Transac-SQL) sont exécutées par le serveur.
Même si c'est par le biais de VBA (et ADO je suppose).
En particulier, dans un projet ADP, il n'y a pas de moteur de base de données local.
Tout ce qui est SQL est forcément exécuté par le serveur.

Le gain au niveau d'une procédure stockée ne se fera, à mon avis, que sur la compilation et l'optimisation du code de la procédure stockée.
Pour du code SQL du type CREATE TABLE, INSERT ou UPDATE je ne suis pas sûr qu'on gagne grand chose.

j'ai développé mon code VBA avec des créations de table, insert,update...

C'est du style ...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CurrentProject.Connection.Execute instructionSQL

ou bien avec ADO/ADOX ?

Tes ajouts de données se font-ils à partir de données sur le poste client ?

Il n'est pas facile de répondre si oui ou non "les procédures stockées sont plus rapides que VBA".
Ça dépend du code VBA et du type d'action sur les données.
Sur un SQL Server il y a aussi les DTS, à partir desquels on peut créer des jobs.

A+

[apprenti46]

Bonjour LEDZEPPII,

Merci pour ta réponse.

Apparemment, cela n'ira pas beaucoup plus vite via des stored procedures?

Il arrive également que j'ai le message "Not responding" avec la fenêtre grisée quand le traitement prend un certain temps.
C'est grave docteur? Y a-t-il moyen d'y remédier?

Voici ci-dessous un exemple du code:

-Je détermine ma connexion

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
Dim db As New adodb.Connection
 Set db = CurrentProject.Connection

- je crée une table

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
strTemp4 = "CREATE TABLE " & TableStr & " (PanId Int, Selected Bit, EmpId Int, " & strTEMP & ");"
 db.Execute (strTemp4)

-je la remplis

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 strTemp3 = "INSERT INTO " & TableStr & " (PANID, EMPID)"
 strTemp3 = strTemp3 & " SELECT PANID, EMPID FROM vwBASE_DISPO"
 strTemp3 = strTemp3 & " WHERE ((RefPan not like '%0') and (RefPan not like '%P') and (Demonter = 0) " & IncludeTYPELOC & ")"
strTemp3 = strTemp3 & strTEMPincludeok
 db.Execute (strTemp3)

-je modifie un des champs en fonction du résultat d'une requête

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SQLupdate = ""
SQLupdate = "update " & TableStr & " set " & Form_Mod_Dispo!Périodes.Column(0, nBoucle) & " = '2'"
SQLupdate = SQLupdate + "FROM " & TableStr & " inner join DocDet on " & TableStr & ".Panid = DocDet.IdPan"
SQLupdate = SQLupdate + " WHERE (((DocDet.DateDeb BETWEEN '" & DateDebutTemp & "' And '" & DateFinTemp & "') AND (DocDet.Etat = 7))"
SQLupdate = SQLupdate + " OR ((DocDet.DateFin BETWEEN '" & DateDebutTemp & "' And '" & DateFinTemp & "') AND (DocDet.Etat = 7))"
SQLupdate = SQLupdate + " OR ((DocDet.DateDeb <= '" & DateDebutTemp & "') AND (DocDet.DateFin >= '" & DateFinTemp & "') AND (DocDet.Etat = 7)))"
db.Execute (SQLupdate)
 
SQLupdate = ""
SQLupdate = "update " & TableStr & " set " & Form_Mod_Dispo!Périodes.Column(0, nBoucle) & " = '0'"
SQLupdate = SQLupdate + "FROM " & TableStr & " inner join DocDet on " & TableStr & ".Panid = DocDet.IdPan"
SQLupdate = SQLupdate + " WHERE (((DocDet.DateDeb BETWEEN '" & DateDebutTemp & "' And '" & DateFinTemp & "') AND (DocDet.Etat > 7))"
SQLupdate = SQLupdate + " OR ((DocDet.DateFin BETWEEN '" & DateDebutTemp & "' And '" & DateFinTemp & "') AND (DocDet.Etat > 7))"
SQLupdate = SQLupdate + " OR ((DocDet.DateDeb <= '" & DateDebutTemp & "') AND (DocDet.DateFin >= '" & DateFinTemp & "') AND (DocDet.Etat > 7)))"
db.Execute (SQLupdate)

[LedZeppII]

Bonsoir,

Ton code est (ce que j'appelle) du type

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CurrentProject.Connection.Execute instructionSQL

La méthode Execute demande au serveur de traiter l'instruction sql instructionSQL

Je ne pense pas qu'une procédure stockée apporte grand chose.
De plus le nom de la table est dans une variable, ce qui laisse entendre que la table cible de INSERT et UPDATE peut varier.
Ce n'est pas possible en SQL, donc dans une procédure stockée non plus.

Une procédure stockée est, à mon sens, utile pour des instructions SQL paramétrées.
Exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
ALTER Procedure dbo.NomDeLaProcedure
  @VALEURCHAMPX as varchar(50)
AS
BEGIN
  INSERT INTO LATABLE (PANID, EMPID)
  SELECT PANID, EMPID FROM vwBASE_DISPO
  WHERE (RefPan not like '%0') and (RefPan not like '%P') 
    and (Demonter = 0) and (ChampX=@VALEURCHAMPX)
END

La procédure stockée dbo.NomDeLaProcedure est une instruction SQL d'ajout de données avec un paramètre : @VALEURCHAMPX
Appel depuis VBA

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CurrentProject.Connection.Execute "EXECUTE dbo.NomDeLaProcedure 'ABCD'"

Avantage : * le plan d'exécution de l'instruction SQL est déjà préparé.
Mais bon, ce n'est pas l'élaboration du plan d'exécution qui prend du temps.
* le code VBA est allégé.
Inconvénient : * le nom des tables ne peut pas être mis en paramètre.

Il arrive également que j'ai le message "Not responding" avec la fenêtre grisée quand le traitement prend un certain temps.
C'est grave docteur? Y a-t-il moyen d'y remédier?

Je pense que c'est simplement la méthode Execute qui fige Access, et Windows reporte l'application comme étant "Not Responding".
C'est souvent le cas quand VBA s'exécute.

C'est bizarre que tes traitements soient long. Les requêtes n'ont pas l'air complexes.

Pour conclure, mon sentiment est que la longueur de traitement est du côté du serveur.
Vu ton code, VBA ne manipule que des chaînes de caractères, et attend que le serveur ait exécuté l'instruction soumise par Execute.

A+

[apprenti46]

Un tout grand merci LedZeppII pour m'avoir renseigné très précisément.

Frédéric.

[LedZeppII]

Bonsoir,

Je te mets en lien un exemple d'exécution de commande asynchrone : SqlSvrExecAsynchrone.zip
Ça permet de ne pas attendre la fin de la méthode Execute, et moyennant du code, éviter qu'Access se fige.

Pour cela il faut utiliser un formulaire (comme mon exemple) dans lequel on déclare une variable objet Connection avec événements.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
Option Compare Database
Option Explicit
 
Private WithEvents oCn As ADODB.Connection

Cela permet de soumettre l'exécution de code SQL au serveur, sans attendre la fin du traitement.
Lorsque le traitement est fini, un événement est déclenché pour la variable objet oCn.
Cela permet de savoir quand l'exécution se termine.

Pendant que le serveur exécute la demande, on entre dans une boucle d'attente.
Elle est faite de telle manière que VBA est peu actif, et du coup Access ne fige pas.
Parallèlement à cette boucle, le timer du formulaire change une image du formulaire toutes les 1/4 de seconde,
pour montrer qu'il se passe quelque chose.

Si tu dois enchaîner plusieurs exécutions de commandes, et attendre la fin d'une exécution pour démarrer la suivante,
tu enchaîne plusieurs fois cette séquence :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
   ' Construction SQL
   strSQL = "....."
   ' Mémorise état exécution démarrée
   bExecEnCours = True
   ' exécution asynchrone
   oCn.Execute strSQL, , adAsyncExecute
   ' Attente fin d'exécution
   Call AttendreFinExec

Attention tout de même : pour être rigoureux il faudrait une variable pour mémoriser s'il y a eu une erreur ou pas.
Mon exemple se contente de mettre le message d'erreur dans une zone de texte.

Cette méthode d'exécution (asynchrone) met bien en évidence où se situe le temps de traitement (côté client ou côté serveur).

A+