Discussion :

[kened4]

Bonjour,

Je travaille sous DB2 avec des gros volumes (dizaines de millions de lignes par tables).

Dans ces tables, il y a un système de versionning basé sur des dates de validité. On a :

DT_DEB_VAL = date de début de validité qui indique a partir de quand la ligne est valide
DT_FIN_VAL = date de fin de validité qui indique quand expire la ligne.

Une ligne valide dans le systeme est une ligne qui a une date de fin de validité a 9999-12-31.

Prenons un exemple de table de lien personne adresse:

On a donc un identifiant personne, un identifiant adresse et les dates de validité:

ID_PRS ID_ADR DT_DEB_VAL DT_FIN_VAL
480 308120801 2001-11-15 2009-09-06
480 301231113 2007-08-30 2009-09-06
6 96123263 2009-12-09 9999-12-31
6 221236811 2003-10-30 2009-12-08

Dans l'exemple ci dessus j'ai déjà filtré pour avoir les lignes qui ont été valides en 2009. Le problème dans cet exemple est que j'ai plusieurs lignes par personne.

Hors je souhaite récupérer pour chaque personne, la dernière occurrence valide en 2009.

Il faut donc que je sélectionne parmis ces quatre lignes , les plus récentes des quatre.

Ma question , comment faire pour récupérer ces lignes récentes. Faut il forcement passer par une sous requete pour chaque client? Sachant la volumétrie des tables, cela me semble tres couteux en performance. y a t il un autre moyen?

Merci d'avance

[Snipah]

Bonjour,

Je ne crois pas que DB2 accepte les fonctions de fenêtrage, donc la sous-requête semble être la meilleure solution.

Voyez déjà ce que donne ceci en termes de performances, avec les bons index ce ne sera peut être pas si mauvais :

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 nt.id_prs,
       nt.id_adr,
       nt.dt_deb_val,
       nt.dt_fin_val
FROM nom_table nt
JOIN 
(
 SELECT id_prs, max(dt_deb_val) AS dt_max
 FROM nom_table
 WHERE <conditions sur la date>
 GROUP BY id_prs
) ntmax
ON nt.id_prs = ntmax.id_prs
AND nt.dt_deb_val = ntmax.dt_max

[kened4]

Bonjour,

Merci de ta réponse. Bonne idée d'utiliser le JOIN. En effet, je n'ai jamais pris l'habitude d'utiliser ces jointures alors qu'elle ont l'air bien plus performante.

De base , j'aurais mis la sous requete dans une condition where surement.

Sinon, il n'y pas d'index sur cette base ou très peu.

[CinePhil]

Une ligne valide dans le systeme est une ligne qui a une date de fin de validité a 9999-12-31.

C'est pas top comme choix, la date devrait être à NULL.

Prenons un exemple de table de lien personne adresse:

On a donc un identifiant personne, un identifiant adresse et les dates de validité:

ID_PRS ID_ADR DT_DEB_VAL DT_FIN_VAL
480 308120801 2001-11-15 2009-09-06
480 301231113 2007-08-30 2009-09-06
6 96123263 2009-12-09 9999-12-31
6 221236811 2003-10-30 2009-12-08

Dans l'exemple ci dessus j'ai déjà filtré pour avoir les lignes qui ont été valides en 2009. Le problème dans cet exemple est que j'ai plusieurs lignes par personne.

Hors je souhaite récupérer pour chaque personne, la dernière occurrence valide en 2009.

Il faut donc que je sélectionne parmis ces quatre lignes , les plus récentes des quatre.

Ma question , comment faire pour récupérer ces lignes récentes. Faut il forcement passer par une sous requete pour chaque client? Sachant la volumétrie des tables, cela me semble tres couteux en performance. y a t il un autre moyen?

Je suppose que ID_PRS et ID_ADR sont indexées ?
Même avec des millions de lignes, si le serveur est correctement dimensionné, ça ne devrait pas poser de problème à DB2.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT pa.ID_PRS, pa.ID_ADR, pa.DT_DEB_VAL
FROM personne_adresse AS pa
INNER JOIN (
  SELECT ID_PRS, MAX(DT_DEB_VAL) AS date_debut_max
  FROM personne_adresse
  WHERE DT_FIN_VAL <> '9999-12-31'
    AND EXTRACT(YEAR FROM DT_DEB_VAL) >= 2009
  GROUP BY ID_PRS
) AS tmp 
  ON tmp.ID_PRS = pa.ID_PRS
    AND pa.DT_DEB_VAL = tmp.date_debut_max

