Forum Programmation.autre Optimisation d'une requête SQL ou choix d'un bon SGBD

Posté par Ontologia (site web personnel) le 23 septembre 2004 à 14:37.

Étiquettes : aucune

sept.

2004

Bonjour, à fin de statistiques je cherche à exécuter des requêtes pour statistiques de ventes.
Pour cela, plutôt que d'écrire un logiciel traitant les données à partir de requêtes simples, je me suis dit, après lecture du site sqlpro (merci à son auteur) qu'il était plus intelligent et plus rapide de se creuser la tête afin d'écrire LA requête qui me donnera les résultats escomptés. Ca m'a d'ailleurs permis d'apprendre à faire du SQL évolué qui se limitait jusque là à "select from where".

J'ai donc conçu cette requête.

J'ai deux possibilités : soit faire générer le résultat par access sur un fichier fixe, et là je doute (voir la requête) qu'Access soit suffisament solide pour cela, soit l'envoyer au serveur Pervasive SQL2000i qui gère ici une 15aine d'utilisateurs et une petite base de 2 Go sur un PIII 1 Ghz qui ne sert qu'a ça, donc pas franchement débordé( je le vois rarement monter à plus de 30% de cpu).
Lorsque j'utilise pervasive j'ai choisi de requêter sur une base copiée chaque nuit, ça évite les conflits.

