ADOQuery Lenteur d'execution requete

**Bertrand.Voland** · 27/07/2020, 15h32

Bonjour à Tous,

Bien qu'ayant parcouru plusieurs discussion à ce sujet, je n'ai pas trouvé de réponse à mon problème. Je me permet donc de solliciter le forum en quête d'une aide. Le titre donne déjà le ton de ma demande mais je vais essayer de détailler au mieux possible ma situation.

Mon EDI : Delphi 10.2
Ma Base : Microsoft access *.mdb (375Mo), dans le même répertoire que l'application. Utilisation en Local. Ordinateur i5-6200U / 16Go de RAM
Dans mon application
Un AdoConnection : Microsoft Jet 4.0
Un AdoQuery : Pour le test, j'ai utilisé les deux configurations (clUseClient et clUseServer), rien d'autre sinon que la connexion
Un dataSource : Rien de particulier à dire
Un DBGrid : tout en valeur par défaut
Un bouton pour lancer ma requête

Le code de ma 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
16
17
18
19
20
21
22
23
procedure TFEN_Principale.Button2Click(Sender: TObject);
Var
  Start : Integer;
begin
  Start := GetTickcount;
  if Edit2.Text <> '' then
  Begin
    Edit1.Text := 'Requête en cours....';
    Screen.Cursor :=  crSQLWait;
    Application.ProcessMessages;
    DM_Principale.QRY_DetailDatalog.DisableControls;
    With DM_Principale do
    Begin
      QRY_DetailDatalog.Active := False;
      QRY_DetailDatalog.SQL.Clear;
      QRY_DetailDatalog.SQL.Add(Edit2.Text);
      QRY_DetailDatalog.Active := True;
    End;
    DM_Principale.QRY_DetailDatalog.EnableControls;
  End;
  Screen.Cursor :=  crDefault;
  Edit1.Text := IntToStr(GetTickCount-Start);
end;

La table vers laquelle je pointe comporte 545558 enregistrements et pourra grossir significativement. Chaque enregistrement comporte une dizaine de champs (string et integer pas de Blob)
Pour mesurer la vitesse j'utilise 3 requêtes. Chaque requête est lancée 5 fois pour estimer le temps. Chaque requete a été lancée avec le cursor Location dans les deux choix possibles. Ci-apresles temps en ms pour chaque test

Requête 1 : SELECT * FROM Principale (545558 enregistrements)

	clUseClient	clUseServer
1	151 406	219
2	150 813	203
3	174 250	172
4	151 656	203
5	152 037	172

Requête 2 : SELECT DISTINCT MySite FROM Principale (2 Enregistrements)

	clUseClient	clUseServer
1	152 703	79 422
2	215 515	160 578
3	207 484	195 375
4	182 296	151 280
5	154 297	144 406

Requête 3 : SELECT * FROM Principale WHERE MySite LIKE "KAN" (365190 enregistrements)

	clUseClient	clUseServer
1	147 672	142 000
2	160 438	151 765
3	207 484	113 031
4	182 296	148 234
5	154 297	147 797

On en arrive à la question. Ces résultats sont ils normaux selon vous? 150 secondes pour obtenir un résultat sur une base en local?
Est-ce une erreur dans mon utilisation du composant ADO ?
Quelle alternative me proposés vous ?

Merci pour votre aide

**ShaiLeTroll** · 27/07/2020, 16h37

Triste même constat sur ADO mais SQL Server pour un gros SELECT * FROM Table avec clUseClient particulièrement lent pour la partie Fetch (et encore, le DBGrid va chercher que la fenêtre visible)

Pour un affichage, 545558 enregistrement n'a aucun sens, mais 150s pour ouvrir une grille, c'est inutilisable
par contre, 545558 enregistrement pour un export dans un fichier, c'est tout à fait crédible et c'est juste inadmissible que le Open soit aussi lent, et votre code ne montre pas votre mesure du Fetch qui n'est pas négligeable

MySite LIKE "KAN" ... pas de % quelque part ?

