Abfrage über mehrere Felder optimieren

[SvB]

Ich habe eine Suchmaske für Beleg, in der folgende Felder angezeigt werden: Belegnummer, Belegdatum, Kundennummer, Kundenname 1, Kundenname 2, Straße, PLZ, Ort, Seriennummer. Das Belegdatum ist ein TIMESTAMP, alle anderen Felder sind VARCHARs. Die Felder sind alle in der selben Tabelle. Zu jedem Feld gibt es auch einen eigenen Index, da ich diesen auch für andere Dinge benötige.
In der Suchmaske gibt es ein Eingabefeld, in den etwas beliebiges eingegeben werden kann und in den oben genannten Feldern gesucht werden soll. Im Moment baue ich mir ein SQL mit OR und LIKE zusammen. Beim Kunden dauert die Abfrage je nach dem zwischen mehreren Sekunden bis zu 3 Minuten und ist daher nicht optimal. Der Anwender ist damit nicht sehr zufrieden.
Da ist kein 100%-iger Datenbank Profi bin, weiß ich im Moment nicht wie ich das verbessern kann. Ich wäre für einige Tipps sehr dankbar.

[waldforest]

Hallo,
über welche Datenmenge erfolgt die Abfrage, kannst du deine Abfrage mal hier einstellen ?

Suchst du immer in alle Feldern über ein Eingabefeld.
Eine Abfrage mit direktem Bezug zum Suchfeld wäre sicher optimaler.

[Dejan Vu]

Bei anderen DB würde ich eine derartige Anforderung über einen Volltextindex lösen. Dabei werden alle zu durchsuchenden Felder mit ' ' als Trenner konkateniert und in einer separaten View indiziert.

Aber Du hast leider FB, der kann das nicht (Nachrüsten geht wohl, mit kleinen Klimmzügen)

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

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

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