Dynamisches SQL mehrere where klauseln

[BenderD]

Und warum denn nicht?
Solange die Original-Spalte auf der linken Seite des Vergleichsoperators (BETWEEN) nicht verändert wird (z.B. durch eine Scalare Funktion) KANN der Optimizer einen regulären Binary Radix Tree Index verwenden.
Ob er es denn tut, hängt mit anderen Faktoren zusammen.

... und selbst wenn auf der linken Seite des Vergleichsoperators die Spalte verändert wird, kann der Optimizer sogar einen entsprechenden Derived Index verwenden.

Kleiner Tipp: bevor man irgendwelche Behauptungen in den Raum stellt, sollte man das Ganze doch mal ausprobieren.

Anhang 631

... das Schnittmuster (Bild oben) interessiert mich wenig. Entscheidend ist, wie die Selektivität der einzelnen Bestandteile der where Klausel ist, wenn sich der Optimizer da verschätzt, dann geht das eh' in die Grütze. Mein Vorschlag ist gut lesbar (man sieht sofort, was gemeint ist) und praktisch erprobt, man sollte natürlich die Indexe mit den zu erwartenden where Klauseln aufeinander abstimmen, sprich: die mit der hohen Selektivität (z. B. Firma o.ä.9 nach vorne nehmen und diei Exoten (Telefonnummer o.ä) nach hinten.

D*B

[K_Tippi]

Hallo,
nicht die schnellste aber bei uns so üblich wir bauen den ganzen String zusammen:
tmp.sql = 'select * from QSAUFPF where aqidaq > 0';
// Status
if statvon_sl <> *Blank ;
tmp.sql += ' and statAQ >= ' + '''' + statvon_sl + '''';
endif;
if statbis_sl <> *Blank ;
tmp.sql += ' and statAQ <= ' + '''' + statbis_sl + '''';
endif;
// Prüfstatus
if pergvon_sl <> *Blank ;
tmp.sql += ' and pergAQ >= ' + '''' + pergvon_sl + '''';
endif;
if pergbis_sl <> *Blank ;
tmp.sql += ' and pergAQ <= ' + '''' + pergbis_sl + '''';
endif;

...usw..

Dann prepare jnd Cursor.

je nach Dateigröße auch mal ohne weitere Indexe. Wobei bei uns immer das erste Feld der Primärindex ist. (Fortlaufende Nummer)

[B.Hauser]

Wenn jeder seinen Senf dazugeben kann ... schlage ich noch eine Lösung mit statischem SQL vor (ebenfalls in hunderten Programmen eingesetzt)

Select ...
  from ...
  Where     Column1 = Case When :Host1 > '' Then :Host1 Else Column1 End
        and Column2 = Case When :Host2 <> 0 Then :Host1 Else Column2 End
        and Column3 = Case When :Host3 < '2000-01-01' Then :Host3 Else Column3 End
        ... and so on
  ...

... und der Optimizer kann durchaus Indices verwenden (auch zusammengesetzte Indices) und je nach Auswahl sogar unterschiedliche Indices (und manchmal sogar mehrere Indices gleichzeitig - Index Merge!)

[mk]

Hallo,
nicht die schnellste aber bei uns so üblich wir bauen den ganzen String zusammen:
tmp.sql = 'select * from QSAUFPF where aqidaq > 0';
// Status
if statvon_sl <> *Blank ;
tmp.sql += ' and statAQ >= ' + '''' + statvon_sl + '''';
endif;
if statbis_sl <> *Blank ;
tmp.sql += ' and statAQ <= ' + '''' + statbis_sl + '''';
endif;
// Prüfstatus
if pergvon_sl <> *Blank ;
tmp.sql += ' and pergAQ >= ' + '''' + pergvon_sl + '''';
endif;
if pergbis_sl <> *Blank ;
tmp.sql += ' and pergAQ <= ' + '''' + pergbis_sl + '''';
endif;

...usw..

Dann prepare jnd Cursor.

je nach Dateigröße auch mal ohne weitere Indexe. Wobei bei uns immer das erste Feld der Primärindex ist. (Fortlaufende Nummer)

Hi,

