Requêtes SELECT très longues

Version imprimable

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:

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.

09/08/2016, 16h58
al1_24

Si j'ai bien lu ton message, tu voudrais optimiser cette requête mais sans en changer une virgule... :weird:
09/08/2016, 17h04
siva_dashq

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.
09/08/2016, 17h29
escartefigue

Citation:

Envoyé par siva_dashq

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

- 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 :weird:, 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
09/08/2016, 17h36
siva_dashq

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:

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

Code:

PE1.ID_ITEM_A

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
09/08/2016, 19h57
escartefigue

Pouvez vous poster le DDL des tables et index, et quelques infos sur les volumes et nombre de valeurs distinctes ?
16/09/2016, 14h07
hotcryx

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.

Citation:
Envoyé par siva_dashq
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:

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:

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:

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 +

06/03/2017, 17h38
mekki_soft

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