Discussion :

[j-lo28]

Bonjour,

Je viens de migrer notre base .accdb vers sql server.

J'ai une fonction qui lie mes tables sql dans mon application acces :

Code :

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

DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;Driver={ODBC Driver 17 for SQL Server};Server=*******;Database=*******;Uid=******;Pwd=******", acTable, d!TableName, d!TableName, False, True

Toutes mes interfaces fonctionnent correctement cependant j'ai des lenteurs qui se sont accentuées depuis cette migration.
Afin d'avoir des interfaces IHM communes, j'instancie des form avec différents filtres.

Je construis un string de requête de la manière suivante :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
StrSelect = "SELECT OrdresFabrications.*, OrdresFabrications.EnControle, OrdresFabrications.NumCmd,OrdresFabrications.NumAffaire, OrdresFabrications.[Sous-Traitant], OrdresFabrications.NumAppelOffre, OrdresFabrications.Solde, OrdresFabrications.EnControle, OrdresFabrications.CodeArticle "
 
      Select Case NomForm
 
      Case "Form Général - Non Soldé"
            StrRequest = StrSelect & _
                         "FROM OrdresFabrications " & _
                         "WHERE (((OrdresFabrications.Solde) = False)) " & _
                         "ORDER BY OrdresFabrications.NumOF"

Ensuite je viens associer cette requête au RecordSource de ma form :

Code :

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

Gvar_FormGestionOF2(Gvar_NbFormGestionOF2).RecordSource = StrRequest

Je travaille exclusivement en mode feuille de donnée et il y'a environ 3000 lignes dans certains cas à afficher.
Le temps d'ouverture de la form est très long, environ de 30 à 40sec.

j'ai fait la même chose en faisant une requête access via l'éditeut et en utilisant "SQL Direct" qui correspond au "Pass-Throught"

Code :

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

Gvar_FormGestionOF2(Gvar_NbFormGestionOF2).RecordSource = "Query1"

Le résultat est immédiat.

Avez-vous une méthode pour faire la même chose depuis mes requêtes en VBA.

PS: Après analyse du SQL Profiler en mode requête VBA :

en mode requête via l'éditeur access


Je vois la command BatchStarting qui pour mois est la différence majeur.

Merci

[Jean-Philippe André]

Salut,

puis-je te conseiller de passer par des vues SQL Serveur, que tu utiliseras directement lors de ton appel , pour voir si tu as des améliorations notables en termes de performances rien qu'en passant par ce biais ?

[j-lo28]

Bonjour,

Merci pour la réponse, je viens de créer une vue cotée serveur :


Je l'ai attaché à ma base, le résultat est le même.

Par contre si je passe par l'éditeur de requête access avec SQL Direct, mon affichage est instantanée. Est-il possible de faire ce genre de requête directement en VBA pour ne pas avoir à créer autant de requêtes ("objet dans le volet") que de type de filtre à faire.

Cordialement,

[j-lo28]

Je viens de m'apercevoir que la table via la requête n'est pas éditable ce qui ne convient pas à mon utilisation

[loufab]

Bonjour,

Erratum : Les requêtes Directes ne sont pas modifiables.

Ce qui n'empêche pas de corriger ta requête :

Code :

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

StrSelect = "SELECT OrdresFabrications.*, OrdresFabrications.EnControle, OrdresFabrications.NumCmd,OrdresFabrications.NumAffaire, OrdresFabrications.[Sous-Traitant], OrdresFabrications.NumAppelOffre, OrdresFabrications.Solde, OrdresFabrications.EnControle, OrdresFabrications.CodeArticle "

Cordialement,

[j-lo28]

Bonjour,

Je reviens sur ce sujet car j'ai toujours des lenteurs sur ma base sql en accès avec access.
Nous avons besoin de travailler en mode feuille (Type excel) et de pourvoir modifier plusieurs lignes sans ouvrir une autre fenêtre.

Quelqu'un as t-il une idée pour accélérer le chargement de ces feuilles ?

Merci

[j-lo28]

Bonjour,

je fais un Up sur ce sujet.
Quelqu'un peut-il m'orienter sur une méthode pour accélérer ma base.

Merci,

