Migration sur SQL Server

Version imprimable

2 pièce(s) jointe(s)

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:

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:

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:

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:

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 :
Pièce jointe 610607
en mode requête via l'éditeur access
Pièce jointe 610608

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

Merci

22/12/2021, 22h51
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 ?
23/12/2021, 08h31
j-lo28

1 pièce(s) jointe(s)

Bonjour,

Merci pour la réponse, je viens de créer une vue cotée serveur :
Pièce jointe 610631

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,
23/12/2021, 22h31
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
30/12/2021, 09h57
loufab

Bonjour,

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

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

Code:

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

Cordialement,
29/03/2022, 09h54
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
15/06/2022, 14h24
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,
15/06/2022, 14h38
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...
17/06/2022, 06h57
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.

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:

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:

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:

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:

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...

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:

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:

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:

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:

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:

NumOF like 'OF32522'

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

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.

04/07/2022, 11h57
nico84

Citation:

Envoyé par j-lo28

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