Office: (Office 2003) Problem mit einer Suchfunktion

[etwas OT, aber vielleicht machen trotzdem ein paar mit. *wink.gif*]

SQL-Aufgabenstellung zum Nachdenken über Performance

Ich erlaube mir mir hier einmal ein Beispiel (Problemstellung) zu bringen, das nicht direkt mit der Ausgangsfrage zusammenhängt, aber meiner Meinung nach das Prinzip beinhaltet, das in diesem Thread vorgeschlagen wurde.

Ausgangslage:
Tabelle tPersonen
- idPers (PK)
- Nachname
- Vorname
- ...

Tabelle tPersonen_Sprachen
(= die gesprochenen Sprachen der jeweiligen Person)
- fiPers
- fiSprache
(PK = fiPers + fiSprache)

Tabelle tSprachen
- idSprache (PK)
- Sprache (Beschreibender Text)

Fragestellung:
zeige jene Personen an, die die 3 Sprachen D, E und F sprechen. (UND-Bedingung ... alle 3 Sprachen müssen gesprochen werden)

Es gibt meiner Meinung mehrere Lösungsvarianten.
Doch welche ist die sinnvollste bezogen auf die Ausführung der SQL-Anweisung?

mfg
Josef

 

Lösung 1: Liefert BBB und CCC
Code:

 

Hallo,

Das ist keine 1:n beziehung, sondern eine n:m mit einer weiteren Tabelle. Und zwar genau deswegen:

Es gibt eine Tabelle für die Mitarbeiter, eine Tabelle für die Fähigkeiten und eine Tabelle für die Kombination daraus. In der letzten Tabelle wird nur die ID der Mitarbeiter die ID der Fähigkeit und weitere Infos zu dieser Kombination gespeichert z.B. wann gelernt, wo gelernt usw. In dem Sprachenbeispiel oben von Josef ist genau dieses Prinzip angewendet und wenn seine Hausaufgabe gelöst ist, weis Du (Joghurt2) wie Du alle Deine Wünsche erfüllen kannst. Eine einmal mit der Zwischentabelle (Kombinationen) erstelle Abfrage ist immer "dynamisch", denn die stimmt immer unabhängig von der Anzahl der Mitarbeiter und der Anzahl der Fähigkeiten (Gesamt und pro Mitarbeiter). Die muss nie angepasst werden.

 

\@ Klaus: Hinweis ist angekommen. Josef hat es mir mit seiner Aufgabe auch deutlich auf´s Brot geschmiert, und ich will auch mitüben.

 

2 weitere Ansätze probierte ich auch noch aus:
1. 3x EXISTS IN
2. 3x INNER JOIN

die Ausführungspläne (Jet-Showplan) sind sehr interessant zu lesen. *wink.gif*

Beispiel für die Join-Variante:
Code:

Die Variante mit Exists
Code:

Bei der Join-Variante irritiert mich etwas:
Code:

warum "test expression"? ... es hätte doch der PK für seek verwendet werden können.
ich hätte so etwas erwartet:
Code:

stattdessen verwendet Access den Index, der über die referentielle Integrität angelegt wurde.

 

Wenn man etwas mehr DS für den Test verwendet, dann gefällt mir die Indexnutzung besser. *wink.gif*

Der Ablaufplan der Join-Variante mit ~30000 DS in der Tabelle tPersonen_Sprachen:
Code:

Im Anhang die erweiterte mdb mit einer Prozedur zum Erstellen von Testdaten und einer Mini-Test-Prozedur für den Vergleich der Abfragen.

Ein paar Test-resultate zum Vergleichen: (100.000 Personendatensätze, 300.128 Personen_Sprachen-DS)
(Werte entsprechen der Zeit in ms, die der Aufruf inkl. durchlaufen aller Treffer benötigte.)

Abfrage1_GroupBy: 2673

Abfrage2_Exists: 1132

Abfrage3a_Join_Where: 1687
Abfrage3a_Join_Where2: 1077
(die beiden Join-Varianten unterscheiden sich nur durch die Join-Reihenfolge!)

 

Wieder etwas zurück zur ursprünglichen Fragestellung: Wie kann man nun dieses SQL-Anweisungen in einem Formular verwenden. (Unter der Voraussetzung, dass die Anzahl der abgefragten Fähigkeiten variabel sein soll)

Wenn die SQL-Anweisung dynamisch zusammengesetzt werden soll, dann würde ich die Exists-Variante wählen, da damit nur der Where-Teile gestaltet werden muss.

Wenn die SQL-Anweisung starr sein soll und nur Kriterien entgegennehmen darf, dann würde ich die GroupBy-Variante verwenden, obwohl dies eindeutig die langsamste Variante ist.
Statt den Parametern könnte man sich in diesem Fall überlegen, ob man die gewünschten Fähigkeiten in eine Temp-Tabelle schreibt und in der "starren" SQL-Anweisung diese Temp-Tabelle zum Filtern verwendet.
Prinzip:
Code:

diese Variante ist dann noch langsamer, dafür aber unabhängig von den Fähigkeiten und dem Form-Code. *wink.gif*

 

Hallo zusammen,

hier gehts ja richtig rund :-)

Die "Hausaufgabe" trifft die Sache auf den Punkt. Genau so sollte das aussehen. Allerdings bleibt ja immer noch die Frage ungeklärt, wie ich die Suchkriterien in ein Formular einbaue. Hier muss ich ja jetzt eine Grenze an Suchkriterien angeben oder? Die Mitarbeiter können unendlich viele Fähigkeiten haben, allerdings muss die Anzahl der Suchkriterien konstant sein, da die dynamische Erstelllung von Kombinationsfeldern schwierig werden sollte.
Andererseits: In der Beispiel-Datenbank auf der ersten Seite des Threads ist eine Mehrfachauswahl in einem Listenelement mit STRG möglich. Das wäre natürlich auch fein.

Zur SQL-Abfrage:
Ein paar Fragen (fett markiert)

Code:

Ich erkenne darin noch keine Dynamik *frown.gif*

Danke im Voraus für alle Antworten

 

Könnte jemand vielleicht in die Beispiel-DB ein Formular einbauen wo obiger Code eingesetzt wird?

 

Im Anhang findest du 2 Varianten (Exists und Group by).
... ist aber nur schnell zusammengeklopft, daher bitte mögliche Fehler verzeihen. *wink.gif*

zu deinen Fragen:

In diesem Fall ja, obwohl mir "=" besser gefällt. *wink.gif*

Having ist das "Where nach der Gruppierung". Mit dem Count>2 werden nur jene angezeigt, bei denen auch alle 3 Sprachen einen Treffer liefern. (Beachte das OR beim Sprachen-Filter)

 

Hallo,

danke schonmal.
Es haben sich aber noch relativ viele Fragen aufgeworfen.

Habe gerade versucht in der Beispiel-DB durchzublicken.
Also von vorne: (Fragen im Code ergänzt)

Code:

Alles in allem ist mir nicht ganz klar, wo hier die Filterungen stattfinden.

Auch haben sich Fragen zu den Abfragen ergeben.
1. Ist die Group by oder die Exists-Variante die einfachere?
2. Brauche ich alle bzw 6 Abfragen von den 7?
-->Natürlich nur Exists oder Group By
-->Nur eine Join
-->Der Rest wird gebraucht. vPersonen nur um das Endlosformular zu füllen
==> Korrekt so?

Was machen die einzelnen Filter genau? Blicke da irgendwie nicht ganz durch...
3. Wo finden diese Abfragen alle ihren Einsatz?

4. Ausgehend davon, dass Abfrage1_GroupBy die Filterung erledigt hätte ich einige Fragen:
(ich blicke das irgendwie gar nicht, möglicherweise wegen verwendeten Abkürzungen)
Code:

Danke im Voraus für eure Antworten (bzw deine Antwort lieber Josef)

 

Noch was.
Du schreibst:
Code:

Ich verstehe nicht genau wie das gemeint ist.
Liefern nicht beide Varianten die selben Ergebnisse? Die Abfrage sollte schon dynamisch sein. Es kommen immer mehr Sprachen hinzu und diese sollten auch als Suchkriterium möglich sein. Muss dann die Exists-Variante gewählt werden?

 

Fragen aus #26:

1. Ja. Verwendete Befehle/Funktionen können/sollen nachgeschlagen werden (Onlinehilfe). Im Fall der konkreten Anwendung bei einem Problem, mit dem man sich auseinandersetzt, ist der Lerneffekt am höchsten.

2. Index beginnt bei 0, daher Anzahl-1

3.+4. In ein Array (aSprachen) werden die ID´s der ausgewählten Sprachen eingelesen zwecks weiterer Verwendung.

5. nicht ganz, es wird ein Filter erstellt (noch nicht angewendet)

6. Die Filter werden auf das Unterformular angewendet.

 

Zu 1: Danke. Du hast recht
Zu 2: Danke
Zu 3, 4: Array, alles klar. Da dämmerts. Schon mal gehört *wink.gif*
Zu 5: ok
Zu 6: Code:

strPersFilter ist der Nachname-Filter oder?
Wie wird im weiteren hier die Liste der Sprachen im Array mit der Liste verglichen?

Wichtig wären mir noch die Antworten zu den Abfragen. Blicke da wenig bis gar nichts *frown.gif*

 

Die Methode SetFilter ist mir bisher noch nicht untergekommen, habe sie auch in der Hilfe noch nicht gesehen.

Da hilft nur der Glauben an Josef, und funktionieren tut es ja.
Überhaupt ist es lohnend, sich das Beispiel in ein Lehrbuch zu legen. Josef verwendet z.B. auch SQL-Befehle (EXISTS, IN), die in der schwachen SQL-Hilfe in Access nicht vorkommen.

Fragenkomplex 2:

1. Einfach ist relativ. Kannst Du die Abfragen einer geänderten Situation anpassen oder aus dem Kopf neu schreiben?

2.+3. Von den 6 immer nur eine. Wie Du bemerkt hast: Es sind Varianten, die das gleiche Ergebnis bringen.

4. keine Frage

5. tPersonen AS P -> P ist Alias für Tabellenname. Er dient der Verkürzung des SQL-Ausdruckes. Außerdem lassen sich damit einige zusätzliche Effekte erzielen (Mehrfachzugriff auf gleiche Tabelle). -> Lehrbuch!

6.+8. War Josefs Aufgabenstellung (#16)

7. siehe Gruppieren