[SQL] l'UNION fait la FORCE !!

Rebonjour,

Voici les résultats :-))

J'ai fait comme suggéré par Michel avec x requetes
intermédiaires avec chacune la clause WHERE avant de bâtir
la requête union sur ces requêtes qui ne retournent que
quelques enregistrements :

Résultat :
10 secondes pour obtenir le résultat au lieu de 20
secondes avec la syntaxe précédemment testée.

Pour Jessy à qui j'ai envoyé la structure de base :
MARAZ :
SELECT *
FROM MARA
WHERE MFRPN Like [Critere];

MAKTZ :
SELECT *
FROM MAKT
WHERE MAKTX Like [Critere];

EINAZ :
SELECT *
FROM EINA
WHERE IDNLF Like [Critere];


REQUETE_METHODE_WALSH :
SELECT MARAZ.MATNR AS Article,
MAKTX AS Designation,
MTART AS Type,
MATKL AS GM,
BISMT AS Ancien_No,
MFRPN AS Reference,
MFRNR AS Fab_Frs,
BWKEY AS Division,
BKLAS AS CV

FROM (MARAZ INNER JOIN MAKTZ
ON MARAZ.MATNR = MAKTZ.MATNR)

INNER JOIN MBEW
ON MARAZ.MATNR = MBEW.MATNR

UNION SELECT MARAZ.MATNR,
MAKTX,
MTART,
MATKL,
BISMT,
MFRPN,
MFRNR,
BWKEY,
BKLAS

FROM (MARAZ INNER JOIN MAKTZ
ON MARAZ.MATNR = MAKTZ.MATNR)

INNER JOIN MBEW
ON MARAZ.MATNR = MBEW.MATNR

UNION SELECT MARAZ.MATNR,
MAKTX,
MTART,
MATKL,
BISMT,
IDNLF,
LIFNR,
BWKEY,
BKLAS

FROM (MARAZ INNER JOIN (EINE
INNER JOIN (EINAZ
INNER JOIN MBEW
ON EINAZ.MATNR = MBEW.MATNR)
ON EINAZ.INFNR = EINE.INFNR
AND EINE.WERKS = MBEW.BWKEY)
ON MARAZ.MATNR = EINAZ.MATNR)
INNER JOIN MAKTZ ON MARAZ.MATNR = MAKTZ.MATNR

WHERE LVORM Is Null;

Bonne continuation à tous

Arnaud qui une fois n'est pas coutume, vous a rendu visite
depuis son travail ;-)

Salut,


Mes hypothèses se confirment drôlement... si on permet que je me
cite moi-même:

"... devrait alors redonner un temps d'exécution d'une dizaine de
secondes (deux fois moins long que la requête UNION), ..."



mais je l'avoue, c'est un "coup de chance". Si cela a tendance à confirmer
quelque chose, c'est que Arnaud est logique... dans le sens que je donne à
cette expression... les grands esprits se rencontrent, quoi... :-)



Vanderghast, Access MVP



"Arnaud [lwa]" <arnaud@microsoft.com> wrote in message
news:2bc7101c468e8$0dbf9fe0$a501280a@phx.gbl...
Rebonjour,

Voici les résultats :-))

J'ai fait comme suggéré par Michel avec x requetes
intermédiaires avec chacune la clause WHERE avant de bâtir
la requête union sur ces requêtes qui ne retournent que
quelques enregistrements :

Résultat :
10 secondes pour obtenir le résultat au lieu de 20
secondes avec la syntaxe précédemment testée.

Pour Jessy à qui j'ai envoyé la structure de base :
MARAZ :
SELECT *
FROM MARA
WHERE MFRPN Like [Critere];

MAKTZ :
SELECT *
FROM MAKT
WHERE MAKTX Like [Critere];

EINAZ :
SELECT *
FROM EINA
WHERE IDNLF Like [Critere];


REQUETE_METHODE_WALSH :
SELECT MARAZ.MATNR AS Article,
MAKTX AS Designation,
MTART AS Type,
MATKL AS GM,
BISMT AS Ancien_No,
MFRPN AS Reference,
MFRNR AS Fab_Frs,
BWKEY AS Division,
BKLAS AS CV

FROM (MARAZ INNER JOIN MAKTZ
ON MARAZ.MATNR = MAKTZ.MATNR)

INNER JOIN MBEW
ON MARAZ.MATNR = MBEW.MATNR

UNION SELECT MARAZ.MATNR,
MAKTX,
MTART,
MATKL,
BISMT,
MFRPN,
MFRNR,
BWKEY,
BKLAS

FROM (MARAZ INNER JOIN MAKTZ
ON MARAZ.MATNR = MAKTZ.MATNR)

INNER JOIN MBEW
ON MARAZ.MATNR = MBEW.MATNR

UNION SELECT MARAZ.MATNR,
MAKTX,
MTART,
MATKL,
BISMT,
IDNLF,
LIFNR,
BWKEY,
BKLAS

