Renvoyer les n éléments de chaque catégorie

**triaguae** · 29/03/2010, 14h33

bonjour,

J'ai fait cette requete pour obtenir les 10 plus grands résultats de imp_MG pour chaque imp_usine
Je me suis inspiré de codes sql glanés sur ce forum mais il doit y avoir un pb car cette requete mouline (Exécute la requete dans la barre d'état) plusieurs minutes, je force l'arrêt car pour l'utilisateur c'est impensable d'attendre tout ce temps.

précision : j'ai une seule table tbl_import avec + de 200.000 lignes

Code :

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

1
2
3
4
SELECT T.imp_usine, T.imp_MG
FROM tbl_import AS T
WHERE (((T.imp_MG) In (SELECT top 10 U.imp_MG from tbl_import as U where U.imp_usine=T.imp_usine order by U.imp_MG desc)))
ORDER BY T.imp_usine, T.imp_MG;

merci d'avance de votre aide

**triaguae** · 29/03/2010, 21h22

je viens de tenter sur une petite table d'un vingtaine de valeurs et là ça fonctionne.
Le code doit être certainement optimisé mais je ne sais pas comment

**f-leb** · 30/03/2010, 09h54

bonjour,

est-ce que le champ imp_usine est indexé ?

**triaguae** · 30/03/2010, 10h49

oui il est indexé avec doublons, mais il fait parti d'une clé primaire composite (composée de 7champs)

**lbar012001** · 31/03/2010, 11h34

Pourquoi pas un truc du style (non testé)

Code :

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

1
2
3
Select USINE, MG, ROW_NUMBER() OVER (PARTITION BY USINE ORDER BY MG DESC) as RANG
FROM IMPORT
WHERE RANG <=10

A+
Laurent

**triaguae** · 31/03/2010, 12h09

j'ai testé, ça ne fonctionne pas
de plus, cela ressemble à de l'Oracle....marche pas un moteur de f1 dans une mobilette!!

je pense vraiment qu'il s'agit d'un problème soit d'optimisation soit de conception de ma table.

je sais plus...................

J1 · 01/04/2010, 12h17

Bonjour,
s'il n'existe pas déjà, un index sur le champ imp_MG pourrait également accélérer l'exécution de la requête.

**triaguae** · 01/04/2010, 13h15

mon champ imp_mg est déjà indexé
J'avoue que je ne comprends pas

**triaguae** · 01/04/2010, 13h55

j'ai refait mon code inspiré du post
http://www.developpez.net/forums/d89...ction-top-5-a/

Code :

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

1
2
3
4
SELECT imp_usine, imp_mg FROM tbl_import T
WHERE T.imp_mg IN (SELECT TOP 5 U.imp_mg FROM tbl_import U WHERE
                      U.imp_usine=T.imp_usine ORDER BY U.imp_mg DESC)
ORDER BY T.imp_usine, T.imp_mg DESC;

les différences ne sont pas fondamentales juste en accord avec le post cité et c'est toujours la même chose ça rame pendant des minutes et ça rame....

Je me pose la question, ainsi qu'à vous tous : Access est-il capable de traiter ce genre de code sql avec une table de 200000 lignes?

**random** · 01/04/2010, 17h59

Je me pose la question, ainsi qu'à vous tous : Access est-il capable de traiter ce genre de code sql avec une table de 200000 lignes?

ben oui si on lui demande de procéder dans un ordre intelligent

je regarde 2000 000 de lignes et pour chacune d'entres elles je vais calculer un top 5 de 2000 000 de lignes même moi dans mes meilleurs jours ca me pendrait du temps

par contre si on commencait par calculer le top 5
on aurait 5 lignes pour lesquelles on interrogerait 5 fois une table déjà indexée

ce serait peut être plus judicieux que de changer de logiciel

**f-leb** · 01/04/2010, 20h27

bon ben une autre proposition alors:

Code sql :

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

1
2
3
4
 
SELECT imp_usine, imp_mg FROM tbl_Import T
WHERE (SELECT COUNT(*) FROM tbl_Import U
                WHERE U.imp_usine=T.imp_usine AND U.imp_mg>T.imp_mg) < 5;

ça donne quoi ?

**triaguae** · 01/04/2010, 21h25

merci de vos réponses, étant en vacances

je verrais ça en rentrant dans une dizaine de jours
le code de f-leb me fait penser à une source mysql que j'ai également tenté dans une base mysql semblable à ma base access
j'ai d'ailleurs posté également un message sur le forum dédié car ça mouline aussi!!
http://www.developpez.net/forums/d90...nts-categorie/

**Arkham46** · 02/04/2010, 15h12

Bjr

Une solution à tester avec du VBA.

Le code SQL :

Code sql :

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

1
2
3
4
5
 
SELECT tbl_import.imp_usine, tbl_import.imp_MG, CheckMG("",0,True) AS FieldForInitialize
FROM tbl_import
WHERE (((CheckMG([imp_usine],[imp_MG]))=True))
ORDER BY tbl_import.imp_usine,tbl_import.imp_MG;

Avec la fonction VBA à mettre dans un module standard :

Code vba :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
 
Function CheckMG(pUsine As String, pMG As Long, Optional pInitialize As Boolean) As Boolean
Static gMGColl As Collection
Dim ldb As DAO.Database
Dim lrs As DAO.Recordset
Dim lError As Boolean
Dim lMG As Long
On Error GoTo Gestion_Erreurs
' Si demande d'initialisation => vide la collection
If pInitialize Then
    Set gMGColl = New Collection
    Exit Function
End If
' Recherche le 5ème MG de l'usine dans la collection
lMG = gMGColl(CStr(pUsine))
' Si erreur, recherche du 5ème MG dans la table et ajout de ce MG à la collection
If lError Then
    Set ldb = CurrentDb
    Set lrs = ldb.OpenRecordset("select top 5 imp_mg from tbl_import where imp_usine='" & pUsine & "' order by imp_mg desc")
    If Not lrs.EOF Then
        lrs.MoveLast
        lMG = lrs!imp_mg
        gMGColl.Add lMG, CStr(pUsine)
    End If
    Set ldb = Nothing
End If
' Si le MG donné en paramètre est supérieur au 5ème MG, retourne Vrai, sinon Faux
If pMG >= lMG Then CheckMG = True
Exit Function
Gestion_Erreurs:
' Si erreur 5 = MG non trouvé dans la collection
'  => flag avec lError et reprise à la ligne suivante
If Err.Number = 5 Then
    lError = True
    Resume Next
End If
End Function

Le champ FieldForInitialize ne sert à rien d'autre qu'à exécuter la fonction une première fois en demandant l'initialisation de la collection.
Dans le filtre, on demande les enregistrements dont le CheckMG renvoit Vrai.
Pour chaque enregistrement, la fonction est exécutée et la collection gMGColl est remplie au fur et à mesure.
Ainsi on ne recherche le 5ème plus grand MG qu'une seule fois par usine.
Ensuite on lit la valeur dans la collection.

Il faut mieux fermer la fenêtre VBA avant exécution de la requête, c'est plus rapide.

Le code donné est écrit pour un champ imp_usine de type string, à adapter donc si c'est du numérique.

Le petit inconvénient c'est que la collection n'est pas vidée à la fin de la requête, mais ça ne devrait pas trop occuper de mémoire et c'est réinitialisé à chaque appel de la requête ...

**random** · 06/04/2010, 11h59

au point où on se situe pourquoi ne pas pas isoler le 5 éme élément du top 5
un >= 5ème sera plus rapide qu'un in top 5

**triaguae** · 08/04/2010, 09h11

merci à Arkham46 pour le code vba
En fait ma requete je l'ai déjà construite en vba (pas le choix n'arrivant pas à le faire en sql), pas avec une fonction mais en parcourant les recordset en dao. je fais une première requete que je classe d'une certaine manière, puis j'insère les enregistrements que je veux dans une table temporaire, et finalement j'utilise cette table pour faire mon état.