Normalement, DB2 devrait commencer par éliminer toutes les lignes qui ne satisfont pas la condition de la sous-requête avant de regrouper les données pour en chercher le MAX(DT_DEB_VAL), ce qui va considérablement diminuer le nombre de lignes pour la jointure.

Si c'est encore trop lent, faites une table temporaire avec la sous-requête et indexez-là avant de faire la jointure.

[Snipah]

Donc, vous travaillez sur des gros volumes sans index.
J'imagine qu'il faut pas être pressé quand on lance une requête
Vous n'aurez jamais de bonnes performances sans index.

Donnez nous la structure de cette table (types des colonnes, clé primaire, etc), les éventuels index, et une idée du temps que met cette requête à s'exécuter.

[kened4]

Je n'ai pas vraiment la connaissance technique niveau DBA. Ce que je sais c'est qu'il y a tres peu d'index.

Il y en a sur les plus grosses tables mais a mon avis pas sur cette table de lien personne adresse.

Ensuite, les clés primaires ne sont pas non plus implementés en base.

Dans le systeme, les données sont charges dans des tables SA (staging area) temporaires avant le chargement en production. Les clés sont implémentés à ce niveau ce qui garantie l'unicité de la clé sur un chargement donné.

Pour le reste, il y a ensuite des contrôles d'intégrités avec un mécanisme de rejets et de recyclage.

Activer les clés primaires feraient surement tous sauter sur quasiment toutes les tables

Je préfère ne pas trop donner d'information sur la structure des tables et de la base (sécurité , tout ca...)

En tout cas , merci pour l'info je vais effectivement utiliser des sous requetes. Je cherchais juste a savoir si on pouvait feinter autrement.

[punkoff]

Bonjour,

DB2 accepte les fonctions de fenêtrage (du moins en V8+ et v5r4+ selon l'os).

Du coup des requête de ce style sont envisageable aussi :

Code :

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

1
2
3
4
5
6
7
 
WITH tmp AS 
(SELECT id_prs, id_adr, dt_deb_val, dt_fin_val, 
ROW_NUMBER() OVER (PARTITION BY id_prs ORDER BY dt_deb_val desc) AS rank 
FROM test
WHERE .....)
select * from tmp where rank =1

[kened4]

Bonjour, nous sommes en DB2V8 je crois. Je connais pas les fonctions de fenetrage je vais regarder.

[kened4]

Super ton code marche parfaitement.

Sinon est on oblige de faire une table temporaire et un select dessus. On ne peut pas directement exploiter le rank en clause where ou en group by?

J'ai essayer mais ca na pas fonctionner.

pouvoir faire rank = 1 dans la clause aurait été le top.

[Snipah]

Non, on ne peut pas l'utiliser directement dans la clause where, si vous ne voulez pas utiliser la CTE il faut simplement passer par une sous-requête.

Code :

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

1
2
3
4
5
6
7
8
9
10
 
SELECT * 
FROM
(
 SELECT id_prs, id_adr, dt_deb_val, dt_fin_val, 
        ROW_NUMBER() OVER (PARTITION BY id_prs ORDER BY dt_deb_val DESC) AS rank 
 FROM test
 WHERE .....
)
WHERE rank = 1;

Par contre, vous devriez vraiment prendre le temps d'indexer correctement vos tables, ça n'a pas l'air d'être le cas et c'est essentiel pour les perfs.

[punkoff]

Super ton code marche parfaitement.

Sinon est on oblige de faire une table temporaire et un select dessus. On ne peut pas directement exploiter le rank en clause where ou en group by?

J'ai essayer mais ca na pas fonctionner.

pouvoir faire rank = 1 dans la clause aurait été le top.

De toute façon en utilisant ce genre de requête le sgbd va automatiquement utiliser des structures temporaires (liste trié en l'occurrence) car il doit faire un classement d'une sélection triée.

Le seul moyen d'améliorer les perf est d'utiliser des indexs.

[kened4]

Oui mais je ne suis pas DBA , je ne suis qu'utilisateur du systeme.

ET s'il n'y pas d'index c'est pour une question de performance car il me semble que cela prends trop de temps d'indexer les tables. Pou charger une journée de 24 heures, la chaine deprod tourne peut etre 18 ou 20 h deja.

Enfin c'est ce que j'ai compris, mais si les gens de la prod pouvaient mettre des index partout je suppose qu'ils l'auraient fait.