IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
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

SQLite Discussion :

Requêtes SELECT très longues


Sujet :

SQLite

  1. #1
    Membre à l'essai
    Femme Profil pro
    Étudiant
    Inscrit en
    Mars 2014
    Messages
    20
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Âge : 34
    Localisation : France

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : Mars 2014
    Messages : 20
    Points : 20
    Points
    20
    Par défaut Requêtes SELECT très longues
    Bonjour à tous et à toutes,

    J'utilise la librairie sqlite3.
    J'effectue un grand nombre de requêtes SELECT dans le cadre de mon programme, et une requête en particulier est particulièrement longue à exécuter : (j'ai volontairement changé le nom des tables et champs, ne cherchez pas un quelconque sens sémantique)

    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
     
    SELECT DISTINCT  IT1.ID_ITEM, IT1.NOM, IT1.TYPE, IT1.ID_LIVRE
    FROM  table_item IT1, table_item IT2, table_item IT3, table_livre LI1, table_livre LI2, table_livre LI3, table_personne PE1, table_personne PE2
    WHERE  IT1.ID_LIVRE=LI1.ID_LIVRE 
       AND LI1.NUM_LIVRE=444 
       AND IT1.TYPE = 'ITI'
       AND LI2.NUM_LIVRE=444  
       AND IT2.ID_ITEM = PE1.ID_ITEM_A 
       AND IT1.ID_ITEM= PE1.ID_ITEM_B 
       AND IT2.ID_LIVRE=LI2.ID_LIVRE
       AND IT2.ID_ITEM = 197 
       AND LI2.NUM_LIVRE=444  
       AND IT3.ID_ITEM = PE2.ID_ITEM_A 
       AND IT1.ID_OBJET=PE2.ID_ITEM_B 
       AND IT3.ID_LIVRE=LI3.ID_LIVRE 
       AND IT3.ID_ITEM = 173
       ORDER BY IT1.NOM;
    Cette requête, je le sais, est pleine de défauts. On peut faire mieux en terme de construction de la requête, mais je n'ai pas le droit de modifier la requête. Il faut que je trouve d'autres moyens pour améliorer son exécution : en effet, cette requête, qui n'a pas l'air bien méchante, met 20 secondes environ à s’exécuter.
    J'ai donc essayé, en vain jusqu'à maintenant, d'optimiser l'exécution de cette requête sachant ceci :

    Ce que j'ai essayé :
    - Juste pour tester, j'ai bidouillé le code pour modifier le texte de la première query de ce genre, et l'ai remplacé par une query avec des INNER JOIN. Le temps d'exécution chute de 20 à 5 secondes (ce qui reste élevé je trouve). Malheureusement comme je l'ai dit précédemment je n'ai pas le droit de faire ceci.
    - J'ai tenté de créer des indexes afin d'accélérer le parcours des tables. J'ai crée des indexes sur : table_personne(id_item_a) et table_livre(num_livre). Cela a eu pour résultat de tripler le temps d'exécution (???)


    Ce que vous devez savoir : (pertinent ou pas pour la réflexion, à vous de juger)
    - Aucune de mes tables ne comporte de foreign key. De ce que j'ai lu, surtout en ce qui concerne les select, la foreign key n'impacte pas sur la performance.
    - La requête est effectuée sur des tables temporaires créées à partir de vues. Cette requête met donc en moyenne 20 secondes à s'exécuter, mais si je la copie dans SQLiteBrowser et l'effectue sur les vues, elle met quelques millisecondes.
    - Afin de calculer ce temps d'exécution, j'ai effectué une toute petite modification de la fonction sqlite3_exec() : j'ai simplement ajouter un timer en début et fin de fonction et j'affiche le temps écoulé.


    Je ne sais vraiment plus sur quelle piste partir, si quelqu'un a une idée je suis open !
    Si vous avez besoin d'autres informations je suis à votre disposition.

    Merci de votre patience.

  2. #2
    Modérateur
    Avatar de al1_24
    Homme Profil pro
    Retraité
    Inscrit en
    Mai 2002
    Messages
    9 080
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 63
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Retraité
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Mai 2002
    Messages : 9 080
    Points : 30 778
    Points
    30 778
    Par défaut
    Si j'ai bien lu ton message, tu voudrais optimiser cette requête mais sans en changer une virgule...
    Modérateur Langage SQL
    Règles du forum Langage SQL à lire par tous, N'hésitez pas à consulter les cours SQL
    N'oubliez pas le bouton et pensez aux balises
    [code]
    Si une réponse vous a aidé à résoudre votre problème, n'oubliez pas de voter pour elle en cliquant sur
    Aide-toi et le forum t'aidera : Un problème exposé sans mentionner les tentatives de résolution infructueuses peut laisser supposer que le posteur attend qu'on fasse son travail à sa place... et ne donne pas envie d'y répondre.

  3. #3
    Membre à l'essai
    Femme Profil pro
    Étudiant
    Inscrit en
    Mars 2014
    Messages
    20
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Âge : 34
    Localisation : France

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : Mars 2014
    Messages : 20
    Points : 20
    Points
    20
    Par défaut
    Bonjour al1_24,

    D'abord, merci d'avoir répondu.

    Et bien formulé comme cela, ça paraît con, mais disons plutôt que j'essaye dans un premier temps de comprendre pourquoi la requête met autant de temps à s’exécuter à l'intérieur de mon programme, alors qu'à l'aide de SQliteBrowser cela met quelques millisecondes.

    J'en conclus donc que la requête certes n'est pas très joli, mais que 20 secondes d'exec me paraît énorme, et même en la transformant avec des jointures elle met 5 secondes, ce qui me paraît toujours énorme. Tout m'amène à croire que le problème ne se situe pas dans la requête elle-même...

    De plus, l'ajout d'index me rallonge encore plus l'exécution de la requête, ce qui me trouble d'autant plus (130 secondes d'exécution).

    Pour des tables dont le nombre d'enregistrements ne dépasse pas 4000, je trouve tout ceci étrange.

    Merci de votre patience.

  4. #4
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    Mars 2010
    Messages
    10 088
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : Mars 2010
    Messages : 10 088
    Points : 38 393
    Points
    38 393
    Billets dans le blog
    9
    Par défaut
    Citation Envoyé par siva_dashq Voir le message
    mais je n'ai pas le droit de modifier la requête.
    C'est dommage, car le mélange des critères de jointure et de filtrage dans le where nuit grandement à la lisibilité


    Citation Envoyé par siva_dashq Voir le message
    - Aucune de mes tables ne comporte de foreign key. De ce que j'ai lu, surtout en ce qui concerne les select, la foreign key n'impacte pas sur la performance.
    Sauf que sans FK, sur quels critères faites vous vos jointures , il semble tout de même que AND IT2.ID_ITEM = PE1.ID_ITEM_A par exemple utilise bel et bien des FK, c'est à espérer en tout cas

  5. #5
    Membre à l'essai
    Femme Profil pro
    Étudiant
    Inscrit en
    Mars 2014
    Messages
    20
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Âge : 34
    Localisation : France

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : Mars 2014
    Messages : 20
    Points : 20
    Points
    20
    Par défaut
    Bonjour escartefigue,

    Oui, la lisibilité de la requête est miséreuse, j'en conviens, mais j'ai souhaité vous la délivrer telle qu'elle a été généré par le programme.

    En ce qui concerne les FK, la contrainte de foreign key n'est pas formulée à la création de la table.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    AND IT2.ID_ITEM = PE1.ID_ITEM_A
    Il est évident que cette évaluation résonne comme un test entre clé primaire et étrangère, mais n'a pas été défini avec la contrainte FOREIGN KEY à la création de la table.

    Je recherche des pistes pour comprendre pour quoi cela met autant de temps, mes soupçons se portent également sur la librairie sqlite3 elle-même, et là alors peut-être suis-je dans le mauvais forum...


    Cordialement

  6. #6
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    Mars 2010
    Messages
    10 088
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : Mars 2010
    Messages : 10 088
    Points : 38 393
    Points
    38 393
    Billets dans le blog
    9
    Par défaut
    Pouvez vous poster le DDL des tables et index, et quelques infos sur les volumes et nombre de valeurs distinctes ?

  7. #7
    Membre extrêmement actif
    Profil pro
    Développeur
    Inscrit en
    Mars 2012
    Messages
    1 969
    Détails du profil
    Informations personnelles :
    Localisation : Belgique

    Informations professionnelles :
    Activité : Développeur
    Secteur : Communication - Médias

    Informations forums :
    Inscription : Mars 2012
    Messages : 1 969
    Points : 3 375
    Points
    3 375
    Par défaut
    Si ta requête met autant de temps, c'est parce qu'il n'y a pas d'index.
    A la place d'ajouter des conditions dans la clause Where (il y a en à trop à mon goût), tu pourrais rajouter des relations entre les tables (INNER JOIN, LEFT JOIN).
    Ca gagne en clarté et en exécution.
    Si la réponse vous a aidé, pensez à cliquer sur +1

  8. #8
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 736
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 736
    Points : 52 447
    Points
    52 447
    Billets dans le blog
    5
    Par défaut
    Citation Envoyé par siva_dashq Voir le message
    Bonjour à tous et à toutes,

    J'utilise la librairie sqlite3.
    J'effectue un grand nombre de requêtes SELECT dans le cadre de mon programme, et une requête en particulier est particulièrement longue à exécuter : (j'ai volontairement changé le nom des tables et champs, ne cherchez pas un quelconque sens sémantique)

    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
     
    SELECT DISTINCT  IT1.ID_ITEM, IT1.NOM, IT1.TYPE, IT1.ID_LIVRE
    FROM  table_item IT1, table_item IT2, table_item IT3, table_livre LI1, table_livre LI2, table_livre LI3, table_personne PE1, table_personne PE2
    WHERE  IT1.ID_LIVRE=LI1.ID_LIVRE 
       AND LI1.NUM_LIVRE=444 
       AND IT1.TYPE = 'ITI'
       AND LI2.NUM_LIVRE=444  
       AND IT2.ID_ITEM = PE1.ID_ITEM_A 
       AND IT1.ID_ITEM= PE1.ID_ITEM_B 
       AND IT2.ID_LIVRE=LI2.ID_LIVRE
       AND IT2.ID_ITEM = 197 
       AND LI2.NUM_LIVRE=444  
       AND IT3.ID_ITEM = PE2.ID_ITEM_A 
       AND IT1.ID_OBJET=PE2.ID_ITEM_B 
       AND IT3.ID_LIVRE=LI3.ID_LIVRE 
       AND IT3.ID_ITEM = 173
       ORDER BY IT1.NOM;
    .
    Si vous aviez écouté les conseils de tous, vous auriez sans doute déjà constaté une erreur stupide d'écriture. En mélangeant tout dans la clause WHERE vous ne l'avez pas vu...

    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
    SELECT DISTINCT  IT1.ID_ITEM, IT1.NOM, IT1.TYPE, IT1.ID_LIVRE
    FROM  table_item IT1, table_item IT2, table_item IT3, table_livre LI1, table_livre LI2, table_livre LI3, table_personne PE1, table_personne PE2
    WHERE  IT1.ID_LIVRE=LI1.ID_LIVRE 
       AND LI1.NUM_LIVRE=444 
       AND IT1.TYPE = 'ITI'
       AND LI2.NUM_LIVRE=444  --> 1 fois
       AND IT2.ID_ITEM = PE1.ID_ITEM_A 
       AND IT1.ID_ITEM= PE1.ID_ITEM_B 
       AND IT2.ID_LIVRE=LI2.ID_LIVRE
       AND IT2.ID_ITEM = 197 
       AND LI2.NUM_LIVRE=444  --> 2 fois !!!
       AND IT3.ID_ITEM = PE2.ID_ITEM_A 
       AND IT1.ID_OBJET=PE2.ID_ITEM_B 
       AND IT3.ID_LIVRE=LI3.ID_LIVRE 
       AND IT3.ID_ITEM = 173
       ORDER BY IT1.NOM;
    Ensuite vous ne dites pas les index que vous avez créé. Comme SQLlite n'est pas taillé pour des volumes importants, ni pour la concurrence, comme son optimiseur est plus que rudimentaire, et que ses algorithmes d’exécution de requêtes sont grossier, y il faut faire des index qui ont des chances d'être réellement utilisés et un ordre des tables particuliers en se basant sur ce document
    https://www.sqlite.org/optoverview.html

    Récrite ainsi :

    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
    SELECT DISTINCT  IT1.ID_ITEM, IT1.NOM, IT1.TYPE, IT1.ID_LIVRE
    FROM  table_item IT1
          JOIN table_livre LI1             ON IT1.ID_LIVRE=LI1.ID_LIVRE 
          JOIN table_personne PE           ON IT1.ID_ITEM= PE1.ID_ITEM_B
               JOIN table_item IT2              ON PE1.ID_ITEM_A = IT2.ID_ITEM
                    JOIN table_livre LI2             ON LI2.ID_LIVRE = IT2.ID_LIVRE
          JOIN table_personne PE2          ON IT1.ID_OBJET=PE2.ID_ITEM_B 
               JOIN table_item IT3              ON PE2.ID_ITEM_A = IT3.ID_ITEM
                    JOIN table_livre LI3             ON IT3.ID_LIVRE=LI3.ID_LIVRE  
    WHERE  LI1.NUM_LIVRE=444 
       AND IT1.TYPE = 'ITI'
       AND LI2.NUM_LIVRE=444  
       AND IT2.ID_ITEM = 197 
       AND IT3.ID_ITEM = 173
     ORDER BY IT1.NOM;
    êtes vous sur le la jointure : JOIN table_personne PE2 ON IT1.ID_OBJET=PE2.ID_ITEM_B ???

    Sinon, je créerais les index suivants :
    • table_livre --> NUM_LIVRE, ID_LIVRE
    • table_item --> ID_ITEM, ID_LIVRE, ID_OBJET, TYPE, NOM
    • table_personne --> ID_ITEL_B, ID_ITEM_A


    Comme vous ne nous donnez pas le DDL des tables ni la cardinalité, il est difficile de vous aider plus en avant.

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  9. #9
    Futur Membre du Club
    Homme Profil pro
    Développeur informatique
    Inscrit en
    Avril 2011
    Messages
    10
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Algérie

    Informations professionnelles :
    Activité : Développeur informatique
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Avril 2011
    Messages : 10
    Points : 9
    Points
    9
    Par défaut Conditions contradictoires
    Vous avez utilisé une condition contradictoire :

    AND IT2.ID_ITEM = PE1.ID_ITEM_A AND IT2.ID_ITEM = 197

    Cette condition est illogique et ne sera jamais vraie d'où le ralentissement de ton code

    Mekkisoft
    Editeur de logiciels professionnels
    www.mekkisoft.net

Discussions similaires

  1. Requête SQL très longue à s'exécuter
    Par dreamcat1 dans le forum SQL
    Réponses: 10
    Dernier message: 22/03/2010, 12h44
  2. Requête sql trés longue?
    Par naturel dans le forum Langage SQL
    Réponses: 4
    Dernier message: 19/05/2008, 12h04
  3. Exécuter une requête SQL très longue
    Par coraziari_l dans le forum Servlets/JSP
    Réponses: 1
    Dernier message: 10/01/2008, 18h07
  4. Requête très longue à éxécuter
    Par pisoka000 dans le forum Requêtes
    Réponses: 4
    Dernier message: 02/05/2007, 12h31
  5. Réponses: 4
    Dernier message: 09/12/2005, 09h25

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo