optimisatin d'une requete

Version imprimable

Salut,

je voudrais optimiser la requete ci dessous car elle prend bq de temps
J'ai trois table
Code:

1 2 3 ORDER(orderid varchar, instid varchar) ORDERST(orderid varchar, updatenum integer, price varchar) INST(instid varchar, place varchar)
Je voudrais récuperer pour chaque "place" la moyenne des "price" des orderids avec le plus grand updatenum

Par exemple

si j'ai dans la table order

Citation:

('ord1', 'inst1')
('ord2', 'inst2')
('ord3', 'inst1')
('ord4', 'inst2')

et dans INST on a
('inst1', 'x')
('inst2', 'y')

et dans ORDERST
('ord1', 1, 0)
('ord1', 2, 10)
('ord1', 3, 15)

('ord3', 1, 0)
('ord3', 2, 20)
('ord3', 3, 20)

donc on devra recuperer pour la place x un average price de AVG(20+15)

voila la requete que j'essaye de faire
Code:

1 2 3 4 5 6 7 select AVG(cast(os.PRICE as DOUBLE)) from ORDERST os INNER JOIN ORDER co ON os.ORDERID = o.ORDERID INNER JOIN INST inst ON o.INSTID = inst.INSTID WHERE inst.PLACE = 'X' AND os.updatenum IN (SELECT MAX(os2.updatenum) FROM ORDERST os2 where os.ORDERID = os2.ORDERID)
Comme vous pouvez le deviner, c'est le max qui me trouble et que j'arrive pas à bien placer

cette requete n'est pas terrible a cause du IN, elle prend bq de temps, comment pourrais je l'améliorer

Merci

je voudrais juste savoir comment optimiser cette requete, car elle m'a prit 2mn pour s'executer. Je sais que c'est a cause du IN, mais je ne vois pas comment je pourrais l'éviter
Code:

1 2 3 4 5 6 7 SELECT AVG(cast(os.PRICE AS DOUBLE)) FROM ORDERST os INNER JOIN ORDER co ON os.ORDERID = o.ORDERID INNER JOIN INST inst ON o.INSTID = inst.INSTID WHERE inst.PLACE = 'X' AND os.updatenum IN (SELECT MAX(os2.updatenum) FROM ORDERST os2 where os.ORDERID = os2.ORDERID)
ne tener pas compte du bla bla que j'ai dit en haut

Merci

Bonjour,

Vous pouvez ajouter une jointure sur la table orderst, pour ne conserver que les updatenum max par orderid :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
SELECT inst.place, AVG(cast(os.PRICE AS DOUBLE)) 
FROM ORDERST os 
INNER JOIN ORDER co 
    ON os.ORDERID = o.ORDERID 
INNER JOIN INST inst 
    ON o.INSTID = inst.INSTID  
INNER JOIN (SELECT orderid, max(updatenum) as updatenum
            FROM ORDERST
            GROUP BY orderid
           ) osmax
           ON os.updatenum = osmax.updatenum
           AND os.orderid = osmax.orderid		   
WHERE inst.PLACE = 'X'
GROUP BY inst.place

Citation:

J'ai trois table

Citation:

ORDER(orderid varchar, instid varchar)
ORDERST(orderid varchar, updatenum integer, price varchar)
INST(instid varchar, place varchar)

Des id et un prix en VARCHAR, ce n'est pas optimum ! :aie:
D'ailleurs, vous voyez que dans votre requête vous êtes obligé de "caster" le prix.
Du coup l'exécution de la requête prend déjà du temps.

D'après la structure de vos tables, le MCD semble être le suivant :
INST -0,n----ORDER----0,n- OERDERST

Au passage, appeler une table 'ORDER' est une mauvaise idée parce que c'est un mot du SQL. Si vous utilisez MySQL, je vous conseille de mettre le nom de cette table entre apostrophes inverses : `ORDER`

Citation:

Je voudrais récuperer pour chaque "place" la moyenne des "price" des orderids avec le plus grand updatenum

Il manque donc un GROUP BY dans votre requête !

Ce qui donne la requête suivante pour toutes les places :
Code:

1 2 3 4 5 6 7 SELECT i.PLACE, AVG(cast(os.PRICE AS DOUBLE)) AS PrixMoyen, MAX(os.updatenum) AS MaxUpdatenum FROM `ORDER` As o INNER JOIN ORDERST AS os ON o.ORDERID = os.ORDERID INNER JOIN INST AS i ON o.INSTID = i.INSTID GROUP BY i.PLACE

11/09/2009, 10h56
Snipah

CinePhil, si j'ai bien saisi le besoin, cette requête ne renverra pas le resultat voulu. Il ne faut conserver que le prix correspondant au max(updatenum) par orderid pour le calcul de la moyenne.
Dans l'exemple, la place ('inst1', 'x') correspond à ('ord1', 'inst1') et ('ord3', 'inst1'), soit ('ord1', 3, 15) et ('ord3', 3, 20).
11/09/2009, 11h01
CinePhil