[Jean-Philippe André]

Salut,

charger moins de donner, en utilisant les TOP LIMIT OFFSET de ce monde ?

Les lenteurs sont-elles uniquement liés à SQL server, ou ton réseau est pas adapté ? Charger 3000 lignes c'est pas un si gros volume de données que ca, donc sans plus de détails, on spécule...

[j-lo28]

Bonjour,

Merci pour le retour, je vais regarder les top Limit.

Ces lenteurs sont apparues après la migration de la base de access vers SQL. Avec la base en acces j'avais de soucis de verrouillage lié au multi-utilisateur je pense.

J'ai 10 utilisateurs qui chargent les mêmes feuilles de données et je pense que c'est le chargement récursif de chaque ligne qui ralentit.

En utilisant la requête en passthrough c'est immédiat, mais la feuille est comme en lecture seule, je vois bien au niveau du serveur que au lieu de faire une requête par ligne c'est le BatchStarting qui démarre.

Le réseau est pour moi correct, mais je ne suis pas expert (Connexion 1Gbit sur les postes, 10Gbit sur le serveur via switch Cisco 3850). Les limits de RAM sont paramétrées sur SQL Server, Partition séparée pour le cache ...

Je penche vraiment pour un souci de code et de méthode d'utilisation des requêtes.

[nico84]

Bonsoir,

Le passage d'access à SQL server gagne en fiabilité et en volume maximum de données mais le gain de temps n'est effectif que si les requetes sont passées en ADO (donc sous-traitées globalement au serveur) sinon au contraire tout va bien moins vite ! De base access fait du DAO (il importe toutes les tables et fait les requetes localement) et par défaut c'est aussi ce que fait le .recordsource

Pour autant dans le cas d'une requete sur une seule table, ADO et DAO vont globalement à la même vitesse, la différence est surtout pour des jointures complexes qu'ADO sous-traite au serveur alors que DAO les décompose en millions de requetes élémentaires

Donc à priori dans les exemples ci-dessus je ne vois rien qui justifie les temps d'ouverture constatés. J'ai 30000 OF et ça s'ouvre en moins d'une seconde avec SQL server comme avec access...

J'ai toutefois qq remarques :

Il faut bien sûr des index sur les champs qui servent à filtrer ou à trier

Code :

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

SELECT OrdresFabrications.*, OrdresFabrications.EnControle, OrdresFabrications.NumCmd,OrdresFabrications.NumAffaire, OrdresFabrications.[Sous-Traitant], OrdresFabrications.NumAppelOffre, OrdresFabrications.Solde, OrdresFabrications.EnControle, OrdresFabrications.CodeArticle FROM...

Cette requete appelle 2 fois tous les champs de OrdresFabrications donc cela ne peut ensuite qu'embrouiller !?
En fait je pense que c'est LE problème car il y a 9 répétitions dans la requete (* plus 8 champs) et on voit que JET fait 9 requetes par OF donc 27000 au lieu d'une seule !

Code :

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

Gvar_FormGestionOF2(Gvar_NbFormGestionOF2).recordsource = StrRequest

Ca fait quoi ce truc ? Je ne connais pas cette syntaxe, pourtant j'en connais plein d'autres :

Code :

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

1
2
3
Me.RecordSource = StrRequest 'ma préférée dans le form_open
form_maform.recordsource = StrRequest 'celle trop bizarre
forms("maform").recordsource = StrRequest 'celle que j'utilise "à distance"

Enfin le code ne dit pas ce qu'est StrRequest mais c'est probablement une string donc le recordsource est bien en DAO

Exemple pour créer un recordsource en ADO pour voir si ça va + vite (malheureusement ce sera en lecture seule...) :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
Dim reca As ADODB.Recordset, Sr as string
 
