Abfrage über mehrere Felder optimieren

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

Es gibt noch einen sinnvollen Grund, so eine Suche à la Suchmaschine (als nur mit einem Eingabefeld) zu machen: Bequemlichkeit, aka 'usability'.

Ich suche nach einem Hr. Müller in Wattenscheid. Also gebe ich ein 'Müller Watt' und meine Routine liefert mir alle Müller in Wattenscheid. Aber klar, es findet auch alle Wattwanderer in Müllersried.

Und ich muss nicht 'umständlich' die einzelnen Suchfelder ausfüllen. Das ist bequem, einfach, und genau so, wie ich es von GoogleBing gewohnt bin.

Die Frage ist erlaubt, ob das Ausfüllen individueller Suchfelder umständlich ist, oder nicht. Das bestimmt der Kunde. Bei uns möchte er nur ein Eingabefeld.

[jobo]

Zitat von Dejan Vu:

Bequemlichkeit

hat ihren Preis

Zitat von Dejan Vu:

'usability'

Fail bei 3 Minuten Antwortzeit

Zitat von Dejan Vu:

Die Frage ist erlaubt, ob das Ausfüllen individueller Suchfelder umständlich ist, oder nicht. Das bestimmt der Kunde. Bei uns möchte er nur ein Eingabefeld.

Es gibt natürlich für einen Entwickler nichts besseres als einen anspruchsvollen Kunden, der sich diesen Anspruch auch leisten kann.

Und zum Thema Google/Bing. Die (wollen) verdienen ihr Geld genau damit. Und sie liefern technisch gesehen etwas vollkommen anderes ab, als hier gefragt ist. Google/Bing sind schnell und die Ergebnisse sind nicht überprüfbar, also viel gebe ich darauf nicht, auch wenn die Handhabung verheißungsvoll ist. (Millionen Fliegen irren ja bekanntlich nicht)

[nahpets]

Hallo,

habe vor Jahren mal 'ne Suchmaschine (für beliebige Texte) geschrieben, die war und ist sauschnell, auch wenn sie eine Volltextsuche enthält. Das Vorgehen ist ganz einfach:

Texte werden in Einzelwörter zerlegt. Diese Wortliste wird von Dubletten bereinigt, so dass es eine eindeutige Liste wird.

Es gibt (beispielhaft) insgesamt drei Tabellen:

1. Daten : DatenID : Integer; beliebige Spalten...
2. Wortliste : WortID : Integer; Wort : varChar
3. Referenzliste: DatenID : Integer; WortID : Integer

Vorgehen:
Beim Speichern eines Satzes in der Datentabelle werden alle Felder, die im Volltextindex benötigt werden, zu einem Text zusammengedaddelt.
Dieser Text wird in Einzelwörter zerlegt.
Jedes, noch nicht in der Wortliste enthaltene, Wort wird in die Wortliste eingefügt, die Wortliste ist also eine eindeutige Liste.
Es wird die Referenzliste gepflegt. Für jedes Wort des Datensatzes wird ein Eintrag DatenID <-> WortID in die Referenzliste eingefügt.

Bei der Suche muss man dann nur (per Index) alle Sätze suchen, die zum eingegebenen Wort passen.

Das geht (beispielhaft) per:

Code:

select a.* from Daten a, Referenzliste b, Wortliste c where a.DatenID = b.DatenID and b.WortID = c.WortID and c.Wort = :Eingabe

(Selbst ein Like auf die Wortliste sollte hier nicht zum Performanzkiller werden.)

Dafür braucht man auf der Datentabelle einen Index auf der DatenID, auf der Referenztabelle einen auf DatenID und einen auf WortID und für die Worttabelle einen auf Wort.

Wenn man nun die Wortliste grundsätzlich nur in Kleinschreibweise befüllt, benötigt man für den Suchbegriff nur einmal ein Lower und spart sich die ganze Schreibweisenveränderung in jedem SQL.

Für eine schnelle Suche muss man sich die Daten nunmal normalisieren und kommt mit einer Tabelle und einem SQL ala "Liebe Datenbank, guckt doch mal, ob Du nicht eventuellunterumständenvielleicht irgendwo was passendes finden könntest" nicht aus.

Dieses sehr ungenaue "Habenmöchten" dauert einfach zu lange, wenn man nicht vorher für eine passende Struktur gesorgt hat.

Das "Wortlisteerstellen" ist (mit Delphi) sehr einfach.
Alle benötigten Felder in eine Stringliste, Delimiter auf Blank und DelimitedText abrufen. Sort aufrufen, Dubletten raus und schon kann die Wortliste in der Datenbank gepflegt werden.

Hier im Beispiel sollte die Änderung des "Systems" in ein- bis zwei Tagen in ausgereifter Form möglich sein.