Problème : cette requête est affreusement lente (au bout de 2h, je n'ai toujours rien), j'aimerai avoir un espoir d'avoir un résultat quitte à ce qu'elle mette une heure à calculer, ce n'est pas grave.

la requête est la suivante (explication plus loin):

SELECT "COMMANDE"."date_creat","FACTURE_ELEMENT"."REF_ELEMENT",

FACTURE_ELEMENT."desi_element","FACTURE_ELEMENT"."quantite",

"FACTURE_ELEMENT"."prix_u"*"FACTURE_ELEMENT"."quantite" AS "Prix
Total",
"CLIENT"."Raison_sociale",
"BON_DE_LIVRAISON"."date_emission"-"COMMANDE"."date_creat" AS "Délai"

FROM FACTURE_ELEMENT , BON_DE_LIVRAISON ,COMMANDE , CLIENT

WHERE FACTURE_ELEMENT.REF_ELEMENT In
(SELECT FACTURE_ELEMENT.REF_ELEMENT from FACTURE_ELEMENT where FACTURE_ELEMENT.FAC > 10000000 and
FACTURE_ELEMENT.bl not in
( select FACTURE_ELEMENT.bl from FACTURE_ELEMENT where FACTURE_ELEMENT.fac < 3000000)

)

and FACTURE_ELEMENT.affaire = BON_DE_LIVRAISON.affaire
and FACTURE_ELEMENT.bl = BON_DE_LIVRAISON.num
and FACTURE_ELEMENT.affaire = COMMANDE.Affaire
and COMMANDE.code_cl = CLIENT.Code_client
And (FACTURE_ELEMENT.REF_ELEMENT) Not Like 'Z%'
And (FACTURE_ELEMENT.REF_ELEMENT) Not Like '5E%'
And (FACTURE_ELEMENT.REF_ELEMENT) Not Like '5P%'
And (FACTURE_ELEMENT.REF_ELEMENT) Not Like '5W%'
And (FACTURE_ELEMENT.REF_ELEMENT) Not Like '5X%'
AND ("FACTURE_ELEMENT"."prix_u")>0.4
ORDER BY "FACTURE_ELEMENT"."REF_ELEMENT"

Nota : Facture_element "pèse" 51000 lignes, commande 22000, client 2300, Bon_de_livraison 25000
Hors les conditions habituelles de discrimination sur critère simple, sur lien avec d'autre bases, la seule difficulté est la suivante :

WHERE FACTURE_ELEMENT.REF_ELEMENT In
(SELECT FACTURE_ELEMENT.REF_ELEMENT from FACTURE_ELEMENT where FACTURE_ELEMENT.FAC > 10000000 and
FACTURE_ELEMENT.bl not in ( select FACTURE_ELEMENT.bl from FACTURE_ELEMENT where FACTURE_ELEMENT.fac < 3000000)
)

en gros je lui demande de me sélectionner uniquement les références
- postérieur au 1/1/2000 ( le > 10000000)
- et dont le BL n'est pas un BL d'un avoir

(pour la petite histoire, je dois éliminer les références qui ont été facturés mais renvoyées par le client et pour lesquels on a crée un avoir (numéro de facture < 3000000, le seul lien étant le bon de livraison)

Y a t il un moyen d'améliorer cette requête ?

Dois-je utiliser un autre SGBD, plus solide (sachant qu'il ne sera utilisé que par moi, sur une base de 2 Go, et quasiment qu'en lecture, et que mon souçi de performances se mesure en dizaines de minutes) ?

merci d'avance, Smile !

# EXPLAIN

Posté par billy le 23 septembre 2004 à 15:25. Évalué à 2.

En utilisant EXPLAIN ta requete, le serveur va décomposer la requete pas à pas et tu seras en mesure de voir ou c'est lent... il se peut qu'il manque des index ou qql chose comme ca
- [^] # Re: EXPLAIN
  
  Posté par Ontologia (site web personnel) le 23 septembre 2004 à 15:29. Évalué à 1.
  
  merci :)
  syntaxiquement je le met où le explain ?
  « Il n’y a pas de choix démocratiques contre les Traités européens » - Jean-Claude Junker
  - [^] # Re: EXPLAIN
    
    Posté par B r u n o (site web personnel) le 23 septembre 2004 à 16:04. Évalué à 1.
    Pour postgresql, le EXPLAIN se met avant le SELECT (mais je pense que c'est une commande SQL standard, mais à vérifier). Dans ton cas, ca donne : EXPLAIN SELECT "COMMANDE"."date_creat","FACTURE_ELEMENT"."REF_ELEMENT", FACTURE_ELEMENT."desi_element","FACTURE_ELEMENT"."quantite" (cut...) et comme dit plus bas, des vues pourraient peut être aider. Et des index aussi, ca permet des gains importants, surtout si la base n'est qu'en consultation car le cout de l'index sur les insertions d'elements t'importe moins du coup
# Une vue

Posté par Calim' Héros (site web personnel) le 23 septembre 2004 à 15:35. Évalué à 3.

Pourquoi ne pas utiliser une vue qui reponde a cette requette

(SELECT FACTURE_ELEMENT.REF_ELEMENT from FACTURE_ELEMENT where FACTURE_ELEMENT.FAC > 10000000 and

FACTURE_ELEMENT.bl not in ( select FACTURE_ELEMENT.bl from FACTURE_ELEMENT where FACTURE_ELEMENT.fac < 3000000)

)

et utiliser cette vue dans ta requette general?

N'y a il pas de probleme entre le FACTURE_ELEMENT de ta sous requette et celle de la requette global? Peut etre que ce qui suit pourait faciliter la chose :

SELECT toto.REF_ELEMENT from FACTURE_ELEMENT as toto where toto.FAC > 10000000 and

toto.bl not in ( select tata.bl from FACTURE_ELEMENT as tata where tata.fac < 3000000)

Enfin voila, c'est que des supposition mais peut etre que...
- [^] # Re: Une vue
  
  Posté par gaaaaaAab le 23 septembre 2004 à 16:08. Évalué à 1.
  
  attention! une vue est définie par un select, et ce select est effectué à chaque fois qu'on fait appel à la vue, en tout cas sous Oracle (et probablement pour les autres SGBDs). Du coup, la création de vue ne permet pas d'améliorer les performances.
  - [^] # Re: Une vue
    
    Posté par Calim' Héros (site web personnel) le 23 septembre 2004 à 16:26. Évalué à 2.
    
    En fait c'est au cas ou le SGBD aurait des pb avec les requettes imbriqués.
    Ca permet aussi de rendre les choses un peut plus lisible.
- [^] # Re: Une vue
  
  Posté par BAud (site web personnel) le 23 septembre 2004 à 16:12. Évalué à 4.
  
  bon je suis pas DBA, mais la vue n'améliorerait rien : de mes souvenirs d'Oracle la vue c'est "simplement" une facilité d'écriture, mais en terme d'exécurtion de requête ça fait comme si tu avais encore le select.
  
  A 1ère vue, avec 51000 lignes dans FACTURE_ELEMENT, ta requête va pour 51000 élément faire un FULL (parcours complet de la table, à cause du not in) dans une table qui fait 51000 lignes (en supposant que tu n'as pas d'index...).
  
  Donc déjà mettre des index sur tes critères de recherche (quoique...)
  
  Pour Access je ne sais pas ce que valent les optimisations...
  
  J'aurais plutôt écrit la requête "à l'endroit" (le not empêche généralement d'utiliser l'index...) :
  
  (SELECT FACTURE_ELEMENT.REF_ELEMENT from FACTURE_ELEMENT where FACTURE_ELEMENT.FAC > 10000000 and
  FACTURE_ELEMENT.bl in
  ( select FACTURE_ELEMENT.bl from FACTURE_ELEMENT where FACTURE_ELEMENT.fac >= 3000000)
  )
  
  voire enlever cette deuxième partie : tu ne veux que les factures (après 2000 ET dont le BL a un avoir) d'où
  (SELECT a.REF_ELEMENT from FACTURE_ELEMENT a, FACTURE_ELEMENT b where a.FAC > 10000000 and
  a.bl=b.bl and
  b.fac >= 3000000)
  )
  
  bon ça risque de pas marcher... ou de pas être équivalent. Bon courage pour te trouver un vrai DBA ;-)
