Abfrage über mehrere Felder optimieren

[SvB]

Hi,

aktuell sind es ca. 400.000 Datensätze.

Die Abfrage sieht z.B. so aus:
SELECT ID,BEL_SERNr,BEL_Dat,BEL_KD_Nr,BEL_KD_SEQ,BEL_KD_N ame1,BEL_KD_Strasse,BEL_KD_PLZ,BEL_KD_Ort,BEL_MA_S erNum,BEL_TechNr FROM QRY_BELEGSEARCHBEL_SERNR WHERE ((UPPER(BEL_SERNR) LIKE ''%BÄCKEREI%'') OR (UPPER(BEL_KD_NR) LIKE ''%BÄCKEREI%'') OR (UPPER(BEL_KD_SEQ) LIKE ''%BÄCKEREI%'') OR (UPPER(BEL_KD_NAME1) LIKE ''%BÄCKEREI%'') OR (UPPER(BEL_KD_STRASSE) LIKE ''%BÄCKEREI%'') OR (UPPER(BEL_KD_PLZ) LIKE ''%BÄCKEREI%'') OR (UPPER(BEL_KD_ORT) LIKE ''%BÄCKEREI%'') OR (UPPER(BEL_MA_SERNUM) LIKE ''%BÄCKEREI%'') OR (UPPER(BEL_TECHNR) LIKE ''%BÄCKEREI%'')) ORDER BY BEL_SERNR ASC

Es ist nur ein Eingabefeld für die Suche. Hier im Beispiel wird jetzt nach "Bäckerei" gesucht, wobei das natürlich in der Belegnummer oder der PLZ keinen Sinn macht. Der Benutzer soll der Einfachheit halber nur "einen" Suchbegriff eingeben und es soll in allen Feldern gesucht werden. Mir ist bewusst, dass dies nicht optimal ist und ich suche deshalb nach einer anderen und besseren Lösung.
Zukünftig möchte ich noch einbauen, dass z.B. durch Leerzeichen getrennt mehrere Suchbegriffe eingegeben werden können wie z.B. "Bäckerei München". Das macht das Ganze aber auch nicht einfacher. Zunächst benötige ich aber eine Verbesserung der aktuellen Situation und bin für jeden Tipp und Vorschlag dankbar.

Wie ich aus Deiner Info sehe, wäre es besser, wenn ich zum Suchbegriff angebe, in welchem Feld gesucht werden soll. Richtig?

[DeddyH]

Die größte Bremse ist das vorangestellte "%", da damit kein Index der Welt mehr greifen kann.

[Sir Rufo]

Zitat von DeddyH:

Die größte Bremse ist das vorangestellte "%", da damit kein Index der Welt mehr greifen kann.

Wird der Index nicht schon durch das Upper ausgehebelt?

Und der Name QRY_* lässt mich irgendwie an eine View denken ...

[SvB]