Wenn man nun noch (wie oben in 'nem anderen Beitrag beschrieben) die Kölner Phonetik für die Wortliste nutzt, dann kann man damit ähnlich unscharf Suchen, wie wir es von den Suchmaschinen gewohnt sind. Meier findet dann auch Maier und Mayer und Meyer und Hofmann findet auch Hoffmann..., Cafe und Café werden nicht mehr unterschieden...

Soll der Suchbegriff mehrere Wörter enthalten (können), dann passt man das SQL halt an:

Code:

select a.* from Daten a, Referenzliste b, Wortliste c where a.DatenID = b.DatenID and b.WortID = c.WortID and c.Wort in (:Wort1, Wort2, ..., WortN)

Auf Like kann man dann ganz verzichten und kann immer per Index suchen.

Wenn das dann (mit zusätzlichem Suchkomfort) nicht schnell wird, hat man ein vollkommen anderes Problem, das nur zufällig bei der Suche deutlich aufgefallen ist.

[jobo]

Eine schöne Sache, aber hier wäre ich vielleicht etwas vorsichtiger gegenüber dem TE:

Zitat von nahpets:

Hier im Beispiel sollte die Änderung des "Systems" in ein- bis zwei Tagen in ausgereifter Form möglich sein.

Neben der Implementierung, braucht es Tests und Migration (der Kundendaten). Passt eine Kölner Phonetik auch für französische Kundennamen, türkische oder polnische ..? Wenn nicht, was dann? Der anspruchsvolle Kunde wird dann vielleicht sauer, weil er sagt, er hat viel Geld ausgegeben für ein Verbesseung und nun funktioniert es nur für deutsche Datenbankeinträge.

[nahpets]

Also mit dieser "Methode" (sprich Kölner Phonetik) haben wir den kompletten Datenbestand der Telekom (also alle von denen gelieferten Adressen) gegen andere Systeme abgeglichen, um Dubletten zu bereinigen und Systeme, die über keine eindeutigen Zuordnungen, über wie auch immer geartete Schlüssel, verfügten, miteinander abzugleichen. Und die Resultate waren (sind) verblüffend gut. Neben dem Namensabgleich haben wir noch versucht, die Adressen gegen einen vollständigen Adressbestand der Bundesrepublik abzugleichen, so dass wir (im Idealfall) den Namensabgleich quasi "nur noch" auf Hausebene durchführen mussten. Wer sich aber die Qualität der Telefondaten (in Bezug auf Schreibweise, mehr oder weniger offensichtliche Fehler) anschaut, weiß, dass der Abgleich auf Hausebene eher unwahrscheinlich ist.

Gerade bei "ausländischen" Namen treten häufig unterschiedliche Schreibweisen auf, nehmen wir den Vornamen Juri und/oder Yuri, beides kann korrekt sein, in Bezug auf eine konkrete Person ist aber eine der beiden Schreibweisen falsch, wird so aber trotzdem gefunden.
Oder wie ist es bei meinem Vornamen: Stephan oder Stefan? Like hilft hier herzlich wenig.

Oder nehmen wir den aus dem Marokkanischen stammenden Nachnamen Bouauda, gesprochen Bo-u-da. Wer kann diesen Namen, beim erstmaligen Hören sofort richtig schreiben? Bei der Kölner Phonetik ist's egal, er wird gefunden.
Wie schreibt man den türkischen Namen Cigdem, gesprochen eher wie Schiedämm?
Oder wie schreibt man Koslow? Oder doch eher Kozlow? Oder war es Kozlov oder doch Koslov?
Alles ist richtig, aber wie ist die Schreibweise im konkreten Fall? Like mit '%KOSLOV%' hilft mir da herzlich wenig.
Auch hier wird die Kölner Phonetik eine gute Hilfestellung sein.
Aber die Fehlermöglichkeit des "Zuvielfindens" durch die Unschärfe, wird den Zusatzaufwand durch beim letztgenannten Namen ggfls. vierfachen Suchaufwand, sicherlich nicht überwiegen. Zumal ich bei unterschiedlichen Möglichkeiten der Namensschreibweise ja auch erstmal darauf kommen muss, dass der Name auch in anderer Form korrekt geschrieben sein könnte.

Natürlich ist mir klar, dass ich bei der Umsetzung einer derartigen Implementierung den Rückhalt beim Kunden haben muss. Den verschaffe ich mir zuerst und dann setze ich mich an die Umsetzung. Eine derartige Änderung würde ich nie ohne Einverständnis des Kunden vornehmen. Als "einfacher Programmierer" muss ich mir ggfls. auch bei meinem Team-, Projekt- oder Sonstwasleiter, Vorgesetzten, Chef... das Ok holen.
Aber bei der von mir skizzierten Lösung sind nur neue Tabellen erforderlich, der eigentliche Datenbestand muss nicht verändert werden, die technische Änderung ist (sollte) für den Anwender (und ggfls. die Anwendung) vollkommen transparent sein. Dies liegt allerdings auch an der Art der bisherigen Implementierung. Da mir diese nicht bekannt ist, kann ich hier auch keine sinnvollen und verlässlichen Abschätzungen vornehmen. Aber ausgehend davon, dass ich für die Entwicklung und die Datenbankänderungen einen "Freifahrschein" habe, also absolut selbständig entscheiden kann, sollte das in ein bis zwei Tagen umzusetzen sein. Der Datenbestand muss einmal komplett gelesen und gespeichert werden. Es ist also einmalig die zukünftig zu verwendende Speicherroutine für jeden Datensatz aufzurufen. Bei der angegebene Datenmenge sollte das (eigentlich) kein übermäßiges Laufzeitproblem werden.

Eine Implementierung der Kölner Phonetik für Delphi ist hier zu finden: (https://github.com/chaosben/theunkno...erPhonetik.pas)

[Dejan Vu]

Zitat von jobo:

Zitat von Dejan Vu:

Bequemlichkeit

hat ihren Preis

Zitat von Dejan Vu:

'usability'

Fail bei 3 Minuten Antwortzeit

So so. Du willst mir also Dämlichkeit unterstellen. Nun denn, wäre nicht das erste Mal.
Stell Dir vor, man würde das mit einer (wie ich übrigens schon ausführte ) Volltextsuche kombinieren. Dann wären das eher so um die 0.01 Sekunden Antwortzeit.

Zitat:

Google/Bing sind schnell und die Ergebnisse sind nicht überprüfbar, also viel gebe ich darauf nicht, auch wenn die Handhabung verheißungsvoll ist. (Millionen Fliegen irren ja bekanntlich nicht)

Dann beschäftige dich einfach mal mit Volltextsuche und Rankingalgorithmen.

So, und nun behandeln wir uns wieder mit Respekt und unterstellen dem Anderen weder Dämlichkeit noch fehlendes Knowhow, ok?

So eine Art 'FT für FB' gibt es vielleicht hier: http://www.heise.de/download/firebir...x-1164064.html

Schade, das dieses Feature in FB bisher nicht eingebaut ist.

Thema 'Kölner Phonetik': Ich habe mit einem tokenbasierten Levenshtein sehr gute Ergebnisse erzielt. Es ging um den Abgleich einer Musikdatenbank von 6 Mio Einträgen mit Sendemeldungen aller Rundfunk und Fernsehstationen (ca. 100 Mio). Abzugleichen waren Interpret und Titel. Der Interpret konnte als 'Michael Jackson, 'Jackson, Michael', 'Jakson, Michael' ets geschrieben werden. Problematisch wurde es bei 'P!nk' (ihr offizieller Name ist so), die es als 'Pink' oder auch 'P.I.N.K.' gibt. Das war nicht so einfach.

Die Ergebnisse waren mit der KP vergleichbar.

[nahpets]

@Dejan Vu
Die Levenshteindistanz hat auch schon was, mir ist nur bisher keine Idee gekommen, wie man die sinnvoll in der Datenbank ablegen kann, hier muss man in einer Abfrage konkret zwei Werte mit einander vergleichen, in dem man die Levenshteindistanz berechnen lässt.

Die Kölner Phonetik ermittelt man einmal (pro Wort) und legt sie zusätzlich zum Wort in der Datenbank ab. Für die Eingabe ermittelt man sie ebenfalls und sucht dann in der Datenbank den ermittelten Wert. Da mehrere Worte den gleichen Wert für die Kölner Phonetik haben können, bekommt man hier ggfls. eine höhere Treffermenge.

Bei der Levenshteindistanz müsste man ja dann für 'Michael Jackson' und 'Jackson, Michael' einen Wert ablegen, einen für 'Michael Jackson' und 'Jakson, Michael' ... für alle möglichen Kombinationen von Schreibweisen.
Oder hab' ich hier was nicht verstanden? Die Levenshteindistanz gibt ja letztlich an, wieviele Mutationen erforderlich sind, um aus der Zeichenfolge A die Zeichenfolge B zu erhalten.

Beim dem von Dir beschriebenen Szenario erscheint sie mir aber eine durchaus brauchbare Möglichkeit für die Ähnlichkeitssuche zu sein.
Die Kölner Phonetik würde ja bei den beiden Zeichenfolgen 'Michael Jackson' und 'Jakson, Michael' gnadenlos scheitern, man müsste hier also erst die Zeichenfolgen in Einzelwörter zerlegen und dann nach der "Ähnlichkeit" der Einzelworte suchen und entscheiden, ob's 'ne Übereinstimmung gibt.

Bei dem von Dir beschriebenen (beinahe schon Prosa-)Vergleich dürfte die Levenshteindistanz (zumindest bei einem einmaligen) Vergleich gegenüber den anderen Algorithmen schon fast unschlagbar sein.

Nur aus Neugier: Wie lang hat der Abgleich von den 100 Mio. Sätzen gedauert?