Sr = "SELECT 0 as cas,  code, date_in, Qin, adrs, iif(cd_cde='1', 'entrée main', cd_cde) as motif, lot, qui, cd_bl_lg, lg, cd_bl as cd, qout, setat" _
& " FROM bl_lg WHERE code='ABCD' " _
& "UNION ALL SELECT [type], m.code, date_out, -m.Qout, adrs, l.cd_cde+'>'+motif, m.lot, null, m.qui, m.cd_bl_lg, lg, m.cd_mvt, 0, l.setat" _
& " FROM mvts m inner join bl_lg l on m.cd_bl_lg=l.cd_bl_lg WHERE m.code='ABCD' " _
& "UNION ALL SELECT -b.nature, b.code, b.date_out, -b.Qout, b.adrs, coalesce(l.cd_cde+'>','') + iif(b.cd_cde='1', 'BL '+cast(cd_blc as nvarchar), b.cd_cde), b.lot, null, b.qui, b.cd_bl_lg, b.lg, b.cd_blc_lg, 0, l.setat" _
& " FROM blc_lg b left join bl_lg l on l.cd_bl_lg=b.cd_bl_lg WHERE (b.nature=1 or (b.nature=3 and b.Qout<0) or b.nature=5) and b.code='ABCD' " _
& " ORDER BY 1"
Set reca = New ADODB.Recordset
reca.Open Sr, cnx, adOpenStatic, adLockReadOnly 'coté serveur, lecture seule
Set Me.Recordset = reca 'ATTENTION cette méthode ne se met pas à jour par requery :(
Set reca = Nothing

Typiquement cette requete prend des plombes en DAO sur SQL server et j'ai dû sacrifier la possibilité de modifier en saisie directe pour avoir un temps d'affichage raisonnable. En DAO sur base access par contre ça va vite...

[j-lo28]

Bonjour,

Merci pour votre réponse, je reviens tard sur le sujet car je ne reçois pas de notification de réponse.

Concernant la première requête :

Code :

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

SELECT OrdresFabrications.*, OrdresFabrications.EnControle, OrdresFabrications.NumCmd,OrdresFabrications.NumAffaire, OrdresFabrications.[Sous-Traitant], OrdresFabrications.NumAppelOffre, OrdresFabrications.Solde, OrdresFabrications.EnControle, OrdresFabrications.CodeArticle FROM...

J'utilise le .* pour récupérer tous les champs, je spécifie certain champs en particulier car j'ai remarqué que cela permet en mode feuille d'avoir les listes dans les filtres, ce que me réclament mes utilisateurs.

Code :

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

Gvar_FormGestionOF2(Gvar_NbFormGestionOF2).recordsource = StrRequest

Effectivement sans le reste ce n'est pas très compréhensible, cela me permet d'instancier une forme avec différentes requêtes :

Code :

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

1
2
Public Gvar_NbFormGestionOF2 As Integer
Public Gvar_FormGestionOF2(0 To 15) As [Form_Gestion des OFV2]

Je limite volontairement le nombre d'instances pour que les utilisateurs n'ouvrent pas trop de fenêtres.

StrRequest est de ce type :

Code :

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

1
2
3
"SELECT OrdresFabrications.*, OrdresFabrications.EnControle, OrdresFabrications.NumCmd,OrdresFabrications.NumAffaire, OrdresFabrications.[Sous-Traitant], OrdresFabrications.NumAppelOffre, OrdresFabrications.Solde, OrdresFabrications.EnControle, OrdresFabrications.CodeArticle " & _
                               "FROM OrdresFabrications " & _
                               "Where " & strfiltre & " ORDER BY OrdresFabrications.NumOF ASC"

ou strfiltre est :

Code :

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

NumOF like 'OF32522'

J'ai créé une possibilité de charger les feuilles en lectures seule et effectivement cela est instantané :

Code :

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

1
2
3
 If DataSetReadOnly = True Then
  Gvar_FormGestionOF2(Gvar_NbFormGestionOF2).RecordsetType = RecordsetTypeEnum.dbOpenDynaset
End If

Cependant je pense qu'il est possible de charger dans un dataset temporaire et de faire un update du/des champs modifié mais je ne sais pas comment gérer ce type de fonctionnement.

[nico84]

J'utilise le .* pour récupérer tous les champs, je spécifie certain champs en particulier car j'ai remarqué que cela permet en mode feuille d'avoir les listes dans les filtres, ce que me réclament mes utilisateurs.

Apparemment cette pratique n'est pas adaptée ici, il vaut mieux lister tous les champs une et une seule fois