Dynamisches SQL mehrere where klauseln

[camouflage]

Baldur,
Danke für die Anmerkung. Doch warum soll ich eine oberschlau Schlagmichtotsuperlösung erarbeiten, wenn es einfacher auch geht. Da muss ich niemanden mehr etwas beweisen.

[Fuerchau]

Weil eine Schlagmichtotsuperlösung vielleicht einfach nur geil ist;-)?

Aber im Ernst, deshalb mache ich ja die pragmatische Lösung, die Schlagmichtotsuperlösung ist im Clientumfeld eher die Regel, da SQL's und Ergebnisse sowieso grundsätzlich dynamisch sind.
Frag mal die Javafraktion auf der IBM i, die kennt solche Probleme gar nicht.

[BenderD]

... irgendwie verstehe ich die Anforderung und eure Diskussion nicht. Unvollständige Selektionen gehen auch mit simplem statischem SQL mit Parametermarkern mit between oder auch mit like.
- Feld between lowval and highval => keine Einschränkung
- Feld between :wert and :wert => nur mit Feld = Wert
etwas aufgebohrt kann das auch größer, bzw kleiner

- Feld like '%' => keine Einschränkung
- Feld like :wert => nur mit Feld = Wert
etwas aufgebohrt kann das auch echtes like (alle Kunden, bei denen der Ort mit 'F' und der Name mit 'M' anfängt etc.).

Das brummt dann auch mit elementarem Index-Design.

D*B

[Fuerchau]

Der obere SQL ist doch statisch mit Hostvariablen. Parametermarker gibts nur bei dynamischem SQL, was u.U. in ILERPG halt schwierig ist.

[BenderD]

... schneller geschrieben als gedacht. Parametermarker braucht man da keine, man besetzt die Hostvariablen der nicht benötigten Selektionen mit loval/highval und die anderen jeweils mit dem Auswahlwert (like entsprechend).

D*B

[Fuerchau]

Ja das geht auch, ist allerdings performancenegativ.
Wenn ich alle Paramter mit Low/High und nur 1 auf einen bestimmten Wert kann SQL nicht über Index optimieren. Wenn ich einen Parameter mit Leer prüfe und nur den gewünschten Parameter fülle, kann SQL den passenden Index nehmen.

[B.Hauser]

Ja das geht auch, ist allerdings performancenegativ.
Wenn ich alle Paramter mit Low/High und nur 1 auf einen bestimmten Wert kann SQL nicht über Index optimieren. Wenn ich einen Parameter mit Leer prüfe und nur den gewünschten Parameter fülle, kann SQL den passenden Index nehmen.

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.

[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.

... 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.