optimiser une requete qui doit de toute façon lire toute la base

Le
WebShaker
salut.

on me demande de réaliser une requête qui consiste a compter le nombre
de produit vendu par référence.

il s'agit donc de
SELECT ref, count(*) FROM commandline GROUP BY ref;

mais j'ai 17.000.000 de lignes de de commandes dans ma base
Alors la question est simple.
que peut on faire pour optimiser ce genre de requête

surtout que le requête suivante est de compter le quantité par catégorie
de produit.
la ca donne

SELECT cat, count(*) FROM commandline INNER JOIN produit ON
commandline.idproduit = produit.idproduit GROUP BY cat;

Les suivantes sont encore pire
Dans touts les cas, il me faut parcourir la table commandline.
ou alors il faut que je fasse un mécanisme de cache, mais la je ne
connais pas trop les techniques (table temporaire peut être, je ne sais
pas).

Etienne
Vidéos High-Tech et Jeu Vidéo
Téléchargements
Vos réponses Page 1 / 5
Gagnez chaque mois un abonnement Premium avec GNT : Inscrivez-vous !
Trier par : date / pertinence
helios
Le #22148461
WebShaker a écrit :
salut.

on me demande de réaliser une requête qui consiste a compter le nombre
de produit vendu par référence.

il s'agit donc de
SELECT ref, count(*) FROM commandline GROUP BY ref;

mais j'ai 17.000.000 de lignes de de commandes dans ma base...
Alors la question est simple.
que peut on faire pour optimiser ce genre de requête...

surtout que le requête suivante est de compter le quantité par
catégorie de produit.
la ca donne

SELECT cat, count(*) FROM commandline INNER JOIN produit ON
commandline.idproduit = produit.idproduit GROUP BY cat;

Les suivantes sont encore pire...
Dans touts les cas, il me faut parcourir la table commandline.
ou alors il faut que je fasse un mécanisme de cache, mais la je ne
connais pas trop les techniques (table temporaire peut être, je ne
sais pas).

Etienne


prendre un sgbd type NOSQL qui n'as pas besoin de 'INNER JOIN'
Mickaël Wolff
Le #22149091
Le 21/05/2010 08:39, WebShaker a écrit :
mais j'ai 17.000.000 de lignes de de commandes dans ma base...
Alors la question est simple.
que peut on faire pour optimiser ce genre de requête...



Tu ne peux pas :o)

surtout que le requête suivante est de compter le quantité par catégorie
de produit.
la ca donne

SELECT cat, count(*) FROM commandline INNER JOIN produit ON
commandline.idproduit = produit.idproduit GROUP BY cat;

Les suivantes sont encore pire...
Dans touts les cas, il me faut parcourir la table commandline.
ou alors il faut que je fasse un mécanisme de cache, mais la je ne
connais pas trop les techniques (table temporaire peut être, je ne sais
pas).



Tu vas devoir utiliser un système qui contourne le problème.
Plusieurs choix s'offre à toi, et qui dépendent des technologies
disponibles sur ta plate-forme. Ou que tu peux mettre en place.
Si ta base de données supporte les trigger, tu peux les utiliser pour
maintenir une table de statistiques. C'est ce qui devrait être le plus
efficace, car le calcul de ces statistiques sera réparti sur les
insertion et mises à jour des tabls. Le danger est d'obtenir des
statistiques fausses (dues à un bug dans un trigger). Il faudra donc
prévoir un mécanisme de vérification.
Si ta base ne supporte pas les triggers, il faudra que le programme
client (ton script PHP, ton application, etc) maintienne une telle
table. C'est plus casse-gueule dans le sens où tu risque d'avoir des
race conditions.

Quand à la base noSQL proposée par helios, pourquoi pas, mais ça veut
dire qu'il faudra peut-être migrer. Et ça, je ne sais pas si ça dépend
de toi ;)
--
Mickaël Wolff aka Lupus Michaelis
http://lupusmic.org
Yliur
Le #22149361
Le Fri, 21 May 2010 11:37:21 +0100
Mickaël Wolff
Le 21/05/2010 08:39, WebShaker a écrit :
> mais j'ai 17.000.000 de lignes de de commandes dans ma base...
> Alors la question est simple.
> que peut on faire pour optimiser ce genre de requête...