J'ai pu comparer ADODB directement en OLE LateBinding, et le TADOQuery en clUseClient, il y a clairement une anomalie durant le Fetch sur SQL Server, à vérifier cela avec ACCESS
Je n'ai rien observer d'alarmant sur le Open
J'ai du mal à croire que cela n'ait gêne personne ... en même temps, tout le monde déteste ADO, très vite ça passe au pire via DBX, FireDac ou les composants payants comme AnyDac, UniDac ...

j'aurais cibler plus précisément la mesure pour savoir quelle est l'opération lente, le Open (Active) ou un début de Fetch par la DBGrid associé (EnableControls)

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
16
17
18
19
20
21
  if Edit2.Text <> '' then
  Begin
    Edit1.Text := 'Requête en cours....';
    Screen.Cursor :=  crSQLWait;
    Application.ProcessMessages;
    DM_Principale.QRY_DetailDatalog.DisableControls;
    With DM_Principale do
    Begin
      QRY_DetailDatalog.Active := False;
      QRY_DetailDatalog.SQL.Clear;
      QRY_DetailDatalog.SQL.Add(Edit2.Text);
      Start := GetTickcount;
      QRY_DetailDatalog.Active := True;
      Edit1.Text := IntToStr(GetTickCount-Start);
    End;
 
    Start := GetTickcount;
    DM_Principale.QRY_DetailDatalog.EnableControls;
    Edit1bis.Text := IntToStr(GetTickCount-Start);
  End;
  Screen.Cursor :=  crDefault;

Je n'ai pas fait de local depuis longtemps, mais avec Paradox BDE, l'ouverture avec l'index primaire étant instantané sur un TTable par contre, je n'ai pas souvenir d'avoir utiliser des SELECT retournant plus de 1 à 100 lignes, plus était je crois tout à fait infame en terme de performance, un Locate sur un TTable était plus rapide qu'un SELECT mais à l'époque Win98/2K, BDE de D4-D6, 128-256Mo de RAM, PIII 750Ghz

**Bertrand.Voland** · 27/07/2020, 17h50

Merci pour cette reponse rapide.

Je ne voyais pas comment dissocier la mesure de temps d’exécution. Sur cette meme base j'ai donc executé ce nouveau code avec les deux mesures de temps sur les deux requetes qui sont les plus longues. Pour chaque requete j'ai fait trois mesures successives les résultats sont sous la forme Open/EnableControls.
Le résultat est intéressant, on voit que les temps sont inversés pour les deux requetes qui etaient les plus longues. Je comprends que les affichages puissent etre dans cette proportion mais si on regarde la requete qui me renvoie l'integralité de la table, le temps est super court. Je vais essayer d'adapter mes requetes mais je ne vois pas comment faire lors de l'utilisation de la clause DISTINCT que je prevois d'utiliser dans plusieurs cas.

SELECT * FROM Principale where MySite LIKE "KAN"
15/140563; 0/143172; 0/143000

SELECT DISTINCT MySite FROM Principale
136860/15; 142062/0; 136828/0

SELECT * FROM Principale
0/188; 0188; 0/172

C'est la premiere fois que je travail avec une grosse base. Jusqu'à present je gérais au max 20 000 enregistrements et l'utilisation de ADO m'allait bien. Je ne suis pas programmeur et j'utilise la programmation pour me créer des outils qui me permettent d'automatiser certaines activités dans mon metier. Ce type d'utilisation me permet d'utiliser ces applications sans necessiter d'acces administrateurs.

**ShaiLeTroll** · 28/07/2020, 09h16

MySite LIKE "KAN" ... toujours pas de % ou * quelque part ? que donne MySite = "KAN" ?

Il va peut-être falloir modifier votre structure de table
dans la table de données, vous ne stocker par le Site mais un numéro de Site, dans une table Site, vous faites l'association, c'est un thésaurus, si Site est enrichi, cela devient une relation entre deux Entités.
Si vous souhaitez la liste des Sites, une simple requête sur la table Site plus besoin d'un DISTINCT sur une grosse table fourre-tout.
C'est la stratégie "Diviser pour Régner"