mais je voudrais bien trouver la solution en sql (curiosité perso!!!)

random, je suis ouvert à toutes propositions, car je ne vois pas comment tu veux faire!!!

**f-leb** · 08/04/2010, 18h57

Envoyé par elnipal

mais je voudrais bien trouver la solution en sql (curiosité perso!!!)

et les codes proposés plus haut aux messages #11 et #13 ?? Ils donnent quoi ? ça mouline pas plus vite ? (juste par curiosité également)

**triaguae** · 19/04/2010, 14h30

Me revoilà avec cette maudite requête que je vais abandonner parce que ma curiosité ne mène nulle part excepté à 2 choses:
-ma base est mal conçue (c'est import de données avec des redondances

) )
-ce que j'ai fait la première fois avec DAO et des tables temporaires ne fonctionne pas si mal que ça (au moins j'ai un résultat

)

les solutions #11 et #13 fonctionne peut-être : pas d'erreur renvoyées mais pas de résultats non plus car je n'ai pas attendu plus de 5min avant de forcer l'arrêt de l'exécution!

**f-leb** · 19/04/2010, 15h28

salut,

Envoyé par elnipal

oui il est indexé avec doublons, mais il fait parti d'une clé primaire composite (composée de 7champs)

Donc il y a des chances que imp_usine et imp_MG ne soient pas indexés.
Tu as essayé la proposition de CinePhil dans le forum MySQL, c'est-à-dire rajouter un index sur imp_usine et un autre sur imp_MG.
Pour un import, la clé sur 7 colonnes n’est sans doute pas utile.
Bref, peut-être en revoyant les indexs…

Renvoyer les n éléments de chaque catégorie

Requêtes et SQL.

Discussions similaires

Partager

Partager