SQLRPGLE - Optimierung

[cicero22]

Hallo Forum,

wir beabsichtigen über unseren Artikelstamm eine eigene Volltextsuche zu programmieren mit deinem SQLRPGLE. Bislang sind wir schon recht zufrieden und haben Antwortzeiten im 500 ms Bereich. Der Anwender kann insgesamt 5 Suchbegriffe mit Leerzeichen getrennt eingeben - welche dann im SQLRPGLE über ein SQL auf Artikelstamm und Artikelmerkmale los gehen mit Like %text% (bzw. nur like %text bei den Merkmalen) Aus dem Lagerstamm soll "nur" der Bestand und der Lagerplatz dazugeholt werden. Jedoch ist diese im SQL "verknüpft" um später noch eine absteigende Sortierung nach Verfügbarkeit durchzuführen.

Kurz zum Dateihintergrund

Artikelstamm (knapp 300.000 Sätze)
Lagerstamm (knapp 100.000 Sätze)

Artikelmerkmale (knapp 2.500.000 Sätze)

Bisher sieht das SQL im SQLRPGLE wie folgt aus_



+ select distinct
+ AATNR ,
+ coalesce(AGART, 0) ,
+ coalesce(ALGNR, 0) ,
+
+ trim(abez1) concat ' '
+ concat trim(abez2) concat ' ' CONCAT trim(abez3) concat ' '
+ concat trim(abez4) ,
+
+ coalesce(AVERB , 0) ,
+ coalesce(AHALLE, '') ,
+ coalesce(AREIHE, '') ,
+ coalesce(APLATZ, '') ,
+ coalesce(ALATN1, '')
+ coalesce(ARTIKELMERKMAL , '')
+
+ from temp/v1000 a
+ left outer join temp/ARTIKELMERKMALDATEI c on a.aatnr = c.f2
+ where
+ ( UPPER(ABEZ1) like TRIM(:WORT1)
+ or UPPER(ABEZ2) like trim(:WORT1)
+ or UPPER(ABEZ3) like trim(:WORT1)
+ or UPPER(ABEZ4) like trim(:WORT1)
+ or UPPER(C.ARTIKELMERKMAL) like trim(:WORT1H)
+
+ )
+ and
+
+ ( UPPER(ABEZ1) like trim(:WORT2)
+ or UPPER(ABEZ2) like trim(:WORT2)
+ or UPPER(ABEZ3) like trim(:WORT2)
+ or UPPER(ABEZ4) like trim(:WORT2)
+ or UPPER(c.ARTIKELMERKMAL) like trim(:WORT2H)
+ )
+ and
+
+ ( UPPER(ABEZ1) like trim(:WORT3)
+ or UPPER(ABEZ2) like trim(:WORT3)
+ or UPPER(ABEZ3) like trim(:WORT3)
+ or UPPER(ABEZ4) like trim(:WORT3)
+ or UPPER(c.ARTIKELMERKMAL) like trim(:WORT3H)

+ )
+ and
+
+ ( UPPER(ABEZ1) like trim(:WORT4)
+ or UPPER(ABEZ2) like trim(:WORT4)
+ or UPPER(ABEZ3) like trim(:WORT4)
+ or UPPER(ABEZ4) like trim(:WORT4)
+ or UPPER(c.ARTIKELMERKMAL) like trim(:WORT4H)
+ )
+ and
+ ( UPPER(ABEZ1) like trim(:WORT5)
+ or UPPER(ABEZ2) like trim(:WORT5)
+ or UPPER(ABEZ3) like trim(:WORT5)
+ or UPPER(ABEZ4) like trim(:WORT5)
+ or UPPER(c.ARTIKELMERKMAL) like trim(:WORT5H)
+
+ and coalesce(AGART, 0) < 02 and coalesce(ALGNR, 0) < 02
+ order by 5 desc


Kurzerklärung Variablen:
AATNR = Artikelnummer
ABEZx = Artikelbezeichnung 1-4

Die V1000 im SQL ist ein View der Artikelstamm und Lagerstamm mit einem LEFT OUTER JOIN VERBINDET.

Ohne die wie oben im SQL angegebene Verknüpfung auf die Artikelmerkmale Datei (in der soll nur ein Merkmal mitdurchsucht werden) ist das SQL rasend schnell - doch sobald diese "große" Artikelmerkmaldatei mit einbezogen wird über den LEFT OUTER JOIN ist es "vorbei" mit der schnellen Suche! Da ist die Antwortzeit bei ca. 4 Sekunden.

Auch Versuche die ARTIKELMERKMAL Datei in den View (V1000) mit einzubeziehen brachten keinen merklichen Erfolg.
Im Joblog kann ich nicht erkennen das der AS/400 Zugriffspfade oder ähnliches fehlen.

Kann jemand helfen? Bringt eine Änderung/Anpassung der QAQQINI etwas? Gibt es eine spezielle Umwandlungsoption die wir vielleicht nicht kennen? Kann man das SQL "geschickter" umbauen? Unsere Maschine hat 4.300 CPW.

Danke - Dirk

[BenderD]

sobald beim like im Suchmuster links ein % auftaucht erzwingt das einen Full Table Scan auf die entsprechende Tabelle, wenn rechts keiner ist, ( like '%text') kann man das Datenbank technisch verhindern durch ein zusätzliches Feld in umgekehrter Buchstaben Reihenfolge.
Wenn man entsprechende Hardware (und Geld) hat, sprich genug Prozessoren, dann hilft bei Full Table Scans das parallel Database Feature (kostet Geld).
Wenn das ganze in einem Programm passiert, dann hilft es zuweilen erst einen Extrakt über die Tabelle mit dem Like zu ziehen und erst dann zu joinen.
Irgendwelche Zauberschalter gibt es da keine (dann stünden die schon auf schnell!)

D*B



Hallo Forum,

wir beabsichtigen über unseren Artikelstamm eine eigene Volltextsuche zu programmieren mit deinem SQLRPGLE. Bislang sind wir schon recht zufrieden und haben Antwortzeiten im 500 ms Bereich. Der Anwender kann insgesamt 5 Suchbegriffe mit Leerzeichen getrennt eingeben - welche dann im SQLRPGLE über ein SQL auf Artikelstamm und Artikelmerkmale los gehen mit Like %text% (bzw. nur like %text bei den Merkmalen) Aus dem Lagerstamm soll "nur" der Bestand und der Lagerplatz dazugeholt werden. Jedoch ist diese im SQL "verknüpft" um später noch eine absteigende Sortierung nach Verfügbarkeit durchzuführen.

Kurz zum Dateihintergrund

Artikelstamm (knapp 300.000 Sätze)
Lagerstamm (knapp 100.000 Sätze)

Artikelmerkmale (knapp 2.500.000 Sätze)

Bisher sieht das SQL im SQLRPGLE wie folgt aus_



+ select distinct
+ AATNR ,
+ coalesce(AGART, 0) ,
+ coalesce(ALGNR, 0) ,
+
+ trim(abez1) concat ' '
+ concat trim(abez2) concat ' ' CONCAT trim(abez3) concat ' '
+ concat trim(abez4) ,
+
+ coalesce(AVERB , 0) ,
+ coalesce(AHALLE, '') ,
+ coalesce(AREIHE, '') ,
+ coalesce(APLATZ, '') ,
+ coalesce(ALATN1, '')
+ coalesce(ARTIKELMERKMAL , '')
+
+ from temp/v1000 a
+ left outer join temp/ARTIKELMERKMALDATEI c on a.aatnr = c.f2
+ where
+ ( UPPER(ABEZ1) like TRIM(:WORT1)
+ or UPPER(ABEZ2) like trim(:WORT1)
+ or UPPER(ABEZ3) like trim(:WORT1)
+ or UPPER(ABEZ4) like trim(:WORT1)
+ or UPPER(C.ARTIKELMERKMAL) like trim(:WORT1H)
+
+ )
+ and
+
+ ( UPPER(ABEZ1) like trim(:WORT2)
+ or UPPER(ABEZ2) like trim(:WORT2)
+ or UPPER(ABEZ3) like trim(:WORT2)
+ or UPPER(ABEZ4) like trim(:WORT2)
+ or UPPER(c.ARTIKELMERKMAL) like trim(:WORT2H)
+ )
+ and
+
+ ( UPPER(ABEZ1) like trim(:WORT3)
+ or UPPER(ABEZ2) like trim(:WORT3)
+ or UPPER(ABEZ3) like trim(:WORT3)
+ or UPPER(ABEZ4) like trim(:WORT3)
+ or UPPER(c.ARTIKELMERKMAL) like trim(:WORT3H)

+ )
+ and
+
+ ( UPPER(ABEZ1) like trim(:WORT4)
+ or UPPER(ABEZ2) like trim(:WORT4)
+ or UPPER(ABEZ3) like trim(:WORT4)
+ or UPPER(ABEZ4) like trim(:WORT4)
+ or UPPER(c.ARTIKELMERKMAL) like trim(:WORT4H)
+ )
+ and
+ ( UPPER(ABEZ1) like trim(:WORT5)
+ or UPPER(ABEZ2) like trim(:WORT5)
+ or UPPER(ABEZ3) like trim(:WORT5)
+ or UPPER(ABEZ4) like trim(:WORT5)
+ or UPPER(c.ARTIKELMERKMAL) like trim(:WORT5H)
+
+ and coalesce(AGART, 0) < 02 and coalesce(ALGNR, 0) < 02
+ order by 5 desc


Kurzerklärung Variablen:
AATNR = Artikelnummer
ABEZx = Artikelbezeichnung 1-4

Die V1000 im SQL ist ein View der Artikelstamm und Lagerstamm mit einem LEFT OUTER JOIN VERBINDET.

Ohne die wie oben im SQL angegebene Verknüpfung auf die Artikelmerkmale Datei (in der soll nur ein Merkmal mitdurchsucht werden) ist das SQL rasend schnell - doch sobald diese "große" Artikelmerkmaldatei mit einbezogen wird über den LEFT OUTER JOIN ist es "vorbei" mit der schnellen Suche! Da ist die Antwortzeit bei ca. 4 Sekunden.

Auch Versuche die ARTIKELMERKMAL Datei in den View (V1000) mit einzubeziehen brachten keinen merklichen Erfolg.
Im Joblog kann ich nicht erkennen das der AS/400 Zugriffspfade oder ähnliches fehlen.

Kann jemand helfen? Bringt eine Änderung/Anpassung der QAQQINI etwas? Gibt es eine spezielle Umwandlungsoption die wir vielleicht nicht kennen? Kann man das SQL "geschickter" umbauen? Unsere Maschine hat 4.300 CPW.

Danke - Dirk

[Fuerchau]

Das Problem ist, dass jeder LIKE keinen Zugriffspfad verwenden kann sondern einen Tablescan erfordert.
Optimierungen kann man nur bei Zugriffswegen erreichen, wenn Operatoren wie =, >=, between verwendet werden.
Das selbe gilt auch bei "where coalesce".

Auch UPPER kann keinen Zugriffspfad verwenden. Ggf. kann man mittels SRTSEQ auf einer LF sprachunabhängig arbeiten.

Vielleicht hilft hier auch dynamisches SQL per Prepare um den SQL zu verkürzen, wenn nicht alle Begriffe verwendet werden müssen.

Ist wirklich LEFT JOIN erforderlich ?
Wenn nein, kannst du dir coalesce schon mal sparen.

Auch ein CommonTableExpression kann helfen:

with
xArt as (
select ... from ARTIKELMERKMAL
where ...)

select ...
inner join xART on ...

Aber wie gesagt, die Hauptprobleme sind UPPER und LIKE.

[cicero22]

Schonmal besten Dank Ihr Beiden!

Ja - der Left outer Join ist erfoderlich - denn wir wollen die Artikel auch finden wenn sie im Artikelstamm sind aber kein Lagerbestand hierfür da ist. (gleiches gilt auch für die Artikelmerkmaldatei!)

Was uns so "schwer" fällt - ohne dieses "recht große" Artikelmerkmaldatei geht die Suche super super flott (500 ms) - erst wenn die Verknüfung auf die Artikelmerkmaldatei erfolgt fällt die Suche auf ca. 4-5 Sekunden ab! (pro Artikel können bis zu 20-30 Merkmale vorhanden sein)

Noch eine Idee - könnte es vielleicht noch etwas bringen wenn wir diese Artikelmerkmale pro Artikel alle in "einen Satz (ein Feld)" bringen (so das wir eben maximal dort soviel Sätze haben wir im Artikelstamm?)

[B.Hauser]

Nur um sicherzugehen, dass es daran nicht liegen kann:
Existiert ein Index oder eine geschlüsselte logische Datei über die Artikelmerkmalsdatei mit F2 asl 1. Key-Feld?

Wenn nicht auf alle Fälle anlegen. Dadruch müsste ein Table Scan oder eine Table Probe verhindert werden und ein Index verwendet werden. (Eine weitere Möglichkeit wäre statt eines normalen Indices einen Encoded Vector Index anzulegen, je nach extrahierter Datenmenge kommt dieser dann zum Zug, wenn ein normaler Index nicht mehr greift.)

Solltet Ihr bereits auf Release 6.1 sein, kannst Du auch einen Index (Binary Radix oder EVI) über F2 und UPPER(Artikelmerkmal) bilden, wodurch m.E. zumindest der Konvertierungsaufwand reduziert werden sollte.


Birgitta

[KM]

Ich weiß nicht, ob das für Dich eine Option ist, aber falls bei Euch ein wenig Java Skills vorhanden sind, dann schau Dir mal die Suchmaschine LUCENE an. Damit habe ich die Suche für unseren Web-Shop programmiert. Ist zwar ein wenig aufwendiger als "nur" ein RPG-Programm zu schreiben. Dafür hat man aber sämtliche Vorteile dieser sehr guten Suchmaschine. Du kannst dann z.B. im Suchbegriff komplexe Konstrukte angeben, wie z.B.

(FeldA = "X" AND FeldB = "Y") OR FeldC = "Z"

Die Antwortzeiten sind enorm schnell.

Gruß,
KM

[cicero22]

Nur um sicherzugehen, dass es daran nicht liegen kann:
Existiert ein Index oder eine geschlüsselte logische Datei über die Artikelmerkmalsdatei mit F2 asl 1. Key-Feld?

Wenn nicht auf alle Fälle anlegen. Dadruch müsste ein Table Scan oder eine Table Probe verhindert werden und ein Index verwendet werden. (Eine weitere Möglichkeit wäre statt eines normalen Indices einen Encoded Vector Index anzulegen, je nach extrahierter Datenmenge kommt dieser dann zum Zug, wenn ein normaler Index nicht mehr greift.)

Solltet Ihr bereits auf Release 6.1 sein, kannst Du auch einen Index (Binary Radix oder EVI) über F2 und UPPER(Artikelmerkmal) bilden, wodurch m.E. zumindest der Konvertierungsaufwand reduziert werden sollte.


Birgitta

Hallo Brigitta -

ja - diesen Index auf F2 haben wir angelegt.
Oh - das mit dem EVI oder Binary Radix (und gleich dem UPPER(ARTIKELMERKMAL) ) - das hört sich gut an - wir haben V6R1 - wie lege ich den an? Im grünen Bildschirm mit CREATE INDEX finde ich das nicht....


Übrigens - nach einem ersten Test dieses ganzen Artikelmerkmale in jeweils einen Satz pro Artikel zu bringen machen wir gerade super super Fortschritte! Das könnte eine "Zwischenoptimierungslösung" für uns sein!

[Fuerchau]

Manchmal sind die Anzahl Datensätze eben entscheidender als die Anzahl Felder.
Dynamischer SQL ist insbesonders dann hilfreich, wenn eben nicht alle Suchfelder gefüllt sind.
Dies ist gerade bei LIKE-Suchen entscheidend.

[BenderD]

... nur damit ihr euch keine unnötige Arbeit macht:
- sobald in der Where Klausel in einem Vergleich ein UPPER(Feld) oder ähnliches auftaucht (selbst abweichende CCSID reicht), hilft ein Index nichts mehr.
- sobald in einem Suchmuster eines like Vergleiches eine Wildcard auftaucht, steigt jeder Index aus; steht die Wildcard am Anfang hilft er nix.
- EVI könnte nur vorteilhaft (habe ich in Praxis noch nie - in Worten: nie - gesehen) sein, wenn hinter den 2,5 Millionen Sätzen in der Artikelmerkmaldatei viele Artikel mit wenigen verschiedenen Merkmalen stehen, dann kann man das Ganze auch Datenbanktechnisch lösen (falls die Datenbank EVI immer noch nicht richtig kann), mit einer Merkmaledatei, die hat dann wenige Sätze und in der Artikelmerkmaldatei stehen dann die Verknüpfungen zwischen Merkmal und Artikel.
Die Abfrage kann dann als select from Artikel where ...
and Merkmal in (select ... from MerkmalDatei where merkmal like '%text%') und der innere Subselect verliert seinen Schrecken, weil er nur noch auf Merkmale geht.
BTW: letzteres kann noch ein Versuch sein, sprich einen Index nur über die Merkmale anlegen und dann den like Vergleich in einen Subselect zu schieben.

D*B


Hallo Brigitta -

ja - diesen Index auf F2 haben wir angelegt.
Oh - das mit dem EVI oder Binary Radix (und gleich dem UPPER(ARTIKELMERKMAL) ) - das hört sich gut an - wir haben V6R1 - wie lege ich den an? Im grünen Bildschirm mit CREATE INDEX finde ich das nicht....


Übrigens - nach einem ersten Test dieses ganzen Artikelmerkmale in jeweils einen Satz pro Artikel zu bringen machen wir gerade super super Fortschritte! Das könnte eine "Zwischenoptimierungslösung" für uns sein!