Tu ne peux pas :o)

> surtout que le requête suivante est de compter le quantité par
> catégorie de produit.
> la ca donne
>
> SELECT cat, count(*) FROM commandline INNER JOIN produit ON
> commandline.idproduit = produit.idproduit GROUP BY cat;
>
> Les suivantes sont encore pire...
> Dans touts les cas, il me faut parcourir la table commandline.
> ou alors il faut que je fasse un mécanisme de cache, mais la je ne
> connais pas trop les techniques (table temporaire peut être, je ne
> sais pas).

Tu vas devoir utiliser un système qui contourne le problème.
Plusieurs choix s'offre à toi, et qui dépendent des technologies
disponibles sur ta plate-forme. Ou que tu peux mettre en place.
Si ta base de données supporte les trigger, tu peux les utiliser
pour maintenir une table de statistiques. C'est ce qui devrait être
le plus efficace, car le calcul de ces statistiques sera réparti sur
les insertion et mises à jour des tabls. Le danger est d'obtenir des
statistiques fausses (dues à un bug dans un trigger). Il faudra donc
prévoir un mécanisme de vérification.
Si ta base ne supporte pas les triggers, il faudra que le
programme client (ton script PHP, ton application, etc) maintienne
une telle table. C'est plus casse-gueule dans le sens où tu risque
d'avoir des race conditions.



Mauvaise idée en général de confier la cohérence des données à l'appli.
D'autant qu'il y a des chances pour qu'il puisse y avoir plusieurs
modifications simultanées et les problèmes que tu évoques. A éviter au
maximum...


Quand à la base noSQL proposée par helios, pourquoi pas, mais ça
veut dire qu'il faudra peut-être migrer. Et ça, je ne sais pas si ça
dépend de toi ;)
Yliur
Le #22149501
Le Fri, 21 May 2010 09:39:34 +0200
WebShaker
salut.

on me demande de réaliser une requête qui consiste a compter le
nombre de produit vendu par référence.

il s'agit donc de
SELECT ref, count(*) FROM commandline GROUP BY ref;

mais j'ai 17.000.000 de lignes de de commandes dans ma base...
Alors la question est simple.
que peut on faire pour optimiser ce genre de requête...

surtout que le requête suivante est de compter le quantité par
catégorie de produit.
la ca donne

SELECT cat, count(*) FROM commandline INNER JOIN produit ON
commandline.idproduit = produit.idproduit GROUP BY cat;

Les suivantes sont encore pire...
Dans touts les cas, il me faut parcourir la table commandline.
ou alors il faut que je fasse un mécanisme de cache, mais la je ne
connais pas trop les techniques (table temporaire peut être, je ne
sais pas).

Etienne



Es-tu obligé de conserver autant de donnés ? Je ne sais pas ce que fait
ton appli, mais je me souviens que dans les applis décisionnelles on
limite la durée pendant laquelle on conserve les données pour n'en
avoir pas trop à traiter. Bon, pas sûr que ça s'applique à ton cas. ..

Combien de temps prennent tes requêtes, les as-tu déjà lancées ? De
combien de temps disposes-tu ? Est-ce que les requêtes doivent être
effectuées en "temps réel" (au moment de l'affichage, donc durer peu de
temps) ou est-ce qu'elles sont effectuées la nuit et qu'elles peuvent
durer plusieurs minutes/heures ?

A priori, sur la première requête un index (sous forme d'arbre) sur ref
pourrait être utile pour trier les données par référence. Il faudra par
contre lire quand même toute la table, ça ne peut servir que pour le
tri.

Donc une question qui va se poser rapidement : de combien de mémoire
disposes-tu ? Es-ce que la table commandline a des chances de tenir
complètement en mémoire par exemple ? Dans ce cas il se pourrait qu'elle
y reste d'une requête à l'autre (gestion de cache par le sgbd), ce qui
rendrait les requêtes suivantes beaucoup plus rapide. C'est assez
facile à vérifier en lançant deux fois de suite la même requête o u deux
de tes requêtes, est-ce que la première génère beaucoup d'accès d isques
et la deuxième non ?

Une ou des tables temporaires ou en mémoire pourquoi pas, mais c'est
surtout utile si ça te permet de permet de faire des agrégats qui
resserviront plusieurs fois (donc ça dépend de tes requêtes) ou si ça
te permet de travailler sur une table réduite (avec moins de colonnes),
qui peut du coup tenir en mémoire.
WebShaker
Le #22152101
Le 21/05/2010 14:03, Yliur a écrit :
Es-tu obligé de conserver autant de donnés ? Je ne sais pas ce que fait
ton appli, mais je me souviens que dans les applis décisionnelles on
limite la durée pendant laquelle on conserve les données pour n'en
avoir pas trop à traiter. Bon, pas sûr que ça s'applique à ton cas...



Heu oui a priori il n'est pas prévu de supprimer des données.

Combien de temps prennent tes requêtes, les as-tu déjà lancées ? De
combien de temps disposes-tu ? Est-ce que les requêtes doivent être
effectuées en "temps réel" (au moment de l'affichage, donc durer peu de
temps) ou est-ce qu'elles sont effectuées la nuit et qu'elles peuvent
durer plusieurs minutes/heures ?



Alors en effet, mon problème est qu'il faut que je réalise un paquet de
requêtes.
pour l'entreprise en entier, par service, par commercial, ...
Je les pré-calcule effectivement la nuit, mais ca me prend déjà
quasiment toute la nuit.

Donc une question qui va se poser rapidement : de combien de mémoire
disposes-tu ? Es-ce que la table commandline a des chances de tenir
complètement en mémoire par exemple ? Dans ce cas il se pourrait qu'elle
y reste d'une requête à l'autre (gestion de cache par le sgbd), ce qui
rendrait les requêtes suivantes beaucoup plus rapide. C'est assez
facile à vérifier en lançant deux fois de suite la même requête ou deux
de tes requêtes, est-ce que la première génère beaucoup d'accès disques
et la deuxième non ?



Alors oui, j'ai effectivement un serveur avec 16 go de ram. et postgres
bouffe un max... Sans doute à cause des jointures multiples que je fais
dans tous les sens.
J'ai lancé 8 processus en parallele car j'ai 8 procs.
une nuit, le serveur s'est mis a swapper, et là tout est parti en
couille treeeeeeeees rapidement :)
D'où l'idée de repenser un peu tout cela.

Une ou des tables temporaires ou en mémoire pourquoi pas, mais c'est
surtout utile si ça te permet de permet de faire des agrégats qui
resserviront plusieurs fois (donc ça dépend de tes requêtes) ou si ça
te permet de travailler sur une table réduite (avec moins de colonnes),
qui peut du coup tenir en mémoire.



Hum...
Mon idée c'est surtout de dupliquer les données tous les soirs dans de
nouvelles tables regroupant plusieurs tables. (Voir si c'est possible un
jour de ne plus dupliquer intégralement mais mettre en jour en fct des
données modifiées.)

Par exemple fusionner les tables produits, commandline, commande, bl et
facture dans une seule table puisque pour chaque ligne de commande j'ai
évidement qu'une seule commande, une seule facture, un seul produit.
bon pour les bl c'est plus complexe à cause des reliquats et livraison
partielle.

je pense que le temps pris pour générer cette table sera conséquent mais
me permettra de faire des requêtes avec nettement moins de jointure.
Par contre quasiment toutes les requêtes liront alors l'intégralité de
la table puisque compter le nombre de factures va devenir

SELECT count(DISTINCT idfacture) FROM compact_table;

Alors qu'avant je faisais un

SELECT count(*) FROM facture;

La table était plus petite et donc la requête plus rapide.

L'interet supplementaire d'une table unique est qu'il devient extrêment
simple de réaliser un outil permettant à chaque utilisateur de créer ses
propres graphes.
par exemple supposons qu'un de mes patrons soit intéressé par un graph
de CA / region de livraison.

