Performance, query schneller als sql?

[Robi]

Hi *all,

nochmal ein Performance Problem

in einer Datei mit 44 Mio Datensätzen sollen die angezeigt werden, die in einem Textfeld
mit einer bestimmten (variablen) Zeichenfolge beginnen.

Select a, b, c from Datei where upper(Text) like 'DR 50%' oder, nach Baldurs Version,
Select a, b, c from Datei where upper(Text) between 'DR 50 ' and DR 50 999'

Beides findet im interaktiven SQL kein Ende (nach > 30 minuten abgebrochen)
Im Query habe ich SOFORT die Anzeige
Einziger Unterschied:
Im query habe ich (kann ich) kein UPPER

Aber auch wenn ich im SQL das upper weglasse, bekomme ich kein Ergebnis
Einen index auf upper(Text) habe ich angelegt.

Die Kiste kann schell.
Wie kann ich sie überreden das auch mal für mich zu machen ?

Robi

[BenderD]

Hi *all,

nochmal ein Performance Problem

in einer Datei mit 44 Mio Datensätzen sollen die angezeigt werden, die in einem Textfeld
mit einer bestimmten (variablen) Zeichenfolge beginnen.

Select a, b, c from Datei where upper(Text) like 'DR 50%' oder, nach Baldurs Version,
Select a, b, c from Datei where upper(Text) between 'DR 50 ' and DR 50 999'

Beides findet im interaktiven SQL kein Ende (nach > 30 minuten abgebrochen)
Im Query habe ich SOFORT die Anzeige
Einziger Unterschied:
Im query habe ich (kann ich) kein UPPER

Aber auch wenn ich im SQL das upper weglasse, bekomme ich kein Ergebnis
Einen index auf upper(Text) habe ich angelegt.

Die Kiste kann schell.
Wie kann ich sie überreden das auch mal für mich zu machen ?

Robi

... das ist ja selbst für einen full table scan und Vergleich auf ein Monsterfeld zu langsam. Da kann ich mir nur ein defekt Problem vorstellen und sei es eine klemmende Eingabetaste...

D*B

[andreaspr@aon.at]

Hallo,
das kann leider immer viele Ursachen haben.
Ich hatte sogar mal ein Problem mit unterschiedlichen CCSIDs wo vorher noch überall eine Convertierung stattfinden musste befor da irgendwas verglichen werden konnte.

Details dazu findet man im Zugriffsplan den man sich anschauen muss.
Ohne den ist alles andere leider nur Glaskugellesen.

lg Andreas

[B.Hauser]

Robi,

versuch mal folgendes:
Generiere eine View mit allen Spalten der physischen Datei und einer zusätzlichen Spalte mit dem gleichen Namen wie das Schlüssel-Feld im Index und mit die 100% gleichen Syntax upper(Text) wie in der Index-Definition, also gleiche Groß/Kleinschreibung keine zusätzlichen Blanks etc.

Anstatt auf direkt auf die physische Datei zuzugreifen, verwende die View und die neue Spalte (auch in den WHERE-Bedingungen).

Übrigens solange Like 'XXX%' verwendet wird, kann der Optimizer auch einen Index verwenden, das ist nicht das Problem.
... und das merkst Du auch beim Query400, hier wird wahrscheinlich ein Index verwendet
Das Problem ist, dass auf der linken Seite des Vergleichsoperators (LIKE) die "Original"-Spalte "verändert" wurde.

Auf welchem Release bist Du eigentlich?
Bei 7.2 oder höher wird auch Query400 (wie übrigens auch alle anderen non-SQL-Zugriffe, u.a. native I/O oder UPDDTA) über SQL bzw. über die SQE ausgeführt.

Birgitta

[Robi]

Erst mal Danke ...

@Dieter
... ein defekt Problem vorstellen und sei es eine klemmende Eingabetaste...

Wo wäre der defekt zu lokalisieren?
qsecofr qsysopr qhst, joblog, ... alles ohne 'Fehlermeldung'
und nein, die eingabetaste klemmt auch nicht ...


@Andreas
CCSID 273
Mit den 'SQL Analyse Tools' (PC basiert, tu ich mich leider immer sehr schwer .. klar, ist mein Fehler,
schon klar ...

@Birgitta
habe die View erstellt und bin nun genau so schell wir mit QRY. Danke!
Verstehen tu ichs nicht ...
Auf der Kiste läuft 7.1, und mindestens TR 6 (fremd gehostet)

Robi

[BenderD]

Erst mal Danke ...

@Dieter
... ein defekt Problem vorstellen und sei es eine klemmende Eingabetaste...

Wo wäre der defekt zu lokalisieren?
qsecofr qsysopr qhst, joblog, ... alles ohne 'Fehlermeldung'
und nein, die eingabetaste klemmt auch nicht ...
Robi

... ich würde mal Softwaredefekt bei IBM reklamieren. Die von Dir genannte Dauer ist bei der Datenmenge unplausibel lang - natürlich könnte man noch versuchen das einzugrenzen, indem man die Datei dupliziert und die Satzanzahll reduziert, um zu sehen, was dann passiert. Es wäre auch interessant zu sehen ob der Prozess noch CPU zieht und/oder I/O.

D*B

[Robi]

So, nochmal eine Frage
da die Performance auf die View unterirdisch langsam ist ist die neue Idee mit einzelnden, auf einander basierenden Abfragen, die Performance zu steigern.

Ist der folgende Denkansatz richtig?

Selektionsfelder aus mehr als 1 Datei
With a as (select f1, f2, f3 from datei1 where ...) (die Datei mit den wenigsten Sätzen)

Selektion mit mehr als 2 Dateien
b as (select f6, f4, f5 from datei2 inner join a on f1=f6 and f2 = f4 and f3 = f5 where ...)

...

Das alles in ein Statement
Wird dann 'intern' a durchgeführt und auf die ergebnismenge b

Bsp.:

with a as (
  SELECT susunr, susun2 
  from      DATEIA
  where   upper(SUNAme) like '%MÜLLER%' and upper(suort) like 'G%'),          
        b as (
   select azkey1, azkey2, azkey3, a.* 
   from   DATEIB inner join a on a.susunr=azsunr and a.susun2=azsun2),                              
        c as (
   select httext, b.* 
   from    DATEIC inner join b on htkey1=azkey1 and htkey2=azkey2 and htkey3=azkey3 
   where upper(httext) like '%123%')                 
select * from c

DATEIA 1.881.000 Sätze, davon 606 zu der selektion passend
DATEIB 2.590.000 Sätze, davon 839 zu der selektion passend
DATEIC 44.130.000 Sätze, davon 27.580 mit dem %123% und
9 Sätze die zu der gesammten selektion passen

Dauert 2 Minuten, 30 sekunden und ist somit unakzeptabel!

Ist die Zeit ok oder soll ich echt IBM einschalten?
LF für die Verknüpfung der Dateien untereinander sind vorhanden und werden auch verwendet, V7R1

Auf dateia und dateic liegen views mit zusätzlichen Feldern, upper(suname) upper(suort), upper(httext) und dazu gehörige indexe

Danke
Robi

[BenderD]

... wenn zwei unterschiedliche Beschreibungen eines identischen Resultsets zu unterschiedlichen Laufzeiten führen, dann ist das ein Bug und kein Feature. Wer Aufwand in solche Work arounds steckt, riskiert, dass sich das mit einem PTF wieder in die andere Richtung dreht.
Das gilt auch und insbesonders für rumdrechseln mit CTEs, oder zwischengeschalteten Views (manchmal macht man das trotzdem, weil es eine Anforderung erfüllt).
Die saubere Lösung liegt hier wohl im Datenbankdesign. Ohne nähere Beschreibung könnte es sich sowohl um unterlassene Normalisierung (insbesonderre inkonsistent Foreign Key Beziehungen), als auch um fehlende Denormalisierung (wenn ich einen upper Matchcode brauche, dann sollte es das Feld geben) handeln.

D*B

[Fuerchau]

Dein Problem sind wirklich die "upper(xxx) like '%aaa%'".
Hier hilft wirklich kein Index!
Und was deine SQL-Konstrukte angeht ist es unerheblich, ob du CTE's (WITH) oder derived Tables "from (select ...) hugo" verwendest. Der Optimizer baut das alles sowieso dann um.
Tatsächliche physische temporäre Tabellen gib es eher selten.
Hier hilft dir nur eine oder mehrere Hilfsdatei(en), die i.W. nur die Upper-Felder mit einem Schlüssel zum Original enthält und per Trigger direkt mit dem Upper gefüllt werden. Da geht ggf. auch ein Tablescan schnell da die Satzlänge kurz ist und somt wenige physische Zugriffe erforderlich sind. Im Zweifel erfolgt hier sogar nur ein "Index Only"-Zugriff.

Da du jedoch eher "Textsuchfunktionen" verwendest, kommst du ggf. mit dem DB-Fetaure "SQL Text Extender" besser zurecht (kostet wohl aber Geld). Hier ist die Indizierung eine andere und auch die SQL-Syntax wird etwas erweitert (Contains).

[Robi]

mann ich verstehe es nicht ...

habe o.g. abfrage in 7 qmqry aufgeteilt
create table qtemp/a as ...
b...
c...
select * from c
drop table qtemp a
b
c

die Dinger in einem cl nacheinander aufgerufen und ich bekomme nach 8 Sekunden meine Anzeige

Ich werd Ibm informieren,
weis nuch nicht so richtig was ich denen sagen soll außer: es ist langsam

Robi

[BenderD]

mann ich verstehe es nicht ...

habe o.g. abfrage in 7 qmqry aufgeteilt
create table qtemp/a as ...
b...
c...
select * from c
drop table qtemp a
b
c

die Dinger in einem cl nacheinander aufgerufen und ich bekomme nach 8 Sekunden meine Anzeige

Ich werd Ibm informieren,
weis nuch nicht so richtig was ich denen sagen soll außer: es ist langsam

Robi

... dasselbe geht in SQL auch! Das vorziehen von selects mit hoher Selektivität und anschließendes verjoinen ist oft schneller als die Variante des Query Optimizers. Für ein Subfile (dirty read) kann man dieses Vorgehen auch erzwingen durch eine UDTF zum Beispiel.

D*B

[Fuerchau]

Der Optimizer kann nicht umhin, eben den in seinen Augen (bzw. der Gedanken der Entwickler) optimalen Weg zu finden.
Hier nützt es auch nicht, den SQL zu drehen und zu wenden, da der Optimizer das dann eben umbaut.
Wie gesagt, eine temporäre Tabelle gibt es nicht, der jeweilige Join wird immer direkt ausgeführt, also quasi eingebettet.
Aus dem "inner join a ..." wird eben ein "inner join (select .... ) a ...".
Aus dem "inner join b ..." wird dann ein "inner join (select ... inner join (select ...) a) b ...
Somit erklären sich dann eben die Laufzeiten, da doch erhebliche Daten bewegt werden müssen.
Welche QAQQINI-Einstellung dann noch zu diversen anderen Ergebnissen führen kann ist mitunter auch nicht mehr oder nur schwer nachzuvolllziehen.

Ich habe hier auch gerade eine Tabelle mit 44Mio Sätzen.
Die Like-Klausel benötigt in beiden Fälle (mit oder ohne Upper) für den Tablescan ca. 20 Sekunden.

Deinen Weg der Einzelschritte mache ich auch häufig per embedded SQL. Zusätzlich mach ich allerdings auch einen "Create Index" auf die temporäre Tabelle.
Um nicht ständig neue Tabellen zu erfinden kann man i.Ü. auch mit "declare global temporary Table" sitzungsbezogene Arbeitstabellen anlegen.