Pour le LIKE, ajouter des index peut aussi améliorer les performances de lecture (ralentir un peu l'écriture mais rien de grave)
D'ailleurs pour le DISTINCT aussi, l'index contient déjà les données agrégées avec une table\arbre liant la valeur avec ses positions dans la table

Pour moi, 500 000 enregistrements, c'est une toute petite base, même sur Paradox, il y a 20 ans, c'était juste la table catalogue, une table parmi 20 à 200 autres pour certains projets.
J'ai souvenir justement d'un projet où il fallait mixer 20 tables (nomenclature VIDAL, CIM10 et CCAM) pour obtenir les données agrégées, trop long en SQL Paradox, un programme transformait ça en une énorme table fourre-tout.
Selon le besoin utilisateur, soit cela utilisait l'énorme table soit cela utilisait les petites selon ce qui était le plus performant.
Et sur un autre projet, j'ai fait exactement l'inverse, diviser en pleine de petites tables, d'index, de relation pour que cela fonctionne mieux sur SQLServer ou Oracle

Je ne connais pas ACCESS mais le SQL Local n'est pas réputé performant.
Par contre 500 000 enregistrements en Paradox correctement indexé, le Locate et filter fonctionnait mieux que le SQL, alors qu'avec SQL Server, MySQL, Oracle ... c'est totalement l'inverse, heureusement lorsque l'on a des jointures entre des tables qui ont 250 000 000 et 1 000 000 000 d'enregistrements, la construction des clés primaires, clés étrangères et index devient stratégique.

Faudrait voir ce que donne SQLite en terme de performance mais si c'est une activité en assistance de votre vrai travail, c'est un investissement temps assez lourd

**SergioMaster** · 28/07/2020, 14h03

Bonjour,

je n'ai jamais été fans d'ADO ou MS-ACCESS cette discussion me conforte.
Cela dit je n'ai jamais été un fan des SELECT *Un truc qu'il faudrait vérifier c'est le temps utilisé par le prepare avant l'exécution proprement dite de la requête, enfin si Prepare existe avec ADO

**Bertrand.Voland** · 28/07/2020, 14h40

Merci beaucoup pour ce retour.

Pour le MySite, je n'avais pas pris la peine de mettre des % car il ne peut prendre que deux valeurs KAN ou KAS. Cette base est populée par une application que j'ecrit et je sais que ce champs ne peut prendre que ces deux valeurs.

Je vais donc modifier ma façon de penser et revoir pour utiliser une base paradox plutot que Access. Comme c'est moi qui renseigne cette base via une autre application, je peux choisir ce qui me semble le mieux. D'un autre côté, je vais revoir ma façon de faire pour diviser les données et utiliser des index. Je ne suis pas encore au fait des Index mais le forum est une mine d'or pour les tutoriels. Je vais m'y mettre au plus tôt.

Effectivement, c'est une occupation en complément de mon activité principale. La programmation ayant toujours été un hobbie, je prendrais quelques heures sur mon temps libre pour m'y mettre.

Merci encore pour tous ces conseils.

**Bertrand.Voland** · 28/07/2020, 14h47

Envoyé par SergioMaster

Bonjour,

je n'ai jamais été fans d'ADO ou MS-ACCESS cette discussion me conforte.
Cela dit je n'ai jamais été un fan des SELECT *Un truc qu'il faudrait vérifier c'est le temps utilisé par le prepare avant l'exécution proprement dite de la requête, enfin si Prepare existe avec ADO

Bonjour,

Le couple ADO/MS-ACCESS était le premier que j'avais trouvé lorsque le besoin s'en été fait sentir. Je vais m'essayer à Paradox et si j'y arrive, je pourrais voir la différence. D'un autre côté, je pense que je dois avant tout prendre le temps de travailler correctement et d'exploiter les bases d'une meilleure façon. Je vais donc prendre le temps de me documenter sur l'utilisation des index et repenser au mieux mes différentes tables.

Merci pour ces informations constructives.

**Paul TOTH** · 28/07/2020, 14h55

paradox ça marche bien, mais c'est une tellement vieille technologie que je ne la recommanderais pas

si tu veux faire une sélection sur une valeur, utilise "=" au lieu de "LIKE", ça serait plus rapide, et surtout ajoute un index sur la colonne en question

**ShaiLeTroll** · 28/07/2020, 15h09

Envoyé par Bertrand.Voland

Pour le MySite, je n'avais pas pris la peine de mettre des % car il ne peut prendre que deux valeurs KAN ou KAS. Cette base est populée par une application que j'ecrit et je sais que ce champs ne peut prendre que ces deux valeurs.

Cela renforce encore plus l'idée d'une table MySiteType contenant
1 = KAN
2 = KAS
et dans la table Principale, cela contient 1 ou 2 (voire NULL), indexé, en clé étrangère sur MySiteType

Réfléchissez à chaque colonne qui vous souhaitiez lister avec DISTINCT, faut le voir dans le sens inverse, il y existe une table par Type contenant une association Clé = Valeur, et c'est la Clé qui est utilisé dans les autres tables avec des jointures

Envoyé par Bertrand.Voland

Je vais donc modifier ma façon de penser et revoir pour utiliser une base paradox plutot que Access.

Surtout pas !
Paradox et BDE, c'est obsolète, à oublier !

Paradox c'est ma base de comparaison pour une base locale, je ne pratique pas ACCESS, je n'apporte que mes expérience sur un SQL traité en local qui calcule souvent tous les résulats avant de les renvoyer, ce qui est un temps lourd, contrairement à un SGBD relationnel, avec un modèle bien construit qui diffère ses calculs (voir pagination)

Si vous souhaitez investir du temps SQLite c'est une idée,
même si je conseillerais MySQL / MariaDB pour la richesse des outils dessus, la communauté ...
Avant, bien avant de changer, travailler sur ACCESS pour les index et les tables secondaires car un mauvais modèle sera lent partout !

**Bertrand.Voland** · 28/07/2020, 17h14

Merci à Tous,

Finalement, après lecture de vos recommandations, je comprends que le problème n'est pas vraiment l'utilisation de ADO/MS-ACCESS mais plutôt mon utilisation des bases de données.
Je vais dans un premier temps garder mon application simplifiée (ADOQuery/Dataset/DBGrid/EDIT et button) pour faire les essais. Par contre, je vais travailler à structurer une base de données en utilisant les indexes, à organiser mes données sur différentes tables et apprendre à les lier entre elles.

Je vais donc noter ce fil de discussion comme résolu car avant de penser à un problème de programmation, je vais d'abord régler le probleme de la base elle-meme.

**SergioMaster** · 28/07/2020, 17h50

Je vous conseille plutôt l'utilisation des composants Firedac (si vous avez une version hadhoc, vous nous avez indiqué 10.2 mais pas s'il s'agissait d'une version starter, pro, entreprise ou architecte ) ou l'utilisation de ZEOSDBO (composant tiers) pour attaquer une base de données SQLite ou plus pour du multi utilisateur : PostgresSQL ou Firebird pour rester dans le libre. Je suis moins partisan de Mysql ou MariaDB (question de goût et de possibilités)

**Bertrand.Voland** · 28/07/2020, 18h21

Envoyé par SergioMaster

Je vous conseille plutôt l'utilisation des composants Firedac (si vous avez une version hadhoc, vous nous avez indiqué 10.2 mais pas s'il s'agissait d'une version starter, pro, entreprise ou architecte ) ou l'utilisation de ZEOSDBO (composant tiers) pour attaquer une base de données SQLite ou plus pour du multi utilisateur : PostgresSQL ou Firebird pour rester dans le libre. Je suis moins partisan de Mysql ou MariaDB (question de goût et de possibilités)

Merci Beaucoup,
Je vais regarder vos différentes propositions. Il me semble avoir déjà lu quelque chose sur ZEOSDBO et cela pourrait etre une des solutions finales. Je vais commencer par structer mes donnees sur le papier et je les porterais ensuite dans une base qui reste à definir.