Discussion :

[FMJ]

Salut,

Pour pouvoir me servir de la mise en forme conditionnel d'un contrôle dans un formulaire, je souhaiterais modifier la requête sous-jacente pour y ajouter un champ correspondant à un compteur de l'ordre de classement de chaque enregistrement.

Je m'explique. En autre le requête fait un regroupement sur toutes les personnes référencées dans une table de planning (T_PREVISIONNEL) qui est d'une taille assez respectable (de l'ordre de 100 000 enregistrements). Je souhaite que la requête trie ces personnes par leur nom et indique également le niveau de rang de chaque personne par rapport à la liste triée.

Exemple : Soit 5 personnes dans le planning : B, E, A, D, C. Une fois trié, cela donnerait :
1, A
2, B
3, C
4, D
5, E

J'ai réalisé une fonction placée dans un nouveau champ de classement de la requête sous-jacente (le #03/01/07# c'est juste pour tester) :

Code :

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

1
2
3
4
5
6
7
8
Function v_classement(v_num_enfant)
ChSQL = "SELECT num_enfant, (nom_enfant & prenom_enfant) AS tri FROM T_PREVISIONNEL LEFT JOIN T_ENFANT ON T_PREVISIONNEL.num_enfant = T_ENFANT.cle GROUP BY periode, [date], num_enfant, (nom_enfant & prenom_enfant) HAVING cdbl([date])=#03/01/07# ORDER BY (nom_enfant & prenom_enfant) ASC"
Set rst = CurrentDb.OpenRecordset(ChSQL)
    If Not rst.EOF Then
      rst.FindFirst "num_enfant = " & v_num_enfant
      If Not rst.NoMatch Then v_classement = rst.AbsolutePosition
End If
End Function

Problème : Comme la table T_PREVISIONNEL est assez volumineuse, la requête sous-jascente s'exécute désormais très lentement (et en plus elle s'exécute 4 fois pour chaque enregistrement mais ça s'est normal, c'est dû au niveau de regroupement de la requête sous-jacente).

Auriez-vous des solutions plus simples pour créer ce champ de classement (attention, il doit respecter le tri final de la requête) ? Je suis persuadé que l'on doit pouvoir arriver à quelque chose directement en SQL sans passer par un recordset VBA toujours beaucoup plus lent !

Merci d'avance.

[Tofalu]

http://access.developpez.com/sources...ueteClassement

[FMJ]

Salut Tofalu,

Encore une fois tu m'ouvres des perspectives gigantesques. C'est très puissant ce truc. Je connaissais les SELECT imbriqués mais pas cette façon si particulièrement de référencer les tables et de jouer avec.

Je sais pas encore comment je vais l'implémenter et l'adapter mais en tout cas il est certain que cela va être plus performant qu'en VBA.

Encore un grand merci !

[FMJ]

OK, j'y suis arrivé uniquement en SQL. Au départ, j'ai directement rajouté le SELECT imbriqué dans un champ de la requête principale mais comme son exécution faisait planter Access, je l'ai décorélé et mis dans une autre requête.

Par contre, c'est également assez long puisque le SELECT imbriqué s'exécute pour chaque enregistrement et doit metre 0.5s à chaque fois. Comme il y a près de 100 enregistrements .....

Question : est-ce qu'un des évènements d'un formulaire peut s'exécuter avant sa requête sous-jacente ? Dans ce cas, je pourrais passer par une table intermédiaire pour éviter ce temps de traitement trop long. Je viens d'essayer en mettant des msgbox sur la plupart des évènements et aucun ne s'exécute avant la requête !!!

[random]

généralement ces techniques sont coûteuses en temps
interrogation de l'ordre de n*n enreistements sur une table contenant n enreistrements
ainsi la requête suivante

Code :

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

1
2
3
4
 
SELECT champ1, champ2 , (select count (champ2) as nb from matable where champ2 < monalias.champ2)+1  AS ordre
FROM matable AS monalias
ORDER BY  champ2;

met environ 50 secondes pour 10000 enregistrements non ordonnées non indexés.
reste le problème des ex aequos qui vont recevoir le même numéro
si j'ajoute la fonction suivante

Code :

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

1
2
3
4
5
6
 
Function scompt(x As Variant) As Long
Static compteur  As Long
compteur = compteur + 1
scompt = compteur
End Function

je peux alors faire

Code :

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

1
2
3
4
5
 
select champ1,champ2, scompt(champ2) from
(SELECT champ1, champ2
FROM matable
ORDER BY champ2);

je divise mon temps de traitement pas n et j'obtiens une valeur scalaire indépendante des ex aequos
nb on peut même donner à compteur un ordre convenu en faisant

Code :

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

1
2
3
4
5
6
7
8
9
10
 
Function scompt(x As Variant, raz As Boolean) As Long
Static compteur  As Long
If raz Then
compteur = x
Else
compteur = compteur + 1
End If
scompt = compteur
End Function

ainsi dans un traitement un premier appel à scompt(-10000,true)
suivi de

Code :

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

1
2
3
4
5
 
SELECT champ1, champ2, scompt([champ2],False) AS monordre
FROM (SELECT champ1, champ2
FROM matable
ORDER BY champ2 )  AS tempo;

ordonnera à partir de -9999

[Tofalu]

Random, le soucis c'est que c'est recalculé dés qu'il y a un requery de la requête sans que la valeur de début ne soit remise à jour

Il y a même des fois où l'affichage dans un formulaire est complètement mélangé

[random]

en ce qui concerne la remise à zéro, elle n'est pas toujours nécessaire si on souhaite seulement retrouver une suite scalaire
j'ai prévu la possibilité toutefois d'une remise à 0
Function scompt(x As Variant, raz As Boolean) As Long

le compteur est réinitialisé à x si raz

il est évident qu'une nouvelle requête relance le calcul sans réinitialiser le compteur aussi on utilisera cette fonction uniquement en final avec une requête déjà classée et ordonnée
select meschamps, moncompteur(unchamp) as tri from
(select meschamps from matable order by)

en ce qui concerne les affichages dans un formulaire, je suis extrêment surpris
rien n'expliquerait un mélange sauf peut être un sort du formulaire différent de celui de la reqête mais je vais tester

je ne dis pas que cette technique peut être utilisée sans précaution, mais une utilisation à bon escient apporte des solutions à certains problèmes d'une façon générique, sans devoir recoder du vba et avec des performances très supérieures à celle du sql classique

[Tofalu]

Function scompt(x As Variant, raz As Boolean) As Long

le compteur est réinitialisé à x si raz

Oui, mais ça nécessite de passer par VBA pour faire un premier appel. Si j'ouvre la requête directement, ou si j'exporte l'objet, le résultat risque de ne pas être celui attendu

en ce qui concerne les affichages dans un formulaire, je suis extrêment surpris

Le fait de faire un Current dans le formulaire provoque le recalcul de ce champ pour l'enregistrement, ce qui crée des décalages à chaque enregistrement.

je ne dis pas que cette technique peut être utilisée sans précaution, mais une utilisation à bon escient apporte des solutions à certains problèmes d'une façon générique, sans devoir recoder du vba et avec des performances très supérieures à celle du sql classique

Tout à fait, et il est clair qu'une fonction rownumber() serait bien pratique en SQL

Ne pas oublier que si c'est pour utiliser au sein d'un recordset il est préférable d'utiliser la propriété RecordCount

[FMJ]

J'en reviens donc à une question posée ci-dessus : existe-t-il un évènement d'un formulaire qui soit exécutable AVANT la requête source ?

Dans l'affirmative, comme mon pb de perf provient de la taille d'une table sur laquelle on requête et du nombre de fois où la sous-requête imbriquée est exécutée, je pourrais ainsi m'arranger pour faire préalablement le regroupement des enregistrements qui m'intéressent dans une table temporaire qui aurait alors une taille infiniment plus restreinte (= +1 exécution un peu "lourde") + mettre à jour un champ "numéro de rang" dans cette table avec une de requête SQL décrite ci-dessus (= +1 exécution mais très light)

Et ce au lieu de N exécutions "lourdes" sur les 100 000 enregistrements de base. ll me suffirait alors de rajouter cette table à la requête source du formulaire.


Est-ce que ce type d'évènement existe car .... après tests et vérifications, je ne l'ai pas trouvé, la requête étant toujours exécutée en 1er !