Stimmt. Mit dem vorangestellten % nützt kein Index was, hab noch mal selbst etwas recherchiert. Das ist schon mal sehr schlecht. Was mach ich aber wenn in einem Feld "- Cafe Quirl -" (ohne die ") steht und ich nach "Cafe" suchen möchte, dann bekomme ich den Datensatz ja nicht zurück. Irgendwie muss ich schon so suchen können, dass der eingegebene Text irgendwo im Feld stehen kann.

Mit dem Upper muss ich mir auch noch mal ansehen.
QRY_* ist eine View, da hab ich schon mal was drüber probiert, hat aber auch keine Geschwindigkeitsverbesserung gebracht.

Ich glaube es ist wirklich am besten, wenn der Benutzer angibt, in welchem Feld er suchen möchte. Wenn er z.B. die Belegnummer eingibt, dann kann das Programm ja direkt nur mit diesem Feld suchen. Entsprechend mit dem Datum, der Kundennummer usw. Bei den anderen Feldern ist es genau so. Das Wort "Cafe" wird auch nur im Namen gesucht, im "Ort" gibt es das nicht.

[nahpets]

Hallo,

ist like bei FB Casesensitive? Bei manchen Datenbanken ist bei like die Groß-/Kleinschreibung nicht von Belang.
Wenn FB die Groß-/Kleinschreibung bei like nicht beachtet, kannst Du die Uppers schonmal weglassen.

Dann ein schräger Vorschlag:

Datenbank um eine Spalte Upperdaten (oder wie auch immer Du sie nennen magst) erweitern. Spalte ausreichend "breit" dimensionieren, so dass alle Werte der zu durchsuchenden Spalten aneinandergehängt da rein passen.

Deine Abfrage ist dann "nur noch"

Code:

SELECT ID,BEL_SERNr,BEL_Dat,BEL_KD_Nr,BEL_KD_SEQ,BEL_KD_N ame1,BEL_KD_Strasse,BEL_KD_PLZ,BEL_KD_Ort,BEL_MA_S erNum,BEL_TechNr FROM QRY_BELEGSEARCHBEL_SERNR WHERE Upperdaten like '%BÄCKEREI%'

Die Spalte muss dann halt bei jeder Änderung (per Trigger?) mitgepflegt werden. Jedenfalls muss FB dann nicht bei jeder Abfrage die ganze Tabelle in Großschrift umwandeln.

Und wenn Du die Daten in der Upperdaten-Spalte noch mittels Kölner Phonetik ablegst und den eingegebene Suchbegriff ebenfalls entsprechend behandelst, wird die Suche auch noch fehlertolerant. Es werden dann nicht nur die Bäckereien sondern auch die Baeckereien gefunden und sowohl Cafe als auch Café (dann aber auch Kaffee). Und ob - davor, dahinter oder dazwischen stehen, wird auch irrelevant. Eignet sich aber nur für die Textsuche und nicht für die Suche nach Zahlen oder Buchstaben-/Zahlenkombinationen.

Sofern der obige Ansatz zu einem Geschwindigkeitsvorteil führt, könnte die Kölner Phonetik zu einem Suchkomfort führen, der keinen zusätzlichen Aufwand bei der Suche erfordert und einen neuen Flaschenhals generieren könnte.

Eine Implementierung der Kölner Phonetik findest Du hier, sofern es Dich irgendwann interessieren sollte.

[Perlsau]

Zitat von SvB:

Ich glaube es ist wirklich am besten, wenn der Benutzer angibt, in welchem Feld er suchen möchte. Wenn er z.B. die Belegnummer eingibt, dann kann das Programm ja direkt nur mit diesem Feld suchen. Entsprechend mit dem Datum, der Kundennummer usw. Bei den anderen Feldern ist es genau so. Das Wort "Cafe" wird auch nur im Namen gesucht, im "Ort" gibt es das nicht.

Das mache ich quasi schon immer so. Manchmal gibt es sogar nur ein Feld, das durchsucht werden darf oder nur ein paar wenige – eben die, die überhaupt Text enthalten können. Wiederkehrende Inhalte wie Kundennamen oder Branchen-Bezeichner (Bäckerei) oder Ortnamen usw. sollte man sowieso in einer eigenen Tabelle führen und in der Haupttabelle nur die jeweilige ID mitführen. Das erleichtert erstens die Suche und schützt zweitens vor Fehleingaben (Baeckerei - Bäckerei - Bäkerei - Bäckerie - Beackerei, Bäcker, Becker ... wenn man sich allein die mangelhafte Rechtschreibung etlicher Forenuser anschaut ...). Wenn nur eine Kunden-Nummer gesucht wird oder ein Kunde, dann sollte in der Haupttabelle, in der der Kunde lediglich durch deine ID repräsentiert ist, nur nach dieser ID gesucht werden müssen. So eine eierlegende Wollmilchsausuche kann letztlich nur sehr langsam sein, wenn man ständig alle Begriffe in allen Feldern zu finden versucht: Du mußt dir klarmachen, daß dabei jedes zu durchsuchende Feld auf die Suchbegriffe hin abgeklopft werden muß, das dauert eben.

Noch ein Hinweis: Indiziere Felder oder Feldgruppen, die häufig durchsucht oder lokalisiert werden müssen. Das bringt schonmal enorme Geschwindigkeitsgewinne.

[mkinzler]

Zitat:

Wird der Index nicht schon durch das Upper ausgehebelt?

Kommt auf den Index an. Bei eienm entsprechenden expression index ist das egal.

[hoika]

Hallo,
wie sieht denn der Query-Plan aus?
Bekommt man z.B. mit IB-Expert.


Heiko

[jobo]

Statt "LIKE" könnte man "CONTAINING" nehmen, das umgeht schonmal das "Upper"-Problem. Ich weiß allerdings nicht, wie geschickt es vorhandene Indices nutzt.
Die vielen "OR" bzw. das Suchverfahren einen Suchwert in vielen Feldern zu suchen, ist aber wohl das größere Problem. Zumindest deutet die Suchzeit darauf hin, dass es nicht um einen, sondern viele Full Table Scans geht.
Wenn man dieses merkwürdige Verfahren beibehalten will, kann man lieber gleich per SP und Cursor alles einmal abklappern.

Ansonsten ist es natürlich empfehlenswert, gezielt zu suchen. Wenn es um 's Datum geht, dann bitte nur auf dem Datumsfeld und natürlich nicht per Wildcard (wenn es denn wirklich ein DATE Feld ist).
Eine solche Zusammenfassung der Suche auf mehreren Feldern ist m.E. nur vertretbar, wenn sich
a) die Inhalte vom Nutzer nicht unterscheiden lassen
b) die Datenstrukturen sich absehbar nicht sinnvoll differenzieren lassen
c) die Suchwerte strukturell so unterschiedlich sind, dass nach der Eingabe bzw. aus dem Eingabewert/-formatierung eindeutig auf das Feld geschlossen werden kann. Also z.B. Artikelnummer, Preis, Bestelldatum oder Artikelbezeichnung

Suchen mit potentiell großer Ergebnismenge sollten ebenfalls unterbunden werden, sie kosten enorm Ressourcen und bringen keinen Nutzen vür den Anwender. Also z.B. Suche nach Preis im 1 Euro Shop.

[Dejan Vu]

Zitat von DeddyH:

Die größte Bremse ist das vorangestellte "%", da damit kein Index der Welt mehr greifen kann.

Schade, dass das bei FB so ist. Denn es muss nicht sein. Ein 'Index Scan' ist wesentlich schneller als ein 'Table Scan', weil weniger Daten zu laden sind.