Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Bases de données Delphi Discussion :

ADOQuery Lenteur d'execution requete


Sujet :

Bases de données Delphi

  1. #1
    Candidat au Club
    ADOQuery Lenteur d'execution requete
    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)


    [TH][/TH]
    [TH]clUseClient[/TH]
    [TH]clUseServer[/TH]
    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)


    [TH][/TH]
    [TH]clUseClient[/TH]
    [TH]clUseServer[/TH]
    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)


    [TH][/TH]
    [TH]clUseClient[/TH]
    [TH]clUseServer[/TH]
    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

  2. #2
    Expert éminent sénior
    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
    Aide via F1 - FAQ - Guide du développeur Delphi devant un problème - Pensez-y !
    Attention Troll Méchant !
    "Quand un homme a faim, mieux vaut lui apprendre à pêcher que de lui donner un poisson" Confucius
    Mieux vaut se taire et paraître idiot, Que l'ouvrir et de le confirmer !
    L'ignorance n'excuse pas la médiocrité !

    L'expérience, c'est le nom que chacun donne à ses erreurs. (Oscar Wilde)
    Il faut avoir le courage de se tromper et d'apprendre de ses erreurs

  3. #3
    Candidat au Club
    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.

  4. #4
    Expert éminent sénior
    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
    Aide via F1 - FAQ - Guide du développeur Delphi devant un problème - Pensez-y !
    Attention Troll Méchant !
    "Quand un homme a faim, mieux vaut lui apprendre à pêcher que de lui donner un poisson" Confucius
    Mieux vaut se taire et paraître idiot, Que l'ouvrir et de le confirmer !
    L'ignorance n'excuse pas la médiocrité !

    L'expérience, c'est le nom que chacun donne à ses erreurs. (Oscar Wilde)
    Il faut avoir le courage de se tromper et d'apprendre de ses erreurs

  5. #5
    Rédacteur/Modérateur

    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
    La seule chose absolue dans un monde comme le nôtre, c'est l'humour. » Albert Einstein

    Delphi installés : D3,D7,D2010,XE4,XE7,D10 (Tokyo, Rio, Sidney) et peut être quelques autres
    SGBD : Firebird 2.5, 3, SQLite
    générateurs Etats : FastReport, Rave, QuickReport
    OS : Window Vista, Windows 10, Ubuntu, Androïd

  6. #6
    Candidat au Club
    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.

  7. #7
    Candidat au Club
    Citation Envoyé par SergioMaster Voir le message
    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.

  8. #8
    Expert éminent sénior
    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
    Developpez.com: Mes articles, forum FlashPascal
    Entreprise: Execute SARL
    Le Store Excute Store

  9. #9
    Expert éminent sénior
    Citation Envoyé par Bertrand.Voland Voir le message
    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

    Citation Envoyé par Bertrand.Voland Voir le message
    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 !
    Aide via F1 - FAQ - Guide du développeur Delphi devant un problème - Pensez-y !
    Attention Troll Méchant !
    "Quand un homme a faim, mieux vaut lui apprendre à pêcher que de lui donner un poisson" Confucius
    Mieux vaut se taire et paraître idiot, Que l'ouvrir et de le confirmer !
    L'ignorance n'excuse pas la médiocrité !

    L'expérience, c'est le nom que chacun donne à ses erreurs. (Oscar Wilde)
    Il faut avoir le courage de se tromper et d'apprendre de ses erreurs

  10. #10
    Candidat au Club
    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.

  11. #11
    Rédacteur/Modérateur

    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)
    La seule chose absolue dans un monde comme le nôtre, c'est l'humour. » Albert Einstein

    Delphi installés : D3,D7,D2010,XE4,XE7,D10 (Tokyo, Rio, Sidney) et peut être quelques autres
    SGBD : Firebird 2.5, 3, SQLite
    générateurs Etats : FastReport, Rave, QuickReport
    OS : Window Vista, Windows 10, Ubuntu, Androïd

  12. #12
    Candidat au Club
    Citation Envoyé par SergioMaster Voir le message
    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.

  13. #13
    Rédacteur/Modérateur

    Un truc que je n'ai jamais fini https://www.developpez.net/forums/bl...dbo-brouillon/ suite à l'apparition de Firedac
    La seule chose absolue dans un monde comme le nôtre, c'est l'humour. » Albert Einstein

    Delphi installés : D3,D7,D2010,XE4,XE7,D10 (Tokyo, Rio, Sidney) et peut être quelques autres
    SGBD : Firebird 2.5, 3, SQLite
    générateurs Etats : FastReport, Rave, QuickReport
    OS : Window Vista, Windows 10, Ubuntu, Androïd

  14. #14
    Candidat au Club
    Citation Envoyé par SergioMaster Voir le message
    Un truc que je n'ai jamais fini https://www.developpez.net/forums/bl...dbo-brouillon/ suite à l'apparition de Firedac
    C'est sympa, merci. Je vais m'en inspirer.

###raw>template_hook.ano_emploi###