Frage an die SQL-Experten: Performance bei View mit Group by

[hs]

Hallo,

habe eine große Tabelle (Lagerkonto) mit mehreren Millionen Datensätzen.

Es gibt einen Index auf FIRMA und ARTIKEL. Zusätzlich habe ich eine View angelegt:
SELECT FIRMA, ARTIKEL, max(DATUM) from LAGERKONTO GROUP BY FIRMA, ARTIKEL

Wenn ich diese in QRY verwende, indem ich eine weitere Tabelle über FIRMA und ARTIKEL verknüpfe, dauert der Zugriff auf die View sehr lange. Dies liegt daran, dass temporär eine Kopie der Tabelle erstellt wird.

Kann ich das irgendwie beschleunigen bzw. so gestalten, dass dies nicht passiert? Ein Index ist ja da und ich habe die gruppierte View angelegt?

Danke
HS

[Fuerchau]

Lege eine Index über FIRMA, ARTIKEL und Datum (Datum ggf. DESC) an.

[hs]

Hab ich gemacht:
CREATE INDEX TABIND1 ON TABELLE (FIRMA ASC,
ARTIKEL ASC, DATUM DESC)

Hat aber leider keinen Effekt ;-(

Und eine wichtige Info habe ich verschwiegen seh ich grad:

In der GROUP BY VIEW ist auch ein SELECT nach TRANSAKTION drin ;-(

(OS ist übrigens 7.1.)

[hs]

Hier nochmals der korrekte SQL der GROUP-BY VIEW:

SELECT FIRMA, ARTIKEL, max(DATUM) from LAGERKONTO WHERE TRANSAKTION in ('A', 'B') GROUP BY FIRMA, ARTIKEL

Indexe habe ich wie folgt:
CREATE INDEX TABIND1 ON LAGERKONTO (FIRMA ASC, ARTIKEL ASC, DATUM DESC)
CREATE INDEX TABIND2 ON LAGERKONTO (FIRMA ASC, ARTIKEL ASC, TRANSAKTION ASC)
CREATE INDEX TABIND3 ON LAGERKONTO (FIRMA ASC, ARTIKEL ASC, TRANSAKTION ASC, DATUM DESC)

Meldung mit entsprechener Performance kommt noch

[Fuerchau]

Die Frage ist wie immer, welche Verknüpfungsarien werden da getrieben.
Ggf. ist ein Index da nicht so hilfreich, da einfach zu viele Sätze sowieso verarbeitet werden müssen.

Mach mal aus dem Query mit der Verknüpfung auf die View eine neue View.

Hilfreich für Einzelwerte sind
a) Indizes
b) scalare Subselect's

zu b)

select blabla,
(select Datum from myfile b
where a.key = b.key
order by b.key, Datum desc
fetch first 1 rows only) as Datum
:
from myfile a

Das erspart den Group by über sehr viele Sätze und macht nur genau 1 Zugriff.
Ähnlich kann man auch mit anderen Werten umgehen.

[hs]

Hallo Fuerchau,

das mit der View anstelle der QRY geht leider nicht, da es sich bei der Tabelle, mit der verknüpft wird, um eine temporäre Ausgabetabelle handelt. Auch möchte ich die View in mehreren Abfragen verwenden, dann wäre jedesmal eine spezielle View erforderlich - was denke ich auch Performanceeinbußen zur Folge hätte (jedesmal dann, wenn ein Datensatz in das Lagerkonto eingefügt wird).

Teil B habe ich noch nicht verstanden. Kannst du den SQL mal für mein Beispiel schreiben?

Vielen Dank
HS

[Fuerchau]

Dein Problem ist die Where-Klausel in Verbindung mit dem Group By und die spätere Verknüpfung mit dieser.
where x in (....) ist das selbe wie where a=1 or x=2
Hier kann es helfen, einen Index mit der Transaktion als 1. Feld zu erstellen.
Also "Transaktion, Firma, Artikel, Datum desc".
Ich glaube aber nicht an den Optimizer, da Where und Groupby eigentlich 2 Indizes benötigt.

zu b)
Mit diesem "scalaren Subselect" wird genau 1 Satz über die Beziehung a.key = b.key ermittelt.
In deinem Fall hilft dies auch nicht da du ja eine zusätzliche Where-Klausel mit einer OR-Bedingung hast.

Ich fürchte, du musst mit dem Performancenachteil leben.

Alternativ würde ich dir einen Trigger empfehlen, der die benötigte Information in eine 2. Datei schreibt die dann nur noch Firma, Artikel, Datum enthält.

[andreaspr@aon.at]

Man müsste sich zunächst auch mal die Daten anschauen. Z.B.: wieviele Sätze gibt es mit TRANSAKTION in ('A', 'B').

Probier die Indice auch mit einer WHERE-Klausel:

CREATE INDEX TABINDx ON LAGERKONTO (FIRMA ASC) WHERE TRANSAKTION in ('A', 'B')

Du könntest auch probieren einen EVI anzulegen:
CREATE ENCODED VECTOR INDEX TABINDx ON LAGERKONTO (FIRMA ASC) WHERE TRANSAKTION in ('A', 'B')

lg Andreas

[BenderD]

das mit der View anstelle der QRY geht leider nicht, da es sich bei der Tabelle, mit der verknüpft wird, um eine temporäre Ausgabetabelle handelt.HS

... da liegt wohl der Hase im Pfeffer! Wieso wird denn da eine temp. Tabelle gebraucht?

[hs]

Möchte das im Detail nicht weiter ausführen, aber es ist eine komplexere Prozedur, in der mehrere Tabellen nacheinander abgefragt werden. Lässt sich nicht in einem QRY oder SQL erledigen.
Auch ist die temp. Tabelle nicht das Problem, Kopie wird immer dann erstellt wenn ich die VIEW verwende, d.h. mit irgendeiner anderen Tabelle verknüpfe.

[B.Hauser]

Das Problem liegt darin, dass Du Query verwendest!
Und Query ist NICHT SQL und wird bis einschließlich Release 7.1 von der alten Query Engine (CQE) verarbeitet.
Da helfen die ganzen neuen Indices mit und ohne WHERE-Bedingungen nichts.

Prüfe, ob das Query vielleicht durch QMQuery ersetzt werden kann. QMQuery basiert auf SQL und sollte von der neuen Query Engine (SQE) verarbeitet werden können und damit auch die neuen Indices nutzen können.

Birgitta

[hs]

Naja, grundsätzlich bringen Index schon auch Performance bei Queries. Nur halt nicht in diesem speziellen Fall.
Hab das gleiche mit einem SQL probiert, das geht sehr flott vonstatten, d.h. der Index wird sicher verwendet.
Denke ich werde bedarfsweise einige Queries mit SQL ersetzen (wird eh alles aus einer Prozedur aufgerufen).

Gruß und nochmals Danke für die Unterstützung
HS