FROM (MARAZ INNER JOIN (EINE
INNER JOIN (EINAZ
INNER JOIN MBEW
ON EINAZ.MATNR = MBEW.MATNR)
ON EINAZ.INFNR = EINE.INFNR
AND EINE.WERKS = MBEW.BWKEY)
ON MARAZ.MATNR = EINAZ.MATNR)
INNER JOIN MAKTZ ON MARAZ.MATNR = MAKTZ.MATNR

WHERE LVORM Is Null;

Bonne continuation à tous

Arnaud qui une fois n'est pas coutume, vous a rendu visite
depuis son travail ;-)

Salut,


Sous Jet, ce serait probablement AFFREUX, car je ne pense pas que
Rushmore verrait l'optimisation possible.... et si c'est le cas, un
dépassement de capacité de stockage temporaire (limité à 2Go) est à
craindre... après avoir rempli 2Go sur disque de données irrelevantes!)
Sous MS SQL Server, c'est à voir... particulièrement la version 2000 qui
peut " s'adapter " en cours de route!


Non, Jet fut pensé, dès le début, dans une optique de cascade de
requêtes, et cela est très bien optimisé... et même louable. Et cela
correspond générallement à l'approche optimale de résolution des problèmes
relationnels. L'ajout de table virtuelles, ou la recherche de "tout faire en
une seule requête" est un ajout relativement récent (Jet 4.0 pour les tables
virtuelles) et risque de ne pas être trop bien optimisé par ... l'optimiseur
de requêtes... utilisé par Jet... même si pour de petites tables, cela tient
encore bien le coût... mais pour 400k et 700k-enregistrements... c'est
forcé...



Vanderghast, Access MVP



"Jessy Sempere [MVP]" <jessy.sempere@prg.sncf.fr> wrote in message
news:cd0mqi$fji$1@muguet.sncf.fr...

Bonjour

Encore une fois tu m'impressionnes par tes explications... ;-)

Petite question :
Et si on spécifiait la jointure directement dans la clause WHERE,
qu'est ce que ça donnerait en terme de performance ???

Genre :
SELECT MARA.MATNR AS Article,
MAKTX AS Designation,
MTART AS Type,
MATKL AS GM,
BISMT AS Ancien_No,
MFRPN AS Reference,
MFRNR AS Fab_Frs,
BWKEY AS Division,
BKLAS AS CV

FROM MARA, MAKT, MBEW

WHERE MARA.MATNR = MAKT.MATNR AND
MARA.MATNR = MBEW.MATNR AND
(MAKTX Like [Critere] OR MFRPN Like [Critere]);

@+
Jessy Sempere - Access MVP
news@access.fr.vu
------------------------------------
Site @ccess : http://access.jessy.free.fr/
Pour l'efficacité de tous :
http://users.skynet.be/mpfa/
------------------------------------
"Michel Walsh" <vanderghast@VirusAreFunnierThanSpam> a écrit dans le
message news:

eh$$BXNaEHA.3804@TK2MSFTNGP10.phx.gbl...

Salut,


La raison du problème semble toute simple. La jointure est effectuée
AVANT

le where. Donc, il y a comparaisons entre plusieurs champs,
"l'explosion" du

join (une possibilité de 400 000 au carré, j'apelle cela une
explosion)

est emmagasinée temporairement, et de cette explosion, établie, on ne
conserve, par sélection de critère WHERE, que quelques individus.
Ressources

mal utilisées!

Il serait plus astucieux, dans ce cas, de faire le critère AVANT la
jointure:

SELECT * FROM makt WHERE maktx LIKE [Critere]

dans une requête, S1, pareil pour mfrpn LIKE [Critere], dans une
requête

S2 (ou la même requête S1, si c'est de la même table). Cette requête
implique maitenant beaucoup MOINS que 400 000 enregistrement,
probablement

100 000, non (mon estimé est basé sur des hypothèses un peu farfelues,
mais

également de par les chiffres que tu avances: 100k = 400k * ( 170 /
(18*0.5) ) ^0.5) ?

Utiliser S1 ( et S2 si applicable) au lieu des tables originales, avec
une

syntaxe voisine de la première requête, sans sa condition WHERE
maintenant

inutile, devrait alors redonner un temps d'exécution d'une dizaine de
secondes (deux fois moins long que la requête UNION), voire mieux
(surtout

si les requêtes S1 et S2 retournent en deça de 100k-enregistrements).


Si tu utilises MS SQL Server, tu peux faire remonter le critère du
genre:

champ=constante
DANS la clause ON appropriée du join, cela aura le même effet. Avec Jet,
on

ne peut pas, car Jet repasse derrière nous et redescend ce critère dans
le

WHERE, à moins de procéder par requêtes cascadées, comme proposé, ou par
tables virtuelles (ce qui est la même chose, dans le fond).


Espérant être utile,
Vanderghast, Access MVP


"Arnaud [lwa]" <anonymous@discussions.microsoft.com> wrote in message
news:2bc2301c468b9$873f7d60$a401280a@phx.gbl...
oups désolé message parti trop vite avec l'interface
WEB....et avant qu'elle apparaisse ....
je continue donc :
alors que la requête UNION ci-dessous
retourne le même résultat en 18 secondes au lieu de 170.

SELECT MARA.MATNR AS Article,
MAKTX AS Designation,
MTART AS Type,
MATKL AS GM,
BISMT AS Ancien_No,
MFRPN AS Reference,
MFRNR AS Fab_Frs,
BWKEY AS Division,
BKLAS AS CV

FROM (MARA INNER JOIN MAKT
ON MARA.MATNR = MAKT.MATNR)

INNER JOIN MBEW
ON MARA.MATNR = MBEW.MATNR

WHERE MAKTX Like [Critere]

UNION SELECT MARA.MATNR AS Article,
MAKTX AS Designation,
MTART AS Type,
MATKL AS GM,
BISMT AS Ancien_No,
MFRPN AS Reference,
MFRNR AS Fab_Frs,
BWKEY AS Division,
BKLAS AS CV

FROM (MARA INNER JOIN MAKT
ON MARA.MATNR = MAKT.MATNR)

INNER JOIN MBEW
ON MARA.MATNR = MBEW.MATNR

WHERE MFRPN Like [Critere];



ps : DISTINCT inutile dans une requête UNION,
mais pas de différence notoire entre les temps dexécution
d'un SELECT et par rapport à un SELECT DISTINCT.

--
Bonne journée
Arnaud
http://memoaccess.free.fr

-----Message d'origine-----
Bonjour,

Une constatation simple sur 2 requêtes impliquant pour
l'exemple 1 table de 700000 enregistrements et 2 tables
de

400000 enregeistrements :

Résultat obtenu au bout de 170 Secondes

SELECT MARA.MATNR AS Article,
MAKTX AS Designation,
MTART AS Type,
MATKL AS GM,
BISMT AS Ancien_No,
MFRPN AS Reference,
MFRNR AS Fab_Frs,
BWKEY AS Division,
BKLAS AS CV

FROM (MARA INNER JOIN MAKT
ON MARA.MATNR = MAKT.MATNR)

INNER JOIN MBEW
ON MARA.MATNR > > >MBEW.MATNR

WHERE MAKTX Like [Critere]
or MFRPN Like [Critere];


.

Bonjour Michel,

Je réponds à ton estimation :

| Cette requête implique maitenant beaucoup MOINS que 400 000
| enregistrement, probablement 100 000, non (mon estimé est basé sur
| des hypothèses un peu farfelues, mais également de par les chiffres
| que tu avances: 100k = 400k * ( 170 / (18*0.5) ) ^0.5) ?

en fait dans le cas présent, j'avais 3 requetes union sur 3 tables.

table 1 = 700000 enregistrements
table 2 = 400000 enregistrements
table 3 = 400000 enregistrements

requete 1 = résultat 8 enregistrements
requete 2 = résultat 6 enregistrements
requete 3 = résultat 3 enregistrements

Plutôt que de mettre en place des regroupements, je choisissais et
positionnais les champs dans une requete union pour obtenir non
pas 8+6+3 = 17 enregistrements, mais par l'effet induit "DISTINCT"
de la requête union, seulement une douzaine d'enregistrements "distincts"

| Utiliser S1 ( et S2 si applicable) au lieu des tables originales,
| avec une syntaxe voisine de la première requête, sans sa condition
| WHERE maintenant inutile, devrait alors redonner un temps d'exécution
| d'une dizaine de secondes (deux fois moins long que la requête
| UNION), voire mieux (surtout si les requêtes S1 et S2 retournent en
| deça de 100k-enregistrements).

effectivement, la gain était bien celui-là, je n'avais pas vu que tu avais allumé
ta boule de cristal pour répondre ;-))

Merci en tout cas, et espérant que cet exemple concrêt aura démontré que
access est capable de manipuler pas mal de données avec de bonnes performances,
dès lors que les requêtes sont bien structurées.

à+
--
Arnaud
--------------------------------------------------
Conseils d'utilisation : http://users.skynet.be/mpfa/
Site Perso : http://memoaccess.free.fr
/Réponses souhaitées sur ce forum, merci/
--------------------------------------------------

| un
| dépassement de capacité de stockage temporaire (limité à 2Go) est à
| craindre... après avoir rempli 2Go sur disque de données
| irrelevantes!)

je comprends pourquoi j'ai eu tant de mal à vider mes tables en mettant des critères
à la noix pour ne garder que quelques exemples utilisables ;-)))

| Non, Jet fut pensé, dès le début, dans une optique de cascade de
| requêtes, et cela est très bien optimisé... et même louable.

Là, j'apprends encore quelque chose.
Je croyais que la requête "tout en un" c'était mieux ....

Alors entre maintenabilité et performance, désormais, j'ai choisi :-)

Merci pour tous ces conseils Ô Dominus Magnus

;-))
à+
--
Arnaud
--------------------------------------------------
Conseils d'utilisation : http://users.skynet.be/mpfa/
Site Perso : http://memoaccess.free.fr
/Réponses souhaitées sur ce forum, merci/
--------------------------------------------------