IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Langage SQL Discussion :

optimisatin d'une requete


Sujet :

Langage SQL

  1. #1
    Membre actif
    Inscrit en
    Juillet 2007
    Messages
    456
    Détails du profil
    Informations forums :
    Inscription : Juillet 2007
    Messages : 456
    Points : 231
    Points
    231
    Par défaut optimisatin d'une requete
    Salut,

    je voudrais optimiser la requete ci dessous car elle prend bq de temps
    J'ai trois table
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    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
    ('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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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

  2. #2
    Membre actif
    Inscrit en
    Juillet 2007
    Messages
    456
    Détails du profil
    Informations forums :
    Inscription : Juillet 2007
    Messages : 456
    Points : 231
    Points
    231
    Par défaut
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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

  3. #3
    Membre éprouvé
    Profil pro
    Inscrit en
    Août 2008
    Messages
    861
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2008
    Messages : 861
    Points : 965
    Points
    965
    Par défaut
    Bonjour,

    Vous pouvez ajouter une jointure sur la table orderst, pour ne conserver que les updatenum max par orderid :
    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 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

  4. #4
    Modérateur

    Avatar de CinePhil
    Homme Profil pro
    Ingénieur d'études en informatique
    Inscrit en
    Août 2006
    Messages
    16 793
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 60
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : Août 2006
    Messages : 16 793
    Points : 34 024
    Points
    34 024
    Billets dans le blog
    14
    Par défaut
    J'ai trois table
    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 !
    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`

    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  5. #5
    Membre éprouvé
    Profil pro
    Inscrit en
    Août 2008
    Messages
    861
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2008
    Messages : 861
    Points : 965
    Points
    965
    Par défaut
    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).

  6. #6
    Modérateur

    Avatar de CinePhil
    Homme Profil pro
    Ingénieur d'études en informatique
    Inscrit en
    Août 2006
    Messages
    16 793
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 60
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : Août 2006
    Messages : 16 793
    Points : 34 024
    Points
    34 024
    Billets dans le blog
    14
    Par défaut
    Peut-être en effet, j'avais compris sa phrase :
    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".
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  7. #7
    Membre actif
    Inscrit en
    Juillet 2007
    Messages
    456
    Détails du profil
    Informations forums :
    Inscription : Juillet 2007
    Messages : 456
    Points : 231
    Points
    231
    Par défaut
    Merci bq pour vos retour,

    pour répondre aux remarque de CinePhil

    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 .....

    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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

  8. #8
    Modérateur

    Avatar de CinePhil
    Homme Profil pro
    Ingénieur d'études en informatique
    Inscrit en
    Août 2006
    Messages
    16 793
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 60
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : Août 2006
    Messages : 16 793
    Points : 34 024
    Points
    34 024
    Billets dans le blog
    14
    Par défaut
    Citation Envoyé par Esil2008 Voir le message
    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.
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  9. #9
    Membre actif
    Inscrit en
    Juillet 2007
    Messages
    456
    Détails du profil
    Informations forums :
    Inscription : Juillet 2007
    Messages : 456
    Points : 231
    Points
    231
    Par défaut
    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

  10. #10
    Modérateur
    Avatar de Waldar
    Homme Profil pro
    Customer Success Manager @Vertica
    Inscrit en
    Septembre 2008
    Messages
    8 452
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 46
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Customer Success Manager @Vertica
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Septembre 2008
    Messages : 8 452
    Points : 17 814
    Points
    17 814
    Par défaut
    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. #11
    Membre éprouvé Avatar de Oishiiii
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Août 2009
    Messages
    508
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 36
    Localisation : France, Ain (Rhône Alpes)

    Informations professionnelles :
    Activité : Administrateur de base de données

    Informations forums :
    Inscription : Août 2009
    Messages : 508
    Points : 1 104
    Points
    1 104
    Par défaut
    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...

  12. #12
    Membre actif
    Inscrit en
    Juillet 2007
    Messages
    456
    Détails du profil
    Informations forums :
    Inscription : Juillet 2007
    Messages : 456
    Points : 231
    Points
    231
    Par défaut
    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)

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Quote dans une requete...
    Par Isildur dans le forum Langage SQL
    Réponses: 6
    Dernier message: 20/06/2006, 11h57
  2. reauete suite à une requete
    Par 74160 dans le forum Requêtes
    Réponses: 4
    Dernier message: 22/08/2003, 15h42
  3. [syntaxe] Gerer les variables dans une requete
    Par rastapopulos dans le forum MS SQL Server
    Réponses: 12
    Dernier message: 15/04/2003, 13h53
  4. [VB6] [Excel] Résultat d'une requete ds une feuille
    Par elifqaoui dans le forum VB 6 et antérieur
    Réponses: 12
    Dernier message: 07/01/2003, 18h52
  5. Créer une vue pour trier une requete UNION ?
    Par Etienne Bar dans le forum SQL
    Réponses: 3
    Dernier message: 03/01/2003, 21h22

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo