Office: JOIN Ausdruck nicht unterstützt

Moin zusammen.

Ich brauche mal einen Denkanstoss:
Zu einer Artikeltabelle gibt es eine Tabelle mit historisierten Preisen.
Ich brauche eine Abfrage, die mir zu jedem Artikel den zur Zeit gültigen Preis ermittelt.

Die folgende Anweisung liefert das aber ist sicher suboptimal - bei großen Datenmengen ist die Performance schlecht.
Code:

Jetzt habe ich das mit einer gejointen SubSelect probiert ... bekomme aber den Fehler "JOIN Ausdruck nicht unterstützt".
Code:

Ich meine, mich zu erinnern, dass ich das in TSQL schon so gemacht habe.
Oder übersehe ich einen Fehler?
Oder hat jemand einen Vorschlag, wie das in JET SQL zu optimieren wäre?

/Edit:
Ich hab die Daten mal nach SQLServer portiert ... der JOIN funktioniert dort ohne Fehler.
Aber es ist ein logischer Fehler drin ... durch das OR bekomme ich auch den Satz mit NULL als Ergebnis.
Ist wohl besser, mit 'gültig_von' und 'gültig_bis' zu arbeiten ...

:)

 

Hallo,
also ein eine Join Anweisung funktioniert doch Tabelle1 Join Tabelle2.
Eine Select Anweisung liefert aber keine Tabelle als Ergebnis.
Kann nach meinem Verständnis also nicht funktionieren.

Wenn du nur den Preis suchst ist der SubSelect auch gar nicht nötig

SELECT TOP 1 p.preis
FROM tbl_artikelpreise p
WHERE p.art_id = " & art & " AND (p.gueltig_bis IS NULL OR p.gueltig_bis >= date() ORDER BY p.gueltig_bis DESC")

Ich verwende für solche Zwecke immer einen Gültigkeitsbereich mit VON und BIS, wobei ich beim aktuellen Preis immer ein Datum sehr weit in der Zukunft verwende, damit ich nicht mit Null suchen muss - was die Performance weiter verschlechtert.

LG Markus

 

Hallo Markus.
Danke für Deine Hinweise.

Selbstverständlich kann man ein SELECT Statement in ein JOIN Konstrukt einbinden.
Access kommt bloss nicht mit dem zweiten Teil der ON Klausel klar.
Wie ich im ersten Beitrag später hinzufügte: unter MSSQL geht das problemlos.

Und dann hatte ich geschrieben, dass ich die aktuellen Preise zu allen Artikeln als ResultSet brauche ... Deine Anweisung liefert lediglich einen Datensatz für einen Artikel.

 

Hallo H-C,

wie ist der aktuelle Preis definiert?
Mich irritiert, dass der aktuelle Preis ein gueltig_bis-Datum haben kann, ich würde hier NULL erwarten.
Es sei denn, es werden schon zukünftige Preisänderungen erfasst, was bei o.a. Abfrage jedoch falsche
Ergebnisse liefern würde.

Sind die Datumsfelder indiziert?

 

Hallo!

Bezüglich "Join Ausdruck nicht unterstützt": eventuell muss eine Klammer um den gesamten ON-Ausdruck.

mfg
Josef

 

Code:

Das ist ein einfacher Filter und damit Stoff für eine WHERE-Klausel und somit eher nicht als Kriterium für den JOIN.

Ich würde diesen Filter unmittelbar in die Unterabfrage übernehmen, weil Filtern weniger aufwändig ist wie joinen (einen vorhandenen Index darf man sicher unterstellen), und somit macht sich eine kleinere Ergebnistabelle besser beim JOIN.

Du musst zusätzlich noch aufpassen, da pro Artikel mehrere Preise auftreten können, z.B. wenn man Gültigkeiten/Preise auf Vorrat einträgt.

 

\@Marsu: wenn gültig_bis is null dann gilt der Preis bis in alle Ewigkeit. Ich habe aber jetzt entschieden, ein gültig_von Datum mit aufzunehmen ... das ist zwar redundant aber es macht die Abfrage einfacher.

@josef: die Klammer-Orgien, die Access da erwartet bzw. voraussetzt, sind eine Krankheit. Da die Konstruktion mit dem gejointen Subselect unter MSSQL problemlos funktioniert, werde ich wohl das BackEnd nicht mit Access sondern mit dem SQL Server realisieren.

@Eberhard: das ist allerdings noch mal ein Hinweis, den ich bedenken werde. Durch die Einführung des 'von' Felds sieht diese Klausel jetzt ja auch noch etwas anders aus.

Danke zusammen für die Hinweise.

 

Das ist in der Tat so.

In der oben gezeigten Unterabfrage müsste eigentlich pro Artikel auf einen Datensatz vereinzelt werden. Dafür bieten sich regelmäßig zwei Varianten an:
=> TOP_1-Gestaltung. Diese wird aber bei Ermittlung pro Artikel auch aufwändiger, auch in der Ausführung.
=> Ermittlung des MAX-Datums bezogen auf Stichtag (bei gueltig_bis = Null mit zusätzlich berechnetem Ersatzwert), und dafür dann die Ermittlung des Preises, sprich die Unterabfrage ist ihrerseits eine Abfrage mit mindestens einem JOIN.

In beiden Fällen werden die SQL-Anweisungen etwas komplexer (das kleinere und einmalige Problem), aber auch der interne Abfrageaufwand steigt - eingangs ging es ja um die Performanceproblematik (eine Unterabfrage im SELECT-Teil mit dann Ausführung pro Datensatz der Hauptabfrage).

 

Auf welche beziehst Du Dich jetzt? Es geschieht doch eine Verreinzelung ... einmal per TOP und dann durch den JOIN auf die Artikelnummer ...

In MSSQL habe ich es jetzt so gelöst: Code:

Ausführungsgeschwindigkeit - bei 2.000 Datensätzen - 45 ms

Die Alternative nach Deinem Vorschlag in #6 wäre: Code:

Ausführungsgeschwindigkeit 35 ms
Alles klar, etwas schneller ... die ursprüngliche in #1 a gepostete Abfrage mit TOP braucht auf dem SQL knapp 50 ms

Werde die Alternative nachher mal auf dem ACCESS FE testen.

 

Ich bezog mich auf Deine selbst vorgestellte (problembehaftete) Alternative in Beitrag #1.

Du musst da darauf vertrauen, dass es bei Datensatzerfassung genau ein gueltig_bis >= Date() oder nur gueltig_bis = NULL gibt, wie Du auch bei den allerletzten Varianten darauf vertrauen musst, dass immer nur ein Zeitraum das heutige Datum umschließt. In den Abfragen wird da nicht auf mehrere Angebote reagiert.
Selbstredend kann man bei der Datenerfassung unmittelbar umsetzen, dass es da immer nur ein möglicher Preis zu einem Datum geben kann, und insgesamt wird das auch einfacher umzusetzen sein.

Wenn es um Performance geht:
Code:

Eine Berechnung auf ein Tabellenfeld bricht die Indexnutzung in folgenden Operationen (=> Vergleich für Filter). Dort wird das sachlich unbedingt richtige gueltig_bis = NULL für ein unbekanntes Ende zu einem Problemfall. Würde man bereits in der Tabelle statt NULL einen Phantasiewert wie 31.12.2222 verwenden, den man inhaltlich als offenes Ende erkennt und behandelt, könnte man hier abfragetechnisch noch etwas Gas geben (zumindest theoretisch).

 

Ich habe jetzt den Code aus #9 b mit einem Access BE probiert und bin durchaus zufrieden mit den Antwortzeiten.

Danke, Eberhard, für den Hinweis mit dem Fantasie-Wert in der Zukunft.
Hab ich auch drüber sinniert. Der Nachteil ist der Aufwand, den ich treiben muss, um den Wert in der GUI als 'Ende offen' darzustellen.

Dank an alle für die hilfreichen Tipps.

 

Hallo zusammen,

würde in dem Zusammenhang gerne noch mal nachfragen wollen:
Hans, warum benutzt du (ursprünglich nur) gueltig_bis-Preise?

Meiner Logik nach, wird ein Artikel eingeführt, dieser hat ab dem Einführungsdatum einen gültig_AB-Preis.
Ändert sich der Preis, gibt es einen neuen Datensatz mit Art_FK, Preis und gültig_ab.
Somit ist der zuletzt angegebene Preis, der zukünftig gültige, bis der Artikel komplett aus dem Programm genommen wird.
Die Verwendung von gültig_ab macht auch das NULL-Problem in der Join-Abfrage und GUI obsolet.

Neben den schon genannten Nachteilen:
Wird bei deinem Konstrukt ein Preis für einen Artikel mit gültig_bis z.B. auf den 03.03.2015 gesetzt,
und wird dieser nicht nachgepflegt, siehst du den Artikel durch den InnerJoin am heutigen Tag gar nicht mehr.
(Lösung wäre ein LeftJoin)

Daher würde mich interessieren, warum du dich (bewußt?) für den gueltig_BIS-Preis entschieden hast.

 

Hi Marsu.

Vielleicht liegt es daran, dass ich persönlich mehr in die Zukunft als in die Vergangenheit blicke?

Aber: ich hab mich doch mittlerweile für gültig_ab und gültig_bis entschieden ...
*wink.gif*

Wenn ich drüber nachdenke ... hast Du freilich Recht.
Mit einem gültig_ab würde man auskommen.

 

Hallo!

Ein Gültig_ab und ein gültig_bis sind meist schneller in der Datenauswertung.
Bezüglich Redundanz: man kann dem User nur das "gültig ab" eingeben lassen und den Rest per Trigger nachtragen. Dann sind zwar die Daten im Prinzip redundant gespeichert, aber systemtechnisch erzeugt und es sollte somit zu keinem Datenmüll kommen können.

mfg
Josef

 

Das denke ich auch, kann es leider nicht testen, da der ShowplanCapturer nicht mit Unterabfragen umgehen kann.

Wenn einer von euch Lust hat, kann er mal folgende Abfragevorlage testen,
die 'gefühlt' sehr schnell ist:
Code: