Discussion :

[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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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]

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]

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]

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

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 ?

[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]

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 ?