With Data as (Select.....)

[martin.kerkhoff]

Hallo,
ich wollte immer schon mal der erste in eine Forum sein ....

Folgende Frage:

ich habe ein Statement nach folgendem Aufbau.

"with Data as ( Select T1.REFID ,Count(*) as REC_ANZAHL from Table1 T1 inner Join Table2 T2 On t1.recid = t2.REFID)
Select t3.RECID, t3.Description, D.REC_ANZAHL
from Table3 T3 left Join Data D on t3.RECID = d.REFID"

Nun das Problem:
Wenn ich das Statement von Data einzeln ausführe bekomme ich ca. 500 Datensätze mit akzeptabler Antwort und prepare- Time
Führe ich das zusammengesetzte Statment aus erhalte ich laut Auswertung von IBExpert eine Ergebnissmenge nach ca. 1,2 Millionen Read-Vorgängen in den von DATA betroffenen Tabellen T1 und T3!! mit Antwort-Zeiten die nicht wirklich gut sind.

Nach meine (Un-)Wissensstand ist das With Statement quasi wie eine Temp-Table zu verstehen.
Will sagen: Das Select für Data wird einmal ausgeführt und mit den folgenden Statments gejoint.

Das o.g. Bespiel kann mann durch aufeinanderfolgende Joins natürlich abbilden.

Was ist jedoch mit Views und Selectable Stored Procedures? ( deren Ergebnismengen haben m.E. keine Indizes und müssten dann über komplette Tabellenscans gejoint werden.

z.B.:

"with Data as ( Select P1.REFID ,P1.REC_ANZAHL From PRC_SELECT_DATA_FROM T1_T3)
Select t3.RECID, t3.Description, d.REC_ANZAHL
from Table3 T3 left Join Data D on t3.RECID = d.REFID"

nutzt man temporäre Tabellen, so stellt sich sofort das erwartete Verhalten ein.
Das ist dann natürlich immer 2-schrittig (Sql1 in TMP-Tabelle einfügen / Sql-Statement mit Join ausführen) und führt dann auch noch zu vielen temporären Tabellen

hat da schon jemand tiefere Erkenntnis zu dieser Aufgabenstellung/ Serververhalten?
( ich weis ist sehr theoretisch, aber vielleicht ist ja schon jemand über so ein Problem gestolpert )

Mit freundlichen Grüßen

Martin Kerkhoff
decon-it

[martin.koeditz]

Hallo Martin,

grundsätzlich stimmt deine Aussage. Die With-Klausel wird als temporäre Instanz behandelt und nur einmalig ausgeführt. Das geschilderte Verhalten ist dementsprechend (zumindest zunächst) etwas merkwürdig. Ich schaue mir das nachher mal näher an.

Gruß
Martin

[martin.koeditz]

Hallo Martin,

ich habe nochmals etwas tiefer recherchiert.

Kannst du die Create-Anweisung der drei Tabellen mit den wichtigsten Feldern und Constraints angeben? Dann kann ich das mal eingehender untersuchen.

Weitere Infos findest du in der Dokumentation (teils etwas holprig, da muss ich nochmal ran ).
https://www.firebirdsql.org/file/docume ... tbl-cte-de

Gruß
Martin

[martin.kerkhoff]

Hallo Martin,

Vielen Dank für die Antwort,

ich werden mal am Wochenende versuchen einen DDL- Auszug zu erstellen.

Vielleicht kann mann da ja das Problem nachstellen.

[vr2]

Hallo Martin,

das with-statement funktioniert *nicht* wie eine temp-Table, es erzeugt keine temp tables, auch nichts konzeptionell ähnliches. Es ist nur eine syntaktische Hilfe für komplexe Abfragen. In Deinem Fall werden Berechnungen wiederholt ausgeführt. Das statement selber wird nur einmal ausgeführt, ja, aber die inneren uU mehrfach. Was willst Du denn erreichen?

Wenn Du SPs selektierst, werden Indizes benutzt, wenn die von der SP genutzen Tabellen welche haben.

[martin.kerkhoff]

Guten Morgen,

vielen Dank für die Antwort. Das scheint dann das "Problem" zu sein.

Die Idee war mittel des "With" statements eine kleineres Subset der Daten zu erhalten welches die Anzahl der Fetches für die nachfolgenden Joins verringert. Das ist insbesondere dann interessant wenn man für bestimmte Fälle keinen Index parat hat und das dann logischerweise zu einem Tablescan (in diesem Falle dann über die komplette Tabellenkombination des "With"-statements) führt.

[bfuerchau]

Eine With-Konstrukt ist nur die vereinfachte SQL-Syntax an Stelle von Derived Tables:

with
Data as (
Select P1.REFID ,P1.REC_ANZAHL
From PRC_SELECT_DATA_FROM T1_T3)

Select t3.RECID, t3.Description, d.REC_ANZAHL
from Table3 T3
left Join Data D on t3.RECID = d.REFID

entspricht:

Select t3.RECID, t3.Description, d.REC_ANZAHL
from Table3 T3
left Join
( Select P1.REFID ,P1.REC_ANZAHL
From PRC_SELECT_DATA_FROM T1_T3)
D on t3.RECID = d.REFID

und wird dann auch so ausgeführt. Eine Optimierung im Sinne von tatsächlichen Tabellen gibt es so nicht.
With-Konstrukte dienen nur der Vereinfachung für uns Programmierer.
Man kann z.B. eine With-Tabelle später mehrfach wieder verwenden. Aber der SQL löst jede Wiederverwendung als eigene Abfrage wieder auf.

Und ja, ohne Index wird daraus dann durchaus ein Tablescan der Jointabelle und zwar je Satz der linken Tabelle.

Möchte man tatsächlich temporäre Tabellen, so kann man sich per Prozedur da eher was zusammenbauen und sog. Global temporary Tables verwenden.
Diese Tabellen sind Connectionspezifisch und sind nicht Bestandteil der Datenbank!
Man kann also einen "Insert into TempTable select ...from ." mit anschießendem "Select .... from Table left join TempTable on ..." verwenden.
Damit erklimmt man dann tatsächlichungeahnte Performancehöhen.