Office: (Office 2003) Ausschluss Daten aus Abfrageergebnis

Hallo ACCESS-Spezialisten,

ich habe ein keines Problem mit der Formulierung einer Abfrage.
Es existieren 3 Tabellen :
Tbl_Monatsnamen (Enthält die Datensätze "Januar" bis "Dezember")
Tbl_Positiv (enthält 1 Datensatz : J*)
Tbl_Negativ (enthält 1 Datensatz : Ju*)

Weiterhin existieren 2 Abfragen :
QRY_Positiv : zeigt aus den Datensätzen aus TBL_Monatsnamen nur die an, die den Datensätzen aus TBL_Positiv entsprechen (also alle, die mit J* beginnen) : Januar, Juni, Juli

QRY_Negativ : zeigt aus den Datensätzen aus TBL_Monatsnamen nur die an, die den Datensätzen aus TBL_Negativ entsprechen (also alle, die mit Ju* beginnen) : Juni, Juli

Nun möchte ich vom Abfrageergebnis QRY_Positiv die Datensätze "abziehen", die dem Ergebnis der Abfrage QRY_Negativ entsprechen, also : von Januar, Juni, Juli sollen Juni, Juli herausgefiltert werden, so dass nur Januar übrig bleibt.

Leider gelingt mir das nicht :-( Hier brauche ich bitte eure Unterstützung...

Vielen Dank im Voraus,

:)

 

(Die DB kann ich mir als Acc2000-Verwender nicht ansehen.)
Das Stichwort heißt Inkonsistenzabfrage. Im Abfrageeditor gibt es auch einen Assistenten dafür.

 

Ich frage mich erstens wieso das überhaupt nötig ist und zweitens wieso abziehen?

Bau doch gleich eine Abfrage die alle Monate die mit 'J' beginnen aber nicht mit 'Ju' zusammen zieht und Du hast Dein Ergebnis.

Wenn es nicht wirklich zeitkritisch ist oder Unmengen von Daten damit abzufragen sind (also x00.000) dann müsste man das auch über ein NOT IN (Unterabfrage) regeln können.

Also im Prinzip so (Luftcode und ungetestet):

SELECT * FROM qry_Positiv WHERE ID NOT IN (SELECT ID FROM qry_Negativ)

Gruß

Rainer

 

\@Rainer: Wenn es Dir bewusst ist, dass die von Dir verwendete Variante mit "NOT IN" zeitkritisch sein kann, warum nimmst Du nicht gleich Varianten, die auch eine Indexunterstützung verwenden? Es gibt sie doch. Die eine hatte ich oben angesprochen (Verknüpfung mit LEFT JOIN), eine andere findet sich hier: Acc2003 - Anfügeabfrage für DS die nicht vorhanden … (Verwendung NOT EXISTS).

In einem realen Beispiel ergab sich zwischen der Konstruktion mit "NOT IN" und der Konstruktion mit "NOT EXISTS" ein Unterschied mit Faktor 250 zugunsten der zweiten Variante (bei vorhandenen Indizes).

 

Weil ich bei mir in keinem meiner Tests einen zeitlichen Unterschied zwischen NOT IN und NOT EXISTS feststellen konnte ... zumindest nicht im Millisekunden-Bereich. Und ich habe die letzten Monate viele Zeittests für Abfragen gemacht ... und einige waren davon eben auch Messungen zwischen IN und EXISTS.

Einen gröberen Zeitunterschied zwischen NOT IN und Left Join (über ein Index-Feld) konnte ich auch nicht feststellen ... wenn überhaupt vorhanden (in vielen Fällen war der Zeitbedarf exakt gleich) dann waren es ein paar ms zu Gunsten von Left Join.

Klar wie schon gesagt bei mehreren 100.000 DS macht sich das sicherlich bemerkbar.

Gruß

Rainer

 

Das halte ich jetzt für widersprüchlich: Wenn alles gleich langsam bzw. schnell ist, wieso dann ein Hinweis auf Langsamkeit der einen Lösung? Wenn es so ist, ist es so.

Ansonsten lautet das Stichwort "Nutzung eines vorhandenen Index". Ein "NOT" verhindert zuverlässig diese Indexnutzung, außer in der Konstellation "NOT EXISTS".
Details dazu sind recht umfassend dargestellt in Performance in Abfragen von Michael Zimmermann, meiner Meinung nach ein Standardwerk, das man pro Woche einmal lesen sollte, so lange man es nicht auswendig kennt. Abfragen und Abfragengestaltung sind nun einmal Basics in der Datenbankarbeit.

Selbstverständlich werden Unterschiede erst bei größeren Datenmengen spürbar, wo dann die Kraft der Maschine nicht mehr ausreicht, alles zu egalisieren.

 

Ich vermute, du wirst es bereits bei ein paar 1000 merken. Aber nicht bei ein paar 1000 in nur einer Tabelle und 5 DS in der anderen, sondern bei ein paar 1000 in beiden Tabellen. Das ergibt dann nämlich 1000000. *Smilie

BTW: Das Argument mit dem "bei wenig DS nicht relevant" ist für mich kein Gurnd nicht von anfang an eine SQL-Anweisung zu schreiben, die auch mit vielen DS klar kommt - vor allem nicht, wenn es kein Mehraufwand ist.

Hast du auch den Unterschied zwischen "Not IN" und "Not Exists" gemessen? "In" und "Exists" ohne "not" sollte einigermaßen gleich ablaufen, weil normalerweise ein identischer oder zumindest sehr ähnlicher Ausführungsplan entsteht.

Falls du auch zw. "Not IN" und "Not Exists" bei Jet und einer einigermaßen großen (indexfähigen) Datenbasis (realistische Mengen von z. B. 1000-100000 DS - es müssen keine Millionen sein) keine Unterschiede feststellten konntest, solltest du vielleicht einmal deinen Test-Ablauf überdenken. *biggrin.gif*
Ein oft gemachter Fehler beim Testen: es wird nur die Zeit zum Öffnen eines unsortierten Recordset gemessen und nicht bis ans Ende des Recordset gewechselt. ... bei einem Table-Scan wirkt nämlich der nicht index-fähige Where-Teil oft erst beim Aufrufen des einzelnen RS.
(Das Problem mit Not In hat aber hauptsächlich Jet. Der MSSQL-Server erstellt z. B. durchaus einen ähnlichen Ablaufplan für "not in" und "not exists".)

mfg
Josef

 

Juup, habe ich. Genauso kein Unterschied. Zumindest bei einer Abfrage mit 1000 bei denen auf NOT IN/NOT EXISTS von 500 DS geprüft wurde. *wink.gif*

Das In-Kriterium war ein indexiertes Feld, allerdings befand sich im Sub-Select ein Where-Kriterium auf ein nicht indexiertes Feld ... waren halt Praxis-Tests und da zählt nur was ich als Ergebnis brauche. *wink.gif*

Da gibt es nix zu überdenken, weil alles sich auf praxis-relevante Abfragen bezog und die Messung ging vom Start der Abfrage bis zum Ende durch. Dabei wurden immer zwei Zeiten gemessen ... einmal bis zur endgültigen Öffnung des Recordsets und einmal bis hin zur Anzeige aller DS im entsprechenden Steuerelement. Also alles absolutes Praxistests ... nur das hat mich in allen Fällen interessiert: Wie lange dauert es vom Knopfdruck bis hin zur fertigen Anzeige ... mit der Zwischenzeit für die Öffnung des Recordsets.

Alle Recordsets sind ohne ORDER BY-Teil weil ich die Ergebnisse der Abfragen in ein DataGrid lade wo der User die Sortierreihenfolge dann selber bestimmt.

Wie gesagt, ich bezweifle nicht das es unter optimalen theoretischen Bedingungen Zeitvorteile geben kann ... bloss konnte ich in meinen Praxis-Tests eben keine messen.

Vermutlich weil überall Felder im WHERE-Teil beteiligt sind die nicht indiziert sind, aber man kann halt eben nicht jedes Feld in jeder Tabelle indizieren. ^^

Da vermutest Du falsch, der Zeitunterschied zwischen einem Left Join und einem NOT IN auf 1000 DS zu 500 DS ist so gut wie nicht vorhanden, erst bei einem Durchschnittsvergleich von 50 Durchläufen jeweils bekam ich einen Vorteil von 3 ms für das Left Join bei einer Durchschnittszeit von 63 zu 66 ms.

Gruß

Rainer

 

Da gibt es nix zu überdenken, weil alles sich auf praxis-relevante Abfragen bezog und die Messung ging vom Start der Abfrage bis zum Ende durch

Da gibt es meiner Ansicht nach sehr viel zu überdenken, weil du aus deinen Tests falsche pauschale Schlüsse ziehst - zumindest macht es für mich diesen Eindruck, wenn ich den Beitrag #5 lese.

Praxis-Tests sind ja durchaus sinnvoll. Die zeigen, dass es bei einer bestimmten Datenzusammensetzung und Abfragegestaltung keine Unterschiede gibt. Aber daraus abzuleiten, dass es generell keinen Unterschied zw. "not in" und "Not exists" bzw. der left-Join-Variante gibt, solange nicht mehrere 100k DS vorhanden sind, halte ich für fragwürdig.

Unter dieser Grundbedingung zu behaupten, es gibt keinen merkbaren Unterschied zw. "not in" und "not Exists" ist ganz einfach falsch. Da kann man dann genauso behaupten, dass es beim Filtern einer Abfrage keinen Unterschied macht, ob auf ein Feld ein Index existiert oder nicht, wenn man die Abfrageausführung nur mit einem Feld ohne Index testet. => Also sofort alle Indizes löschen, die helfen nach dieser Logik sowieso nicht. *Smilie

mfg
Josef

 

Ein Beispiel aus der Praxis, nicht konstruiert und nicht unter spezifischen Laborbedingungen: http://*******/dlE9Vd. Ich hatte mich oben um eine Zehnerpotenz vertan.

Praktische Erfahrungen sind dann manchmal ein Anlass, Nachdenkprozesse zu starten und die eigene Praxis an die Theorie anzunähern. Bei mir jedenfalls hatten entsprechende Schlüsselbeispiele von Josef ein nachhaltiges AHA ausgelöst.

 

\@ ebs

Da gebe ich Dir auch absolut Recht ohne Diskussion.

Das ist ja auch der Grund wieso ich alle Abfragen die ich erstellt habe/erstellen werde immer wieder durchteste welche Variante Vorteile bringt.

Einfach weil ich genau sehen will wo die theoretischen Überlegungen in der Praxis unter welchen Umständen welche Vorteile bringen ... und dazu gehört eben z.B. alle Tips von Michael Zimmermann zu berücksichtigen. *wink.gif*

Und Fakt ist eben das nicht jedes Theorem unter jeden Umstand einen Vorteil bringt.

Und Dein Link ist eben ein Beispiel wo es einen Vorteil bringt ... habe ja auch nie behauptet das es keinen Vorteil bringen kann. Daher eben der Hinweis auf zeitkritische Anwendung.

@ Josef

Die Frage von Ebs an mich war wieso ich NOT IN anstatt NOT EXIST einsetze/schreibe und da war die Antwort weil ich bis jetzt keinen Zeitvorteil dadurch erreichen konnte.

Und das ist kein pauschaler Schluß sondern eine faktisch richtige Aussage.

Ich habe ja nie behauptet das es keine Zeitvorteile gäbe - DAS wäre ein pauschaler Schluß - sondern eben nur das ich in der Praxis bis jetzt keinen feststellen konnte.

Aber allein nur das ich das jedesmal durchteste, sollte ja zeigen das ich mir die theoretischen Grundlagen jedes Mal wieder vor hole und eine Abfrage darauf hin durchteste und gucke welche Methode hier am schnellsten funktioniert.

Das habe ich ja auch nie so behauptet, sondern nur das es bei den Tests die ich gemacht habe (und das war bei jeder Abfrage in der ein NOT IN oder ein IN vorkommen sollte/musste) eben keinen gab und das ist einfach Fakt.

Genau nichts anderes behaupte ich ja. ^^

Und aus dieser Sicht ist es also ebenso nicht richtig pauschal zu sagen das NOT EXISTS immer einen Zeitvorteil gegenüber NOT IN hat. Mir kam eben bis jetzt kein Fall in der Praxis unter wo ein NOT EXISTS schneller gewesen wäre. *wink.gif*

Wie gesagt, ich stelle in keinster Weise die Theorie in Frage - würde mich auch gar nicht einfallen - aber merke nur an das in einigen Praxisfällen die Theorie eben Theorie bleibt und keine praxisrelevanten Auswirkungen hat.

Gruß

Rainer

 

Anders herum gefragt: Welche Schlussfolgerung ziehst Du daraus, dass ein anderer mit den gleichen Anweisungen einen Unterschied um Faktor 2500 feststellt (theoriekonform) und Du praktisch keinen?

Nebenbei(!) sollten wir NoNet nicht vergessen: Ist Deine Frage schon hinreichend beantwortet? Wenn nicht: Traust Du Dich noch, weiterzufragen?

 

\@Rainer: verstehe ich dich richtig: du verwendest absichtlich "Not IN (...)" wenn es in deinen Tests keinen Unterschied zu "Not Exists" bringt, obwohl du weißt, dass "Not Exists" bzw. die Left-Join-Variante im Regelfall schneller wäre, sobald ein Index genutzt werden könnte. (Beachte: es kommt auf die Index-Nutzung im Select von Exists an.)

Code:

vs.
Code:

Der Index auf "FeldMitIndex" sollte in beiden Fällen keine Rolle spielen. Die Exists-Variante wird aber schneller sein, wenn für xyz ein Index genutzt werden kann. Messbar wird das erst, wenn in der Tabelle etwas mehr Datensätze vorhanden sind und der Index Wirkung zeigen kann.

Wenn du dich damit auf keinen Zeitvorteil bei deinen Tests bzw. deinen Anwendungen und Tabellenkonstrukten beschränkst, ist das ok. Ich las deine Antwort aber so, dass du aufgrund deiner Tests das als allgemeingültige Aussage für die Praxis ableitest. Und genau das störte mich. *Smilie
Das kann vielleicht für deine Praxis gelten, es gilt aber z. B. definitiv nicht für meine (bereits vergangene) Jet-Praxis.

Als Falscheinschätzung betrachte ich immer noch die Behauptung, dass es bei dir keine Unterschiede zw. Not-in und Not-Exists gab, wenn du genau weißt, dass in diesen Abfragen ein Table-Scan durchgeführt wurde.
Damit hast du zwar festgestellt, dass es bei dieser Abfrage keinen Unterschied macht, ob du Not-In oder Not-Exists einsetzt. Der Test zeigt aber nicht, dass Not-In gleich schnell wie Not-Exists ist in deinen Abfragen ist, da du dessen Anteil an der Abfragezeit gar nicht richtig messen kannst, wenn noch andere Kriterien im Spiel sind, die zu einem Table-Scan führen bzw. die Index-Nutzung im Exists-Teil deaktivieren.

Und aus diesem Grund wählt man absichtlich die theoretisch langsamere Variante aus, wenn es bei den Tests keinen Unterschied gab? Ist eine interessante Logik, die ich allerdings nicht verstehe. *Smilie

Für die Entscheidung ob Not in oder Not Exists könnte man die obige "Behauptung" etwas anders betrachten:
Kannst du mir einen Fall zeigen, wo "Not Exists" langsamer als "Not in (select. .. )" ist.
Falls dir kein Beispiel einfällt, könnte man sich die Frage stellen, welchen Vorteil dann die "Not in"-Variante hat, wenn sie höchstens gleichschnell wie die "Not Exists"-Variante ist.
Falls es Beispiele gibt, wissen wir zumindest, dass man immer beide Varianten prüfen sollte, wenn bestimmte Regeln zutreffen.
(Mir fällt gerade kein Beispiel ein - das kann aber auch daran liegen, dass ich kaum noch Jet-SQL verwende und bei mir daher die Jet-Erfahrung nicht mehr besonders aktuell ist. Zumindest traue ich es Jet zu, dass es damit auch Fälle gibt, wo Not-In schneller als Not-Exists ist. *Smilie)

Mir auch nicht, weil ich in der Praxis MSSQL verwende und das das "Not in" optimieren kann. Allerdings schreibe ich aus Gewohnheit meist nur die Exists-Variante. *Smilie

mfg
Josef

 

Absolut richtig ... wenn ich weiss das ich die Abfrage eh nie auf reine Index-Nutzung umstellen kann und es keinen Zeitunterschied gibt wieso sollte ich es nicht? *wink.gif*

Genau das tue ich.

Genau das tue ich aber nicht. Sonst würde ich mir die Mühe ersparen jedes Mal jede Variante durchzutesten. *wink.gif*

Richtig, aber das ist für Praxis-Tests auch absolut irrelevant weil es mir hier nur darum geht zu sehen wie lange es dauert bis der Anwender das Ergebnis auf dem Bildschirm hat.

Das kann ich nicht ... zumindest ist mir das bei keinem meiner Tests bisher aufgefallen.

Sie hat manchmal einfach den Vorteil übersichtlicher (weil kürzer) zu sein, aber hauptsächlich ist sie in dynamisch erstellten SQL-Statements oftmals einfacher umzusetzen als die EXISTS Variante, die in manchen Fällen auch noch die dynamische Anpassung des Sub-Selects benötigt.

Das mach ich eh immer ... zumindest immer dann wenn mir die IN-Variante einen Vorteil im Sinne von wesentlich übersichtlicher oder einfacher in einem dynamischen SQL-Statement einzusetzen bringen würde.

Ah okay. ^^

Gruß

Rainer

 

Das ist dann eine spezielle Verknüpfung von Zuständen mit Null Bedeutung hinsichtlich allgemeiner Aussage an andere.
Dass man vorrangig ein Abfragedesign wählt, das eine Indexnutzung zulässt, ist hoffentlich unstrittig - zur Gewohnheitsbildung oder eben, weil es wegen der Datenmenge notwendig ist. Und große Datenmengen können schnell auch in Mini-Datenbanken auftreten, z.B. durch ein Kreuzprodukt. Wer kann da schon sicher beurteilen, ob er auf den möglichen Performancegewinn durch Indexnutzung verzichten kann oder nicht?

Umgekehrt wird man sich erst offensiv auf das Suchen und Verwenden von SQL-Lösungen statt von VBA-Lösungen einlassen, wenn man von deren Vorteilen überzeugt ist, und die mögliche Verarbeitungsgeschwindigkeit ist da sicher ein wesentlicher Maßstab.