Discussion :

[ch0upette]

Bonjour à tous !
J'ai un énorme souci (et vu que je débute avec l'administration de Base de données, je rame pas mal):
Je dois créer une application qui fournit des statistiques (CA, Marge, poids ...) sur des commandes passées par les clients.
Voici un exemple de requête :

Code :

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

1
2
3
4
5
6
7
 
"SELECT Sum(PDSQTELIV*L.PXNET), Sum(PDSQTELIV*(L.PXNET-PXRVT)), Count(*)
FROM LICOM L INNER JOIN COMMANDE C ON C.NOCDE=L.NOCDE
AND L.STAT=1 AND C.STAT=1
AND DATELIV BETWEEN '2006-01-01' AND '2006-12-31'
INNER JOIN CLIENT CL ON CL.NOCLI=C.NOCLI
AND CL.NOREP BETWEEN 50 AND 98;"

Le souci, temps d'exécution = 8 minutes
Est ce normal, ou ai-je mal choisi l'écriture de la requête?
Il faut savoir que le count(*) renvoie 198000 enregistrments, et que sans le count, le temps d'exécution est aussi long.
Des index sont tous à colonnes unique:
- sur les nocde des tables COMMANDE et LICOM
- sur les nocli des tables COMMANDE ET CLIENT
- sur norep de CLIENT
- sur dateliv de COMMANDE

Merci de m'éclairer avec vos lanternes
Céline

[ch0upette]

Y a vraiment personne qui pourrait m'aiguiller?

[WolffN]

essaye de ne pas faire count(*),mais count(champ index)

[ch0upette]

J'ai rajouté le count pour savoir combien de lignes étaient traité; mais j'ai testé au départ sans le count et le temps d'exécution est de même grandeur.
En fait, j'ai l'impression que c'est les jointure qui sont très lourdes.
Mais surtout j'aimerais savoir si c'est normal que pour ce type de requête, on est un temps de réponse de l'ordre de la dizaine de minutes?
Car on me demande d'obtenir une réponse en 30 sec.
Est-ce possible?

Il faut savoir que dans LICOM, il y a environ 2 500 000 enregistrements, dans CLIENT, 10 000, et 700 000 dans COMMANDE.

[rv26t]

Bonjour,
Personnellement je n'utilise pas les "inner join"
Essaye d'abord plutôt dans ce sens
SELECT Sum(PDSQTELIV*L.PXNET), Sum(PDSQTELIV*(L.PXNET-PXRVT)) FROM LICOM L, COMMANDE C , CLIENT CL
where C.NOCDE=L.NOCDE AND DATELIV BETWEEN '2006-01-01' AND '2006-12-31' and L.STAT=1 AND C.STAT=1
and CL.NOCLI=C.NOCLI and CL.NOREP BETWEEN 50 AND 98;

Ensuite essaye les requêtes imbriquées,
select ... in (select .... in (select ...) )

puis avec exists
Exists est très performant mais un peu délicat a mettre en oeuvre.
select ... from Licom where exists (select ... where exists ( select ... ) )

Si le temps reste encore trop important tu peux aussi utiliser des tables temporaires. (j'ai utilisé sous informix, à voir avec interbase)
Effectue les sum et count avec une dernière requête sur ta dernière table temporaire qui contiendra moins de données.

1ère table de résultat : (client) avec
... where CL.NOREP BETWEEN 50 AND 98

2ème table : (Table1(resultat client) et commande) avec
... where CL.NOCLI=C.NOCLI
AND C.STAT=1
AND DATELIV BETWEEN '2006-01-01' AND '2006-12-31' j'ai présumé que datedeliv est dans commande

3ème table : (Table2(resultat client et commande) et ligne de commande) avec
... where C.NOCDE=L.NOCDE

puis sum sur 3ème table
Je pense que les sum sont pour beaucoup dans le ralentissement.

(tu peux peut être faire le select sur client et commande en seul coup pour éviter une table temopraire, à tester. Voire select sur client commande et ligne vers table temporaire, puis le sum sur la table temporaire)

Contrairement a ce que l'on pourrai penser l'utilisation de table temporaire réduit les temps.
Procéde par étape.

a+, Hervé.

[rv26t]

Pour la 3ème table temporaire j'ai oublié le
and L.STAT=1
mais je pense que tu auras corrigé de toi même

A+, Hervé

[rv26t]

J'ai essayé d'écrire ta requête avec exists :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT Sum(PDSQTELIV*PXNET), Sum(PDSQTELIV*(PXNET-PXRVT))
FROM LICOM 
where Licom.STAT=1 
and exists (select commande.nocde 
            from commande
            where Commande.NOCDE=Licom.NOCDE
            AND DATELIV BETWEEN '2006-01-01' AND '2006-12-31'
            AND Commande.STAT=1
            and exists (select client.nocli
                        from client
                        where clien.nocli=commande.nocli
                        and CLient.NOREP BETWEEN 50 AND 98
                       )
           )

Je ne garantie rien mais tu as un exemple qui peut te servir.
Compare les résultats (les chiffres en résultat et le temps)

Informe nous de l'évolution. A+, Hervé.

[ch0upette]

Merci beaucoup du coup de pouce
Malheureusement, après test sur les différentes écritures proposées, pas vraiment d'amélioration.
Cependant, je n'ai pas testé les tables intermédiaires; car, entre temps, j'ai opté pour une autre solution (un peu cavalière, je dois bien l'avouer) :
j'ai créé une table persistante LIGNES qui contient toutes les données de la table LICOM avec toutes les données utilisées pour les jointures.
Cette table est alimentée par des triggers (environ une dizaine car prise en compte des insert, delete, et update).
Autant dire que j'ai énormément d'informations redondantes, mais j'obtiens un temps d'exécution convenable, de l'ordre de la minute (un peu moins).

Donc, je peux considérer mon problème comme résolu, même si la solution n'est pas la meilleure.

En tout cas, merci bien de ton intérêt.
A+ et bonne journée
Céline

[ludo00002]

Il faut éviter de mettre les conditions "AND L.STAT=1 AND C.STAT=1
AND DATELIV BETWEEN '2006-01-01' AND '2006-12-31'" et "
AND CL.NOREP BETWEEN 50 AND 98" dans le inner join

écris plutôt :

"SELECT Sum(PDSQTELIV*L.PXNET), Sum(PDSQTELIV*(L.PXNET-PXRVT)), Count(*)
FROM LICOM L INNER JOIN COMMANDE C ON C.NOCDE=L.NOCDE
INNER JOIN CLIENT CL ON CL.NOCLI=C.NOCLI
where L.STAT=1 AND C.STAT=1
AND DATELIV BETWEEN '2006-01-01' AND '2006-12-31' AND CL.NOREP BETWEEN 50 AND 98;"

[edam]

je me pose une question; pourqoi ne pas céparé la tache
en demande au serveur les donnée et localement on fait les calculs, comme sa on fait comme si on a 2 processeurs,non?
surtout avec les IBx les données ne sont ressus que à la demande pas tous.