# autojointure ?

Posté par gaaaaaAab le 23 septembre 2004 à 16:06. Évalué à 2.

en rajoutant une auto jointure sur facture_element ?

select (...)
from facture_element f, facture_element f1
(...)
where
(...)
and f.fac > 10000000
and f.bl = f1.bl
and f1.fac < 3000000
- [^] # Re: autojointure ?
  
  Posté par benjamin fremaux le 23 septembre 2004 à 17:35. Évalué à 2.
  
  L'auto-jointure sous Oracle a tendance à améliorer le plan d'exécution. Par-contre, les tamps de réponse ne sont pas au rendez vous.
  - [^] # Re: autojointure ?
    
    Posté par gaaaaaAab le 23 septembre 2004 à 18:05. Évalué à 2.
    
    heu ... là je sais pas trop.
    Cest vrai que j'ai pas de chiffres pour étayer ça, mais j'ai quand même l'impression qu'entre deux jointures et un IN et un NOT IN, il vaut mieux choisir les jointures.
    
    sinon, les temps de réponse seront meilleurs si on a des indexs sur les champs bl et fac de facture_element.
    - [^] # Re: autojointure ?
      
      Posté par BAud (site web personnel) le 23 septembre 2004 à 18:32. Évalué à 2.
      
      jointure et select where in (select ) en fait c'est équivalent, dommage j'ai plus d'oracle sous la main pour montrer un plan d'exécution.
      
      en revanche un not in a *très peu* de chance (aucune de mémoire) de passer par l'index
      - [^] # Re: autojointure ?
        
        Posté par gaaaaaAab le 23 septembre 2004 à 19:11. Évalué à 2.
        
        j'en ai un sous la main. C'est pas tout à fait tout à fait pareil pour la jointure et le IN. Dans le IN, Oracle rajoute un SORT.
# Avec access, on peut pas faire gd chose

Posté par Ontologia (site web personnel) le 27 septembre 2004 à 15:33. Évalué à 1.

Finalement j'ai fait plusieurs requêtes où j'ai discriminé mon ensemble d'avoirs faux, avec la même requete ensuite mais tappant sur une table préalablement générée.

ca m'étonne qu'un SGBDR ne "pense pas " à générer ses ensembles en durs d'abord pour travailler sur l'affinage ensuite.

Falloir écrir un générateur...
« Il n’y a pas de choix démocratiques contre les Traités européens » - Jean-Claude Junker

Suivre le flux des commentaires

Note : les commentaires appartiennent à celles et ceux qui les ont postés. Nous n’en sommes pas responsables.