la requête devient alors simplement
SELECT sum(facture_montant), region FROM compact_table GROUP BY region;
(d'ailleurs en tapant cet exemple je m'apercoit qu'il ne marche pas car
je vais devoir faire une somme des montant de facture pour chaque
idfacture distinct et pas pour toutes les lignes.)

Le truc ultra complexe (même si possible) à faire sans code spécifique
si je conserve les tables séparées.

Bon le mieux va être de tester je pense :)
J'aurai la surprise !!!

Etienne
helios
Le #22152411
WebShaker a écrit :
Le 21/05/2010 14:03, Yliur a écrit :
Es-tu obligé de conserver autant de donnés ? Je ne sais pas ce que fait
ton appli, mais je me souviens que dans les applis décisionnelles on
limite la durée pendant laquelle on conserve les données pour n'en
avoir pas trop à traiter. Bon, pas sûr que ça s'applique à ton cas...



Heu oui a priori il n'est pas prévu de supprimer des données.




sur un SGBD NOSQL sans jointure FT geres avec la 42C toutes les données
depuis 1981 sans probleme


Combien de temps prennent tes requêtes, les as-tu déjà lancées ? De
combien de temps disposes-tu ? Est-ce que les requêtes doivent être
effectuées en "temps réel" (au moment de l'affichage, donc durer peu de
temps) ou est-ce qu'elles sont effectuées la nuit et qu'elles peuvent
durer plusieurs minutes/heures ?



Alors en effet, mon problème est qu'il faut que je réalise un paquet
de requêtes.
pour l'entreprise en entier, par service, par commercial, ...
Je les pré-calcule effectivement la nuit, mais ca me prend déjà
quasiment toute la nuit.



sur un SGBD NOSQL sans jointure FT fait enormement de requetes chaque
nuit sans probleme


Donc une question qui va se poser rapidement : de combien de mémoire
disposes-tu ? Es-ce que la table commandline a des chances de tenir
complètement en mémoire par exemple ? Dans ce cas il se pourrait qu'elle
y reste d'une requête à l'autre (gestion de cache par le sgbd), ce qui
rendrait les requêtes suivantes beaucoup plus rapide. C'est assez
facile à vérifier en lançant deux fois de suite la même requête ou deux
de tes requêtes, est-ce que la première génère beaucoup d'accès disques
et la deuxième non ?



Alors oui, j'ai effectivement un serveur avec 16 go de ram. et
postgres bouffe un max... Sans doute à cause des jointures multiples
que je fais dans tous les sens.
J'ai lancé 8 processus en parallele car j'ai 8 procs.
une nuit, le serveur s'est mis a swapper, et là tout est parti en
couille treeeeeeeees rapidement :)
D'où l'idée de repenser un peu tout cela.




sur un SGBD NOSQL sans jointure FT utilise un IBM 16pross mais tu doit
etre lancement en dessous des besoins de FT donc 8 pross est largement
suffisant

ton probleme est les JOINTURES utilise un SGBD NOSQL sans jointure

Une ou des tables temporaires ou en mémoire pourquoi pas, mais c'est
surtout utile si ça te permet de permet de faire des agrégats qui
resserviront plusieurs fois (donc ça dépend de tes requêtes) ou si ça
te permet de travailler sur une table réduite (avec moins de colonnes),
qui peut du coup tenir en mémoire.



Hum...
Mon idée c'est surtout de dupliquer les données tous les soirs dans de
nouvelles tables regroupant plusieurs tables. (Voir si c'est possible
un jour de ne plus dupliquer intégralement mais mettre en jour en fct
des données modifiées.)

Par exemple fusionner les tables produits, commandline, commande, bl
et facture dans une seule table puisque pour chaque ligne de commande
j'ai évidement qu'une seule commande, une seule facture, un seul produit.
bon pour les bl c'est plus complexe à cause des reliquats et livraison
partielle.

je pense que le temps pris pour générer cette table sera conséquent
mais me permettra de faire des requêtes avec nettement moins de jointure.
Par contre quasiment toutes les requêtes liront alors l'intégralité de
la table puisque compter le nombre de factures va devenir

SELECT count(DISTINCT idfacture) FROM compact_table;

Alors qu'avant je faisais un

SELECT count(*) FROM facture;