also so etwas kann man machen. Würde ich aber nie posten. Solche SQL in Strings werden häufig nicht von den Change Management Systemen erkannt und sind bei großen DB Änderungen permanente Zeitbomben. Und dann gibt es noch Programmierer die den Sqlstate oder Sqlcode nicht abfragen und nach einiger Zeit fragt man sich warum die Daten auf den Systemen nicht mehr korrekt sind :-(

Bitte jetzt keine grosse Diskussion daraus machen.
Gruß
Michael

[Fuerchau]

Da kann ich Dieter nur zustimmen.
Die Reihenfolge der Whereklausel ist bei Vergleichen ungleich "=" von entscheidender Bedeutung:

where F1 between :#V1 and :#V2
and F2 between :#V3 and :#V4

Wenn V1 = Minval und V2 = Maxval wird ein Tablescan erzwungen.

Und was das obige Zusammenstricken angeht so wird da nicht auf enthaltene Hochkommata innherhalb von Strings eingegangen.
In der Web-Community wird diesbezüglich auch auf SQL-Injection hingewiesen, wenn Eingabefelder vom Frontend kommen.
Du brauchst z.B. in StatVon-Variable nur eine Eingabe
=> ' or 1=1 or '
Und schon erhältst du alle Daten für die du u.U. nicht berechtigt bist.
Hostvariablen (Parametermarker) schützen vor SQL-Injection.

[camouflage]

Baldur,
die Spritze hat noch gefehlt. Da habe ich gar nicht darauf hinweisen wollen, reicht, dass ich sie mir geben liess. Für meinen Teil habe ich ja die Lösung und bekanntlich gibt es viele Wege nach Rom - jeder mit seiner Berechtigung. Also, alles gut - Thread schliessen.

[B.Hauser]

Da kann ich Dieter nur zustimmen.
Die Reihenfolge der Whereklausel ist bei Vergleichen ungleich "=" von entscheidender Bedeutung:

where F1 between :#V1 and :#V2
and F2 between :#V3 and :#V4

Wenn V1 = Minval und V2 = Maxval wird ein Tablescan erzwungen.

Das war einmal! Zu CQE-Zeiten.
Heute wird das SQL-Statement analysiert und in Verbindung mit dem Statistics Manager, der Informationen darüber liefert wie viele verschiedene Schlüssel und Werte ein bestimmter Index liefert und wie schnell man mit diesem Index an die Daten kommt (bzw. wieviel das "kostet").
Die Indices werden vorselektiert, d.h. es wird zunächst geprüft, ob sich die Join-Felder, die Felder, die in den WHERE-Bedingungen auf =, IN und BETWEEN geprüft werden als Schlüssel-Felder in Indices hinterlegt sind. Dann werden die verschiedenen Indices bewertet und sobald ein "teurer" Index gefunden wird, wird der "günstigste" Index verwendet und die weitere Prüfung abgebrochen.
Ein (Binary Radix Tree) Index wird i.Ü. nur verwendet wenn weniger als 15-20% der Daten selektiert wurden.
Zwischen 15-20 und 70-80% kann der Optimizer auch noch einen Encoded Vector Index (EVI) verwenden, ansonsten wird die ganze Tabelle verarbeitet, (wobei auch nicht mehr Table Scans verwendet werden, sondern meist über RRN- und Values Listen gearbeitet wird.

[Fuerchau]

De Technik dahinter ist mir letztlich egal.
Mein Erlebnis ist einfach (auch noch mit V7R4, dass solche Abfragen einfach langsam sind.
Bei nur ein paar 1000 Zeilen braucht man keine Indizes, da ist die Kiste sowieso schnell.
Bei ein paar Millionen Zeilen wird es schon mal kritisch.

Außerdem benötigen meine Queries in der Regel mehr als 1 Tabelle (auch schon mal bis zu 40), da gelten dann noch weitere Regeln. Ein Index alleine hilft da eher wenig.
Und ich habe schon genug Optimizervorschläge bekommen und ausprobiert ohne dass diese Indizes dann auch genommen wurden.
Auch so seltsame Vorschlage, statt dem Index "Firma, Werk, Teil" einen Index "Firma, Teil, Werk" zu nehmen entzieht sich mir da im Anschluss ja trotzdem der 1. Index verwendet wird.

Und ist ein RRN-Zugriff nichts anderes als TableScan?

Es steht außer Frage, dass die IBM i einen inzwischen besseren Optimizer hat. Meine Erfahrung ist, dass ich mit selbst erstellten Indizes sehr häufig bessere Laufzeiten erziele, weil der Optimizer diese dann verwendet. Da ist dann auch schon mal ein Index-Only-Zugriff dabei.