Discussion :

[marot_r]

Bonjour à tous et merci de votre temps et de votre aide.

J'ai une requête qui est TRÈS lente car elle utilise des DSum.
Mes esssais semblent indiquer que qu'avec des sous-requêtes j'irai plus vite mais je n'arrive pas à écrire ce que je veux ni à saisir les exemples que j'ai trouvés sur Google.

Ici le SQL de ma requête un peu simplifié :

Code sql :

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

1
2
3
4
5
6
7
8
9
 
SELECT "[NoGroupeAff]=" & [tblBaseGroupeAff].[No] & " and [Annee]=" & [reqBaseParam_Annee].[Annee] & " and [NoMois]<=" & [reqBaseParam_NoMois].[NoMois] AS Critere, 
 
            (SELECT Sum(reqHP_TDBIntervention.IndicateurNonDummy) AS SommeDeIndicateurNonDummy
                         FROM reqHP_TDBIntervention
                         WHERE (((reqHP_TDBIntervention.[NoGroupeAff])=1) AND ((reqHP_TDBIntervention.[Annee])=2016) AND ((reqHP_TDBIntervention.[NoMois])<=5));) 
                                    AS NbIntervention
 
            FROM reqBaseParam_NoMois, reqBaseParam_Annee, tblBaseTerr INNER JOIN tblBaseGroupeAff ON tblBaseTerr.[No] = tblBaseGroupeAff.NoTerr

celle-ci marche mais 1, 2016 et 5 sont des constantes alors que je souhaite que ce soit des variables en fonction du groupe d'affectation (1), de l'année (2106) et du mois choisi (5)

J'ai essayé cela :

Code sql :

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

1
2
3
4
5
6
7
8
9
SELECT "[NoGroupeAff]=" & [tblBaseGroupeAff].[No] & " and [Annee]=" & [reqBaseParam_Annee].[Annee] & " and [NoMois]<=" & [reqBaseParam_NoMois].[NoMois] AS Critere, 

            (SELECT Sum(reqHP_TDBIntervention.IndicateurNonDummy) AS SommeDeIndicateurNonDummy
                         FROM reqHP_TDBIntervention
                         WHERE (((reqHP_TDBIntervention.[NoGroupeAff])=[tblBaseGroupeAff].[No]) AND ((reqHP_TDBIntervention.[Annee])=[reqBaseParam_Annee].[Annee]) AND ((reqHP_TDBIntervention.[NoMois])<=[reqBaseParam_NoMois].[NoMois]));) 
                                    AS NbIntervention

            FROM reqBaseParam_NoMois, reqBaseParam_Annee, tblBaseTerr INNER JOIN tblBaseGroupeAff ON tblBaseTerr.[No] = tblBaseGroupeAff.NoTerr

Mais lors de l'exécution de la sous-requête, il me dit qu'il ne connait pas [tblBaseGroupeAff].[No], [reqBaseParam_Annee].[Annee], [reqBaseParam_NoMois].[NoMois].
Ce qui est logique puisque les tables et requêtes ne sont pas dans la clause FROM de la sous-requête.

Donc ma question est : comment faire pour que la sous-requête se réfère aux champs visibles dans la requête principale.

A+

[User]

Bonjour René,

Tu as essayé d'enregistrer la requête pour tester, ou de mettre un alias dans la sous-requête ?

Cdlt,

[marot_r]

Bonjour User.

Tu as essayé d'enregistrer la requête pour tester

J'aimerai minimiser mon nombre de requêtes enregistrées. J'en ai déjà un paquet et j'aimerai que ma source de données (cette requête est utilisée pour un export vers Excel) reste "unique".
Les Dsum résolvaient se problème mais ils ont trop lent (env 5mn)

ou de mettre un alias dans la sous-requête ?

Comment on fait cela ?

A+

[User]

Salut,

Bonjour User.



J'aimerai minimiser mon nombre de requêtes enregistrées. J'en ai déjà un paquet et j'aimerai que ma source de données (cette requête est utilisée pour un export vers Excel) reste "unique".
Les Dsum résolvaient se problème mais ils ont trop lent (env 5mn)

A+

Peux-tu détailler ta procédure d'export de la requête vers Excel ?

J'ai essayé d'exporter simplement une requête comportant une sous-requête et je n'ai pas eu de problème.

à+

[marot_r]

Bonjour.

Mon problème ne se situe pas dans l'export vers Excel qui marche mais qui est très lent.

Mes tests mettent bien en cause mon DSum(). Si je l'enlève la requête s'exporte en quelques secondes.

Mon problème est de remplacer mon DSum() par une sous-requête, si c'est possible.

Pour faire cela, il faut que je récupère des champs de la requête principale pour qu'ils servent de paramètre de sélection à la sous-requête.

ce sont les termes soulignés dans

Code sql :

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

1
2
3
(reqHP_TDBIntervention.[NoGroupeAff])=[tblBaseGroupeAff].[No]) 
AND ((reqHP_TDBIntervention.[Annee])=[reqBaseParam_Annee].[Annee]) 
AND ((reqHP_TDBIntervention.[NoMois])<=[reqBaseParam_NoMois].[NoMois])

Je m'y prends peut-être mal.

A+

[User]

Peux-tu poster le sql avec dsum pour que je puisses comparer les 2 versions.

Ensuite, il y a la partie select que je ne comprend pas avec les guillemets :

Code sql :

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

SELECT "[NoGroupeAff]=" & [tblBaseGroupeAff].[No] & " and [Annee]=" & [reqBaseParam_Annee].[Annee] & " and [NoMois]<=" & [reqBaseParam_NoMois].[NoMois] AS Critere, ...

Edit:

ok, j'ai compris c'est pour afficher l'expression du critère

à+

[marot_r]

Avec le DSUM :

Code sql :

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

1
2
3
4
5
SELECT "[NoGroupeAff]=" & [tblBaseGroupeAff].[No] & " and [Annee]=" & [reqBaseParam_Annee].[Annee] & " and [NoMois]<=" & [reqBaseParam_NoMois].[NoMois] AS Critere, 
 
            DSum("IndicateurNonDummy", "reqHP_TDBIntervention", [critere]) AS NbIntervention
 
            FROM reqBaseParam_NoMois, reqBaseParam_Annee, tblBaseTerr INNER JOIN tblBaseGroupeAff ON tblBaseTerr.[No] = tblBaseGroupeAff.NoTerr

Note que la requête de base est un produit croisé qui donne pour le mois et l'année courante tous les groupes d'affectation possibles.

J'ai mis le critère dans un champ pour le voir et pour pouvoir m'en servir dans les 4 autres DSum() de ma requête.

J'ai essayé d'ajouter les champs de critère (NoGroupeAff, Annee et NoMois) et de leur assigner un alias MainNomChamp mais Access continue à me les demander à l'exécution.
Les exemples que j'ai trouvé sur le Net disent pourtant que la sous-requête peut lire les champs de la requête.

A+

[User]

Avec le DSUM :

Code sql :

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

1
2
3
4
5
SELECT "[NoGroupeAff]=" & [tblBaseGroupeAff].[No] & " and [Annee]=" & [reqBaseParam_Annee].[Annee] & " and [NoMois]<=" & [reqBaseParam_NoMois].[NoMois] AS Critere, 
 
            DSum("IndicateurNonDummy", "reqHP_TDBIntervention.IndicateurNonDummy", [critere]) AS NbIntervention
 
            FROM reqBaseParam_NoMois, reqBaseParam_Annee, tblBaseTerr INNER JOIN tblBaseGroupeAff ON tblBaseTerr.[No] = tblBaseGroupeAff.NoTerr

Note que la requête de base est un produit croisé qui donne pour le mois et l'année courante tous les groupes d'affectation possibles.

De mémoire, les sous-requêtes ne marchent pas avec les requêtes analyse croisée.

Ne peux-tu pas ajouter les données de la requête croisée dans une table temporaire ?

à+

[marot_r]

Bonjour.

Ce n'est pas une requête d'analyse croisée mais un produit croisé (soit 3 tables ensembles sans relations entre elles).

Pour les tables c'est la même raison qui me pousse à utiliser une sous-requête : une seule source pour mes données.

La je n'ai mis qu'un seul DSum() mais j'en ai en réalité 4 et ensuite je fais des calculs avec les sommes obtenues.

J'ai fait un autre essai.

Code sql :

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

1
2
3
4
5
6
7
 
SELECT tblBaseGroupeAff.[No] AS MainNoGroupeAff, 
    (SELECT Sum(reqHP_TDBIntervention.IndicateurNonDummy) AS SommeDeIndicateurNonDummy
      FROM reqBaseParam_NoMois, reqHP_TDBIntervention INNER JOIN reqBaseParam_Annee ON reqHP_TDBIntervention.Annee = reqBaseParam_Annee.Annee
      WHERE (((reqHP_TDBIntervention.NoMois)<=[reqBaseParam_NoMois].[NoMois]) and [reqHP_TDBIntervention].[NoGroupeAff]=[MainNoGroupeAff])
      GROUP BY reqHP_TDBIntervention.NoGroupeAff;) AS NbIntervention
FROM tblBaseGroupeAff;

En ne gardant qu'une seule table dans la requête principale (et en virant les champs accessoires) et même résultat. Il n'est pas capable de voir le NoGroupeAff de la requête principale.
Là il y a un alias sur le numéro mais mêmes sans alias cela ne fonctionne pas .

A+

[marot_r]

Bon je viens de faire un test avec une des BD exemple de M$ et cela fonctionne dans ce cas.

Code sql :

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

1
2
3
4
SELECT Commandes.[Réf commande], (SELECT Sum([Détails commande].Quantité) AS SommeDeQuantité
FROM [Détails commande]
WHERE ((([Détails commande].[Réf commande])=[Commandes].[Réf commande]));) AS Qté
FROM Commandes;

Cela donne bien la quantité commandée par commande.

Je continue ma quête.

A+

[User]

Et si on revient à la 1ère requête, est-ce que tu peux pas enlever la sous-requête et faire un groupe by sur :

"[NoGroupeAff]=" & [tblBaseGroupeAff].[No] & " and [Annee]=" & [reqBaseParam_Annee].[Annee] & " and [NoMois]<=" & [reqBaseParam_NoMois].[NoMois]

Et utiliser la fonction sum un peu comme ceci :

Code sql :

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

1
2
3
4
SELECT "[NoGroupeAff]=" & [tblBaseGroupeAff].[No] & " and [Annee]=" & [reqBaseParam_Annee].[Annee] & " and [NoMois]<=" & [reqBaseParam_NoMois].[NoMois] AS Critere, 
Sum(reqHP_TDBIntervention.IndicateurNonDummy) AS NbIntervention
FROM reqHP_TDBIntervention, reqBaseParam_NoMois, reqBaseParam_Annee, tblBaseTerr INNER JOIN tblBaseGroupeAff ON tblBaseTerr.[No] = tblBaseGroupeAff.NoTerr
GROUP BY  "[NoGroupeAff]=" & [tblBaseGroupeAff].[No] & " and [Annee]=" & [reqBaseParam_Annee].[Annee] & " and [NoMois]<=" & [reqBaseParam_NoMois].[NoMois]

Sans garantie...

[marot_r]

Merci du temps que tu y consacres.

J'aurai du préciser que le DSum() est le résultat de plusieurs itérations visant à réduire la complexité de mon application et que les données que j'utilise proviennent de sources diverses et variées.
J'avais au départ 6 requêtes différentes pour arriver au résultat final mais cela était un enfer à maintenir car cette situation se répète pour environ 20 sources différentes donc au final je me retrouvais avec une quantité industrielle de requêtes dont la plus-part n'étaient que des étapes intermédiaires.

Je continue à creuser le sillon de l'exemple qui marche ... tant qu'il y a de la vie, il y a de l'espoir.

A+

[marot_r]

Bonjour à tous.

Alors suite à mes derniers essais j'ai compris pourquoi ma sous-requête ne marche pas : il faut que la sous-requête porte sur une table et non une requête !!!

C'est pour cela que l'exemple basé sur le modèle de M$ fonctionnait.

Et ça c'est VRAIMENT pas cool :-( pour ce que j'avais en tête.

Bon je vais clore cette discussion. Je vais peut-être remplacer ma requête par son SQL mais reste à voir.

Merci User de ton aide.

A+

[marot_r]

Juste un complément à cette discussion pour ceux qui serait confronté à un problème de performance.

J'ai finalement redécouper ma requête en 3 : 1 requête principale et 2 requêtes intermédiaires qui remplacent mes DSum() et je suis passé de 3mn à 1 seconde.

A+

[User]

Bonjour René,

Juste un complément à cette discussion pour ceux qui serait confronté à un problème de performance.

J'ai finalement redécouper ma requête en 3 : 1 requête principale et 2 requêtes intermédiaires qui remplacent mes DSum() et je suis passé de 3mn à 1 seconde.

A+

En effet, l'écart en terme de performance, entre les fonctions de domaine et les sous-requêtes est assez net, comme on le signale d'ailleurs régulièrement sur ce forum

Certains continuent quand même à les utiliser pour leur simplicité et parce qu'elles permettent de mettre à jour la requête, contrairement aux sous-requêtes.

[marot_r]

Certains continuent quand même à les utiliser pour leur simplicité

Oui c'est pour cela que j'avais choisi les DSum.

Au début on ne devait sortir les rapports qu'une fois tous les 15j donc la simplicité primait sur la performance.
Je les ai d'ailleurs gardés pour une requête qui aurait nécessité 4 sous-requêtes + 1 requête principale pour obtenir le même résultat.
Elle met 1mn au lieu de quelques secondes mais elle est plus facile à comprendre et a maintenir.

A+

[User]

Rebonjour,

Je réponds un peu tardivement à quelques-uns de tes commentaires :

Ce n'est pas une requête d'analyse croisée mais un produit croisé (soit 3 tables ensembles sans relations entre elles).

J'avais compris avant mais je n'avais pas eu le temps de me reprendre, et je crois aussi que ma remarque concernant l'impossibilité de l'utilisation des sous-requêtes dans les requêtes analyse croisée était intéressante à noter.

...

Alors suite à mes derniers essais j'ai compris pourquoi ma sous-requête ne marche pas : il faut que la sous-requête porte sur une table et non une requête !!!

Là tu m'étonnes car il me semble que j'ai déjà construit des sous-requêtes basées sur des requêtes.

Cdlt,

[marot_r]

Bonjour User.

Et merci de ton retour.

Là tu m'étonnes car il me semble que j'ai déjà construit des sous-requêtes basées sur des requêtes.

Oui, j'aurai du précisé que si on veut que la sous-requête récupère des informations de la requête principale, il semble obligatoire qu'elle soit basée sur une ou des tables et non un requête.
En tout cas c'est les seul moment où j'ai réussi à le faire.

A+