Requête avec une performance minable

[Oz-WereWolf]

Bonsoir.

Au boulot, nous avons un souci avec une requête.

Nous stockons dans plusieurs tables une série d'évènements dont certains sont datés.

Certains d'entre eux n'ont pas de date de fin définie. Le champ vaut donc null.

Nos critères ressemblent donc à ça :

Code :

1
2
WHERE date_debut <= SYSDATE()
AND (date_fin IS NULL OR date_fin >= SYSDATE())

Les clients utilisent des bases Oracle 8, 9 ou 10, c'est variable.

Avec cette requête, on peut avoir des temps de traitement dépassant les 3 minutes.

Maintenant, si on alimente tous les champs date_fin qui valent NULL avec la date du 31/12/9999 (solution trouvée par notre responsable technique) et qu'on transforme la requête en

Code :

1
2
WHERE date_debut <= SYSDATE()
AND date_fin >= SYSDATE()

La requête est quasi immédiate.

Est-ce la bonne solution ?

Si non, que faudrait-il faire ?

Merci de votre aide.

[nuke_y]

J'imagine qu'il y a un index sur date_debut, date_fin ? Je crois que les valeurs null ne peuvent pas être indexées, ce qui expliquerait le problème.

[Oz-WereWolf]

La date de début est indexée, pas la date de fin.

Mais d'après ce que j'ai pu lire, à partir du moment où il y a un "IS NULL" dans la requête, aucun index n'est utilisé.

Ma question était surtout de savoir comment gérer ce cas.

J'imagine qu'avoir une date de fin non renseignée est un cas qui doit se rencontrer fréquemment et je me demande comment c'est géré dans les autres applications.

Est-ce que l'astuce de mettre une date au 31/12/9999 est universelle ou existe-t-il d'autres solutions moins contraignantes ?

[Vinchou]

Je pense que mettre 31/12/9999 est la meilleure solution, ça simplifie les requêtes et ça améliore grandement les performances. Sinon par expérience il me semble que les OR sont très coûteux en temps d'exécution. Tu peux essayer avec

Code :

WHERE sysdate() BETWEEN date_debut AND NVL(date_fin,999999)

999999 représentant à peu près une date de l'année 4639, mais tu peux mettre la valeur de la date 31/12/9999 si tu veux.

[nuke_y]

Il y a un truc qui me gène quand même... Je veux bien que le IS NULL soit consommateur de charge mais si la date de début est indexée, et qu'il n'y a qu'elle, j'ai du mal à comprendre la différence de perfs.

Je me pose une question de volumétrie. Ta condition date_debut <= SYSDATE doit te ramener quasiment toute ta table en fait non ? Ca m'étonnerai que tu ai beaucoup d'événements dont la date de début soit anti-datée, puisqu'elle aurait lieu AVANT la date de requete.

Donc en gros, utiliser l'index sur date_debut te ramène toute la table. Par contre le critère date_fin >= SYSDATE lui est assez sélectif puisqu'il te ramène les cas qui sont encore actifs, filtrant tous ceux qui se sont terminés.

Tu as quoi comme volumétrie sur ta table :
- sans condition
- avec la condition date_debut <= SYSDATE uniquement
- avec la condition date_fin >= SYSDATE uniquement
- avec la condition date_fin IS NULL uniquement
- avec la condition date_fin >= SYSDATE OR date_fin IS NULL uniquement
- avec toutes les conditions

[Oz-WereWolf]

Envoyé par nuke_y

Je me pose une question de volumétrie. Ta condition date_debut <= SYSDATE doit te ramener quasiment toute ta table en fait non ? Ca m'étonnerai que tu ai beaucoup d'événements dont la date de début soit anti-datée, puisqu'elle aurait lieu AVANT la date de requete.

Ce n'est pas le cas.

Cette table contient les différentes situations par lesquelles peut passer une personne.

La requête ramène la situation en cours.

La table en elle même doit contenir plus de 8000 lignes. La requête en renvoie autour de 3000 (mais ça varie d'un client à l'autre).

Le volume est relativement faible mais il y a des jointures sur d'autres tables qui compliquent la requête.

Toujours est-il qu'en supprimant la clause "IS NULL" et en alimentant les dates de fin nulles avec 31/12/9999, le temps de traitement passe de plusieurs minutes à moins de 5 secondes.

[orafrance]

Envoyé par Oz-WereWolf

Est-ce la bonne solution ?

Non puisque tu enléves un OR qui évidemment rend la requête plus rapide mais change aussi le résultat

sans index sur date de fin tu n'as aucune chance que ça fonctionne... l'idéal serait d'updater la colonne pour mettre une très grande valeur à la place de NULL

[Oz-WereWolf]

Envoyé par Fred_D

sans index sur date de fin tu n'as aucune chance que ça fonctionne... l'idéal serait d'updater la colonne pour mettre une très grande valeur à la place de NULL

Envoyé par Oz-WereWolf, au début

Maintenant, si on alimente tous les champs date_fin qui valent NULL avec la date du 31/12/9999 (solution trouvée par notre responsable technique) et qu'on transforme la requête en

Code :

La requête est quasi immédiate.

C'est ce qu'on a fait. Est c'est là dessus que je demandais si c'était la bonne solution.

[orafrance]

Oui sauf que tu exclus toutes les lignes avec date de fin NULL alors forcément c'est plus rapide... tu comprends que c'est pas la même requête ?

si tu fais WHERE 1=0 ce sera instantané aussi mais le résultat sera faux

[Oz-WereWolf]

Je crois que je ne suis pas clair.

J'ai écris au dessus qu'il n'y avait plus de champs avec NULL parce qu'ils avaient tous été alimentés avec la valeur 31/12/9999.

Code :

UPDATE TABLE SET date_fin = 31/12/9999 WHERE date_fin IS NULL

(en gros)
Et, à moins de vraiment rien paner au truc, supprimer les valeurs nulles et enlever le critère sur le null me semble être la même chose que la requête précédente.

Non ?

[nuke_y]

Envoyé par Oz-WereWolf

Maintenant, si on alimente tous les champs date_fin qui valent NULL avec la date du 31/12/9999 (solution trouvée par notre responsable technique) et qu'on transforme la requête

Pas de problème Fred, il voulait juste savoir s'il n'y avait pas une meilleure solution.

D'ailleurs, pendant que j'y pense : pourquoi ne pas faire cet update, puis interdire le null sur la date de fin et mettre comme valeur par défaut 99999 ?

[plaineR]

As-tu comparer le plan d'exécution de tes 2 requêtes ? Les statistiques sont elles à jour ?

[remi4444]

Si ta table contient dans les 8000 lignes, je vois pas comment tu peux dépasser 3 minutes... (à moins d'avoir une base sur une disquette ).

Tu n'aurais pas d'autres tables impliquées dans ta requête par hasard ??


[EDIT]

Combien de colonnes (avec quelle taille en gros) contient ta table ?

[/EDIT]

[nuke_y]

Il a dit plus haut qu'il y avait d'autres tables dans la requête.

Alors comme je le disais plus haut, je pense que le problème ne vient pas directement des date_fin IS NULL mais plutôt que la présence ou l'absence de date_fin IS NULL provoque des plans d'exécution différents qui peuvent être idéaux ou catastrophiques.

La solution de mettre une date par défaut fonctionne et c'est sûrement la meilleure solution si on ne veut pas chercher plus loin. Cependant je pense que ça pourrait valoir le coup de jeter un oeil au problème global.

[orafrance]

OR = UNION ALL = 2 requêtes exécutées... tu enléves le OR tu enléves le UNION ALL et c'est plus performant... CQFD

Avec les plans d'exécution tu comprendrais immédiatement pourquoi tu as ce comportement

[nuke_y]

Certes mais même avec 2 requêtes exécutées... Entre 2 * 1s et 3mn, il y a une différence.

[remi4444]

Envoyé par nuke_y

Il a dit plus haut qu'il y avait d'autres tables dans la requête.

c'est vrai j'avais mal lu....

Donc pour moi le fait de prendre une solution ou l'autre n'est pas le soucis, c'est juste une pichnette qui fait basculer le plan d'exéctution général de la requête d'un coté ou de l'autre. Donc meme en remplaçant les null, ça risque de "résoudre" par effet de bord sans aucune stabilité ni certitude dans le temps.
En soit, ramener 3000 lignes sur 8000 ne pourra en aucun cas s'améliorer spectaculairement avec quelquonque index (on considère qu'un index n'améliore qu'à partir d'une discrimination d'1/4, et encore dans la pratique je mets la barre beaucoup plus bas...). Tant qu'on a pas les autres jointures et le plan d'exécution, on ne fera que tirer des plans sur la comète...

[orafrance]

Envoyé par nuke_y

Certes mais même avec 2 requêtes exécutées... Entre 2 * 1s et 3mn, il y a une différence.

t'ajoute un FTS et voila

Toujours est-il que n'étant pas voyant extra-lucide nous devons nous en tenir à des suppositions plus ou moins hasardeuses

[Oz-WereWolf]

Bon,

Tout d'abord merci de prendre le temps de me répondre.

Ensuite, d'après ce que j'en sais, les stats ne sont pas activées (en tout cas, elles ne le sont pas dans notre environnement de développement).

J'ai essayé d'installer SQL Developper mais comme on développe sur un Oracle 8.1.7, il y a un message d'erreur m'indiquant qu'il me faut la version 9 au minimum.

Ensuite, je ne suis que simple développeur. Simplement, les pansements sont une coutume courante dans ma boite et "du moment que pour l'instant ça marche, on ne cherche pas plus loin. On avisera dans 2 mois si ça explose à nouveau".
Donc, j'essaie d'apporter ma petite pierre à l'édifice pour améliorer la qualité du produit.

Pour info, il s'agit d'une boite de 150 personnes dont plus de 40 développeurs.

Personne ne maîtrise Oracle, ne sait quand mettre un index ou pas.

Pour vous donner une idée du niveau ambiant, voir la pièce jointe à ce message.

Il s'agit d'une analyse faite par deux des soit-disant "cadors" de la boite. Le but de la table est de stocker les évènements rattachés à une personne, pour tous les jours de l'année. Je vous laisse savourer ce brillant choix, très courageux et qui moi, m'a vraiment fait sauter en l'air quand je l'ai vu.

Donc, maintenant, pour revenir au sujet, que faire pour voir le plan d'exécution (je n'ai pas tous les outils Oracle sur ma machine, seulement SQL plus worksheet et DBA Studio.
Et ensuite, comment comprendre un minimum ce qui se passe et comment savoir comme l'optimiser ?

Merci à tous.

[pifor]

Essayez:
EXPLAIN PLAN
ou
trace SQL + TKPROF (nécessite accès serveur qui héberge la base)
ou
SET AUTOTRACE avec SQL*Plus (nécessite droits DBA si n'est pas encore configuré).

Voir http://oracle.developpez.com/guide/tuning/tkprof/