Peut-être en effet, j'avais compris sa phrase :

Citation:

Je voudrais récuperer pour chaque "place" la moyenne des "price" des orderids avec le plus grand updatenum

dans un autre sens : "Je veux récupérer la moyenne et le max".

Merci bq pour vos retour,

pour répondre aux remarque de CinePhil

Citation:

Des id et un prix en VARCHAR, ce n'est pas optimum !

Je suis tout a fait daccord, sauf qu'il y a deux raisons pour cela :

la première c'est que j'ai pas la main sur la génération de cette base et que je me contente de récupérer ce qu'il y a dedans.

La deuxième chose, je pense que les gens qui ont fait cette base l'en pas fait juste par ce qu'il sont bêtes, ils ont fait comme ca pour pouvoir créer automatiquement leur BD sur n'importe quel SGBD, vous etes bien daccord que s'ils avaient mit un DOUBLE, ça serait reconnu sous HSQLBD mais pas sur MYSQL, même chose pour NUMBER je pense .....

Citation:

Au passage, appeler une table 'ORDER' est une mauvaise idée parce que c'est un mot du SQL. Si vous utilisez MySQL, je vous conseille de mettre le nom de cette table entre apostrophes inverses : `ORDER`

Encore daccord, mais c'est juste un nom que j'ai donné ici pour simplifier, c'est pas le nom qu'on utilise
Code:

1 2 3 4 5 6 7 8 9 Ce qui donne la requête suivante pour toutes les places : Code : SELECT i.PLACE, AVG(cast(os.PRICE AS DOUBLE)) AS PrixMoyen, MAX(os.updatenum) AS MaxUpdatenum FROM `ORDER` AS o INNER JOIN ORDERST AS os ON o.ORDERID = os.ORDERID INNER JOIN INST AS i ON o.INSTID = i.INSTID GROUP BY i.PLACE
La par contre je ne suis pas daccord par ce que ça donne pas le bon resultat, j'ai deja essayé ça tout à l'heure mais je me suis rendu compte que c'est faux.

La requete proposée par Snipah donne le bon resultat

Merci bq a vous deux, je vais essayer la requete d'Snipah avec pas mal de données pour voir les perf d'exec et je vous tiens au courant

11/09/2009, 11h26
CinePhil

Citation:

Envoyé par Esil2008

ils ont fait comme ca pour pouvoir créer automatiquement leur BD sur n'importe quel SGBD, vous etes bien daccord que s'ils avaient mit un DOUBLE, ça serait reconnu sous HSQLBD mais pas sur MYSQL, même chose pour NUMBER je pense .....

SQLPro nous donne les types de colonnes normalisés SQL.
Si on veut faire une requête passe-partout, il faut se rappocher le plus possible de cette syntaxe. Malheureusement, les SGBDR ne respectent pas toujours la norme et on est souvent obligé d'adapter les requêtes de création de tables au SGBD choisi.

Il n'empêche que, sur le plan conceptuel, vouloir enregistrer un prix qui est une quantité sur laquelle porteront des calculs dans un type alphanumérique est pour le moins douteux.
11/09/2009, 11h44
Esil2008

c'est justement le non respect de la norme SQL qui contraint les développeurs à contourner ça, parfois avec des choses pas très propres
11/09/2009, 12h05
Waldar

C'est un peu facile comme argument vous ne trouvez pas ?

La norme d'une part n'est pas figée et évolue, il faut donc pouvoir la suivre.
D'autre part elle est arrivée sur un marché installé, certains éditeurs (de SGBD) ont mis du temps à appliquer la norme (justement je suppose pour éviter des solutions trop facilement transportables), certains ont peut-être des mécanismes internes non compatible avec la norme, et bien entendu tout ceci n'est pas gratuit.

Je pourrai vous rétorquer que certains éditeurs (de logiciels) préfèrent économiser en coût développement en proposant de mauvaises solutions.

Bref tout le monde cherche à économiser et montre le voisin comme responsable de cet état de fait !
11/09/2009, 12h36
Oishiiii

Par exemple au niveau des dates.

Si vous stockez les dates dans des colonnes de type Varchar, à chaque manipulation relativement complexe il va falloir convertir et manipuler les dates avec des fonctions qui très souvent n'ont pas d'équivalent (n'on pas le même nom, ou pour un même nom n'a pas la même fonction) d'un SGBDR à l'autre.

A moins de reporter toutes les manipulations coté application... :roll:
11/09/2009, 14h33
Esil2008

pour la requete, elle est mnt largement mieux qu'avant, le temps d'exec est en qlq millisecondes mnt.

Sinon concernant les dates je pense que TimeStamp existe sur la plupart des SGBD (les plus utilisés)