La table était plus petite et donc la requête plus rapide.

L'interet supplementaire d'une table unique est qu'il devient
extrêment simple de réaliser un outil permettant à chaque utilisateur
de créer ses propres graphes.
par exemple supposons qu'un de mes patrons soit intéressé par un graph
de CA / region de livraison.

la requête devient alors simplement
SELECT sum(facture_montant), region FROM compact_table GROUP BY region;
(d'ailleurs en tapant cet exemple je m'apercoit qu'il ne marche pas
car je vais devoir faire une somme des montant de facture pour chaque
idfacture distinct et pas pour toutes les lignes.)

Le truc ultra complexe (même si possible) à faire sans code spécifique
si je conserve les tables séparées.

Bon le mieux va être de tester je pense :)
J'aurai la surprise !!!

Etienne


cela est une premiere etapes vers un SGBD NOSQL sans jointure il
restes juste a pouvoir avoir une modelisation permetant d'optimisé cela
avec un SGBD permettant de coder la modelisation
Eric Demeester
Le #22158081
dans (in) fr.comp.applications.sgbd, WebShaker ecrivait (wrote) :

Bonjour Étienne,

mais j'ai 17.000.000 de lignes de de commandes dans ma base...
Alors la question est simple.
que peut on faire pour optimiser ce genre de requête...



C'est tellement évident que personne n'a évoqué ce point, mais as-tu
défini des index pertinents dans tes tables ?

Les suivantes sont encore pire...
Dans touts les cas, il me faut parcourir la table commandline.



Même remarque, en définissant de bons index, tu devrais gagner beaucoup
de temps dans le traitemen des requètes.

--
Eric
Alain Montfranc
Le #22158461
Dans son message précédent, Eric Demeester a écrit :
dans (in) fr.comp.applications.sgbd, WebShaker ecrivait (wrote) :

Bonjour Étienne,

mais j'ai 17.000.000 de lignes de de commandes dans ma base...
Alors la question est simple.
que peut on faire pour optimiser ce genre de requête...



C'est tellement évident que personne n'a évoqué ce point, mais as-tu
défini des index pertinents dans tes tables ?

Les suivantes sont encore pire...
Dans touts les cas, il me faut parcourir la table commandline.



Même remarque, en définissant de bons index, tu devrais gagner beaucoup
de temps dans le traitemen des requètes.



Index ou pas, il y aura un scan de toute la base
helios
Le #22159341
Alain Montfranc a écrit :
Dans son message précédent, Eric Demeester a écrit :
dans (in) fr.comp.applications.sgbd, WebShaker ecrivait (wrote) :

Bonjour Étienne,

mais j'ai 17.000.000 de lignes de de commandes dans ma base...
Alors la question est simple.
que peut on faire pour optimiser ce genre de requête...



C'est tellement évident que personne n'a évoqué ce point, mais as-tu
défini des index pertinents dans tes tables ?

Les suivantes sont encore pire...
Dans touts les cas, il me faut parcourir la table commandline.



Même remarque, en définissant de bons index, tu devrais gagner beaucoup
de temps dans le traitemen des requètes.



Index ou pas, il y aura un scan de toute la base




et avec les jointures cela multiplie le temps de scan
Alain Montfranc
Le #22160161
helios a écrit :
Alain Montfranc a écrit :
Dans son message précédent, Eric Demeester a écrit :
dans (in) fr.comp.applications.sgbd, WebShaker ecrivait (wrote) :

Bonjour Étienne,

mais j'ai 17.000.000 de lignes de de commandes dans ma base...
Alors la question est simple.
que peut on faire pour optimiser ce genre de requête...



C'est tellement évident que personne n'a évoqué ce point, mais as-tu
défini des index pertinents dans tes tables ?

Les suivantes sont encore pire...
Dans touts les cas, il me faut parcourir la table commandline.



Même remarque, en définissant de bons index, tu devrais gagner beaucoup
de temps dans le traitemen des requètes.



Index ou pas, il y aura un scan de toute la base




et avec les jointures cela multiplie le temps de scan



si jointure, alors index accellere - relire cours
Publicité
Poster une réponse
Anonyme