SQLRPGLE: Abfrage als Tabelle

[ExAzubi]

Hallo zusammne,

es gibt in SQL sie Möglichkeit, eine Abfrage als temporäre Tabelle zu benutzen.

Ich weiß aber nicht mehr genau wie

Es war glaub ich so oder Ähnlich

use x as Select * from tabelle 

select count(*) from x

Wenn mir jemand nochmals auf die Sprünge helfen könnte....

Danke

[B.Hauser]

Das Zauberwort heißt Common Table Expressions oder Verschachtelte Sub-Selects.
Das Ganze geht auch im interaktiven SQL.

With x as (Select ...)
Select Count(*) From x

oder

Select Count(*)
From (Select .... From ...) x

Birgitta

[ExAzubi]

DANKE Birgitta,

das habe ich gesucht!!

Mir viel das Zauberwort CTE nicht mehr ein!

Vielen Dank nochmals.

Viele Grüße

David

[Fuerchau]

Zu bedenken ist nur, dass eine CTE keine wirkliche temporäre Tabelle ist.
Je nach Lust des Optimizers wird für Join, Subselects o.ä. eine *QUERYnnn verwendet oder eben auch nicht (meistens).
CTE vereinfacht nur die Schreibweise und die Wiederverwendung:

with cte1 as (select k1, f1 from tab1)
, cte2 as (select k2, sum(f2) f2 from tab2 Group by k2)
select * from cte1
inner join cte2 on k1=k2

ist identisch zu

select * from (select k1, f1 from tab1) cte1
inner join (select k2, sum(f2) from tab2) cte2 on k1=k2

Der Vorteil der CTE ist die übersichtlichere Schreibweise und eben die Wiederverwendung wenn ich eine Tabelle mehr als 1 Mal joine.
Zur Laufzeit wird dann aber auch der CTE-Ausdruck n Mal ausgeführt. Ggf. also auch n *QUERYnnn-Tabellen.

[Fuerchau]

Nicht aufregen, ich habe den "Group by k2" im 2. SQL vergessen!

[B.Hauser]

Und keine Panik wegen der angeblichen QUERYnnn-Tabellen.
Selbst wenn solche erstellt werden ist dies immer noch schneller als zu Fuß eine temporäre Tabelle zu erstellen und füllen und dann auch noch ohne entsprechende Indices darauf zuzugreifen.
IBM bzw. der Optimizer hat das zumindest mit der SQE ganz gut im Griff.
Und nach meiner Erfahrung sogar wesentlich besser als bei komplexen verschachtelten Sub-Selects.

Birgitta

[Fuerchau]

Da kann ich leider nicht mithalten.
Ich gehe auch mit der neuen SQE manchmal über echte temporäre Tabellen, da es der Optimizer leider nicht schafft trotz Befolgung sämtlicher Hinweise (Index anlegen) die Daten schnell zu liefern.
Es gibt ja genug Hinweise, dass der Optimizer vorgeschlagene Indizes nicht nimmt.
Dabei hat der Optimizer am Meisten Schwierigkeiten mit "Group by"-Ergebnissen die anschließend verjoint werden.
Durch die Erweiterungen "create table .. as select ... with data" kann ich das nun sogar in einem Rutsch erledigen was ich früher als "Create Table" und "insert ... select ..." bzw. als QMQRY mit Dateiausgabe gemacht hatte. Natürlich lege ich auch Indizes an. Die Laufzeiten lassen sich dann häufiger von mehreren Minuten/Stunden in wenige Sekunden verringern.
Ich habe auch schon versucht, Programme aus V5R2 auf die neuen Möglichkeiten von V6/V7 anzupassen. erreicht habe ich bzgl. der Performance da eher weniger.

[BenderD]

- CTEs haben mit temp Tabellen nichts gemeinsam, das ist nur eine alternative Schreibweise.
- wenn man denselben CTE öfter benötigt, dann sollte man das als View hinterlegen.
- für SQL gilt (eigentlich) das Kohl Prinzip: entscheidend ist, was hinten rauskommt. Wenn unterschiedliche Formulierungen derselben Ergebnismenge zu verschiedenen Laufzeiten führen, dann schwächelt der Optimizer.
- einigen gängigen Schwächeanfällen (wie das Query Engin heißt, ist mir grundzipiell Wurscht!!!) kann man mit Temp Tabellen entgegenwirken, weil man so Selektivität vorziehen kann (erst Exttrakte ziehen, dann joinen).

D*B

[ExAzubi]

Hallo,

ich habe die Abfrage 1x als CTE und 1x als CREATE TABLE ... with DATA gemacht.
Da mehrfach auf das ResultSet der Abfrage zugegriffen wird, habe ich merklich festgestellt, das die temp. Table wesentlich performanter ist.
Keine AHnung warum und ich habe auch groß keine Analysen gemacht, nur die Laufzeit war merklich kürzer mit der richtigen temp. Tabelle in der QTEMP....

[Fuerchau]

Wie schon beschrieben, CTE's sind alternative Schreibweisen.
Was macht SQL eigentlich?
Da alle nur mit Wasser kochen, i.W. nichts anderes als ein Programmierer auch.
Der Programmierer überlegt sich vorher seine Zugriffe (LF's) bevor er loslegt. Anschließend werden die Zugriffe von links nach rechts vorgenommen.
Dabei wird links 1 Satz gelesen und nach rechts die passenden Daten je Join ermittelt, wobei dieser Join über Index am Besten ist um die Zugriffe zu reduzieren.
Die Schwierigkeit besteht halt, die Beziehung und die Where-Klausel unter einen Hut zu bringen.
Der Optimizer kann halt nur versuchen, das beste herauszuholen.

CTE's verkürzen nur die Schreibweise aber sparen nicht die Zugriffe!

Was temporäre Tabellen in QTEMP angeht so kann man hier noch mal mit Indizes beschleunigen.
Das Schöne an SQL:
DROP TABLE löscht automatisch auch alle Indizes!

Und wer mit Transaktionen arbeitet:
Hier kann man kurzfristig mit "Set Transaction" die Transaktion aussetzen bzw. bei einigen Befehlen (Update/Insert/Delete) "WITH NC" für QTEMP-Tabellen anwenden.

[BenderD]

Wie schon beschrieben, CTE's sind alternative Schreibweisen.
Was macht SQL eigentlich?
Da alle nur mit Wasser kochen, i.W. nichts anderes als ein Programmierer auch.
Der Programmierer überlegt sich vorher seine Zugriffe (LF's) bevor er loslegt. Anschließend werden die Zugriffe von links nach rechts vorgenommen.
Dabei wird links 1 Satz gelesen und nach rechts die passenden Daten je Join ermittelt, wobei dieser Join über Index am Besten ist um die Zugriffe zu reduzieren.
Die Schwierigkeit besteht halt, die Beziehung und die Where-Klausel unter einen Hut zu bringen.
Der Optimizer kann halt nur versuchen, das beste herauszuholen.

CTE's verkürzen nur die Schreibweise aber sparen nicht die Zugriffe!

Was temporäre Tabellen in QTEMP angeht so kann man hier noch mal mit Indizes beschleunigen.
Das Schöne an SQL:
DROP TABLE löscht automatisch auch alle Indizes!

Und wer mit Transaktionen arbeitet:
Hier kann man kurzfristig mit "Set Transaction" die Transaktion aussetzen bzw. bei einigen Befehlen (Update/Insert/Delete) "WITH NC" für QTEMP-Tabellen anwenden.

... was Einfluss hat ist:
- ob ein Cursur read only ist
- scrollable oder nicht
Indexe auf Temp tables braucht man eigentlich kaum noch (wenn es einen braucht, macht es sich einen und cached den)
@commit: Da muss man beachten, dass set connection nur an einer Transaktionsgrenze geht und die with Klausel kein SQL Standard ist. Besser und einfacher ist da, die Temp Tabellen in einer eigenen ACTGRP (und damit eigenem SRVPGM) zu verarbeiten, dann kann man diese Connection mit Isolation level no commit verarbeiten (CRTSQLRPGI COMMIT(*NONE))

D*B