Discussion :

[LeXo]

BonJour

voila j'ai 2 tables avec 1 clé primaire NumContact

les tables contiennent des infos différentes

je voulais prendre les enregistrements qui sont dans la table1 mais pas dans la table2

alors j'ai fais

Code :

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

select * from table where not exists (select * from table2);

mais il me sort aucun enregistrement

d'avance merci

[al1_24]

Comme ça, ça marchera mieux

Code :

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

1
2
3
4
5
6
7
8
9
 
select * 
from table 
where not exists 
  (select * 
   from table2 
   where table2.NumContact = table.NumContact
  )
;

[LeXo]

Ya 2100 dans la premiere table et 23000 et la table 2 et chaque fois que je lance la requete access tourne et fini par planter

c a cause du nombre d'enregistrement ?

[Mathusalem]

si les 2 tables ont la même clé tu pourrais simplement faire un NOT IN sur la clé, plutôt qu'un NOT EXISTS sur *

[LeXo]

comme ça

Code :

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

1
2
3
SELECT * 
FROM TABLE 
WHERE TABLE.NUMCONTACT NOT IN  (SELECT table2.NumContact  FROM table2 );

bha pareil ça aime pas ça affiche les premiers enregistrement et des que je descend voir les derniers ça plante

c pour ça que je suis demandé de l'aide pensant que je me plantais de formule

[stigma]

3 ans plus tard, je viens juste remercier car ça m'a bien dépanné et ça marche plutôt bien !

[Delioneras]

(encore 3 ans plus tard, mais si quelqu'un - comme moi - retombe dessus ...)

SELECT *
FROM TABLE
WHERE TABLE.NUMCONTACT NOT IN (SELECT table2.NumContact FROM table2 );

Personnellement, j'aurais plus fait

Code :

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

1
2
3
4
5
6
 
SELECT Table.*
FROM Table
LEFT OUTER JOIN Table2
ON Table.NumContact = Table2.NumContact
WHERE ISNULL(Table2.NumContact);

Ca évite l'imbrication, ça va plus vite, que du bon.

[Waldar]

Ca évite l'imbrication, ça va plus vite, que du bon.

Il faut argumenter ces deux points.
Pourquoi l'imbrication serait-ce mal ?
Prouvez-nous que ça va plus vite.

[SQLpro]

Il faut argumenter ces deux points.
Pourquoi l'imbrication serait-ce mal ?
Prouvez-nous que ça va plus vite.

Tu t'emmerdes tant au bureau que tu déterres les cadavres ??? ;-)

A +

[J1]

(encore 3 ans plus tard, mais si quelqu'un - comme moi - retombe dessus ...)



Personnellement, j'aurais plus fait

Code :

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

1
2
3
4
5
6
 
SELECT Table.*
FROM Table
LEFT OUTER JOIN Table2
ON Table.NumContact = Table2.NumContact
WHERE ISNULL(Table2.NumContact);

Ca évite l'imbrication, ça va plus vite, que du bon.

Il faut argumenter ces deux points.
Pourquoi l'imbrication serait-ce mal ?
Prouvez-nous que ça va plus vite.

Bonsoir,

je confirme les propos de Delioneras quant à la supériorité (en termes de rapidité), dans Access, de la méthode en LEFT JOIN sur la méthode en NOT EXISTS.
Pour s'en convaincre, il suffit de créer sous Access une table comptant une seule colonne mais quelques dizaines de milliers de lignes. Puis de copier-coller ladite table sous un autre nom et de lui ôter une ligne.
Le tout sans aucun index sur les deux colonnes pour ne pas fausser le test.
La comparaison entre les deux méthodes parle alors d'elle-même.
Une nouvelle fois, je parle bien d'Access. Sous SQL Server, c'est une autre histoire.

En revanche, pour en revenir à la solution proposée par Delioneras, je déconseillerais de passer par la fonction ISNULL d'Access (à ne pas confondre avec la fonction ISNULL de SQL Server) pour tester la NULLité de la colonne dans la clause WHERE finale. Il est plus rapide de passer par l'opérateur normalisé IS NULL.

[punkoff]

Le tout sans aucun index sur les deux colonnes pour ne pas fausser le test.

Le test est donc totalement caduc.

Les colonnes de jointure, dans une base de donnée, doivent etre indéxées.

[Delioneras]

Il faut argumenter ces deux points.
Pourquoi l'imbrication serait-ce mal ?
Prouvez-nous que ça va plus vite.

Imbriquer des requêtes impliquent plusieurs analyses de la base de données.
Access va créer une boucle à chaque imbrication pour récupérer correctement chaque membre.
A l'inverse, l'utilisation en jointure aplanit le tout, permettant au moteur de se débrouiller avec un seul passage.
Plus rapide.

Ce n'est pas flagrant sur 3 lignes, mais d'expériences sur Oracle avec plusieurs milliers, la différence est nette.
Même chose pour SQL Server. Et Access pour l'avoir testé hier même.

Le test est donc totalement caduc.

Les colonnes de jointure, dans une base de donnée, doivent etre indéxées.

En terme de normalisation, le prérequis d'indexation est exact.
Mais en terme technique ? C'est faux.

Aucune base de données n'oblige l'indexation des informations, excepté clé primaire.
Et d'expérience, je sais que toute jointure ne se fait pas nécessairement sur la clé primaire d'une table.

Le test n'a rien de caduc, c'est une réalité professionnelle à laquelle je suis régulièrement confronté.
C'est peut-être les boites pour lesquelles j'ai bossé qui ont de mauvaises méthodes (dieu sait que j'ai pesté tout ce que j'ai pu quand j'ai percuté que la DB d'une certaine boite où j'ai bossé ne contenait AUCUNE intégrité référentielle. Mais j'ai du faire avec), il n'empêche que c'est permis au niveau SGBD, et donc utilisable.

[tatayo]

C'est peut-être les boites pour lesquelles j'ai bossé qui ont de mauvaises méthodes (dieu sait que j'ai pesté tout ce que j'ai pu quand j'ai percuté que la DB d'une certaine boite où j'ai bossé ne contenait AUCUNE intégrité référentielle. Mais j'ai du faire avec), il n'empêche que c'est permis au niveau SGBD, et donc utilisable.

Pff, si je fais le bilan chez nous:
ERP: pas d'intégrité référentielle
WMS: pas d'intégrité référentielle
Front Office: pas d'intégrité référentielle

Et quand je demande pourquoi, j'ai comme réponse "On préfère le faire dans l'application, c'est plus souple"

Bref la seule base sur laquelle des contraintes ont été définie est celle que nous utilisons en interne...

Tatayo.

[J1]

Le test est donc totalement caduc.

Les colonnes de jointure, dans une base de donnée, doivent etre indéxées.

Oui et non : tout dépend des autres traitements que tu appliques sur les tables concernées. Si tu dois également réaliser beaucoup de requêtes d'écriture, tes index vont a contrario te ralentir.
C'est donc un juste équilibre à trouver.

Quoi qu'il en soit, ta remarque a eu le mérite de me piquer et j'ai effectué le même test que précédemment après avoir créé des index (non-uniques) sur mes deux colonnes et bien sûr démultiplié le nombre de lignes de mes deux tables pour être sûr que la différence de rapidité entre les deux requêtes soit perceptible.
J'ai donc deux tables d'une seule colonne (indexée) et d'environ 500 000 lignes chacune.
Et là, je constate une inversion des résultats : la requête en NOT EXISTS est plus rapide que la requête en LEFT JOIN. J'avoue que je suis particulièrement surpris.

J'en ai profité pour refaire le test sans index, toujours sur ces tables de 500 000 lignes.
Côté distribution de données, je précise bien que les 500 000 valeurs de chaque table ne sont pas uniques.
Un petit bilan de mon jeu de tests :

Méthode en LEFT JOIN sans index : 13 secondes
Méthode en NOT EXISTS sans index : stoppée après 7 minutes d'exécution
Méthode en LEFT JOIN avec index : (toujours) 13 secondes
Méthode en NOT EXISTS avec index : 5 secondes

Au final, on constate que la méthode en LEFT JOIN reste raisonnable avec ou sans index, contrairement à la méthode en NOT EXISTS, qui est elle inexploitable sans index... mais intéressante avec index.

Je suis surpris... et en même temps très intéressé !
Delioneras, pourrais-tu faire le même test de ton côté et nous en communiquer le résultat, s'il-te-plaît ?

[Delioneras]

Hum ... Surpris également, sur une base plus petite, j'obtiens des résultats sensiblement similaires.

Je maintiens que j'ai une préférence personnelle pour les jointures, mais je veux bien retirer le fait que ça va plus vite qu'une imbrication

Mea culpa

[J1]

Pour le coup, on touche vraiment un point très intéressant, là.
Dommage qu'Access ne permette pas de visualiser le plan d'exécution d'une requête, ça nous apprendrait probablement bien des choses sur le comportement du moteur dans le cas qui nous occupe.
Enfin, pour être exact, Access le permet via une bidouille dans la base de registre qui permet de générer un fichier texte contenant le plan d'exécution des requêtes exécutées... sauf que cela ne fonctionne pas pour les requêtes contenant des sous-requêtes ! (comme c'est le cas avec notre NOT EXISTS)

[Waldar]

Je ne connais pas vraiment Access, mais n'y a-t-il pas l'équivalent d'un plan d'exécution ou de statistiques d'exécution de requête, autres que la durée comme la quantité de données lues, les méthodes d'accès, et cetera ?

J1, pourriez-vous si vous en avez l'occasion faire un test avec deux tables possédant une dizaine de colonnes (la jointure toujours sur une seule colonne) ?

Delioneras, pas besoin de mea culpa : la discussion est saine et profitable à tous.

Edit : J1 a répondu au premier point le temps de la rédaction de ce message.

[SQLpro]

Accès n'a pas d'optimiseur, c'est une base fichier.....

A +

[J1]

Je ne suis pas sûr d'avoir compris ce que tu souhaitais, Waldar : que j'exécute exactement les mêmes requêtes, mais sur des tables contenant 9 colonnes supplémentaires (non indexées et non exploitées dans la "jointure") ?

Pour être sûr que nous nous comprenons bien, les 2 requêtes exécutées :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT *
FROM t1
WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE f1=t1.f1);
 
SELECT t1.*
FROM t1 
LEFT JOIN t2 
	ON t2.f1=t1.f1
WHERE t2.f1 IS NULL;

Si c'est bien ce que tu attends, voilà le résultat (réalisé sur deux tables de 100 000 lignes et 10 colonnes chacune) :

Méthode en LEFT JOIN sans index : (un peu moins de) 2 secondes
Méthode en NOT EXISTS sans index : stoppée après 1 minute d'exécution
Méthode en LEFT JOIN avec index : (un peu moins de) 2 secondes
Méthode en NOT EXISTS avec index : (un peu plus de) 1 seconde

Qu'en déduis-tu et pourquoi ce test ?

Accès n'a pas d'optimiseur, c'est une base fichier.....

A +

Quel que soit son nom (optimiseur ou autre), il existe bien un mécanisme dans Access qui tente de déterminer la façon optimale d'exécuter une requête. Sans quoi il serait inutile de créer des index.
D'ailleurs, je viens de trouver cet article qui s'attarde un peu sur la question et emploie bien le terme d'optimiseur de requêtes pour le moteur Jet.

[punkoff]

Oui et non : tout dépend des autres traitements que tu appliques sur les tables concernées. Si tu dois également réaliser beaucoup de requêtes d'écriture, tes index vont a contrario te ralentir.
C'est donc un juste équilibre à trouver.

Le seul cas (que je vois) où un index est inutile c'est quand le sgbd choisit de faire une jointure de type hash (ou que la jointure est trop farfelue mais c'est un autre probleme)

Pour les actions de chargement de masse ...un index se désactive / supprime.


A partir de ca je ne vois pas en quoi poser un index et laisser l'optimiseur choisir est néfaste, surtout vu le postulat de base : ne pas créer d'index pour ne pas fausser les résultats



La on est dans un cas basique le numcontrat doit etre une clef étrangère