Performante SQL- Abfrage (LIKE %)

[Generic1]

Hallo,

"fassy" hat mir mal bei einem Thread folgendes geschrieben:

>> '%Hans%'
>> ist immer eine ganz schlechte Idee. Wenn du das Pattern mit einem Wildcard anfängst kann die >> Datenbank keine Indexstrukturen nutzen und macht einen Fulltablescan - sprich die Performance >> geht in den Keller.

Ich brauche aber eine Abfrage in der abgefragt wird, ob z.B. ein Vorname Hans enthält.
Wie würdet ihr dann das machen?

Meine Abfrage schaut momentan so aus:

public List<Participant> searchParticipant(final String name) {
            final String searchQuery = " from " + PARTICIPANT + " as p where p.firstname LIKE '%" + name + 
                                       "%' OR p.surname LIKE '%" + name +
                                       "%' OR p.address.city LIKE '%" + name + 
                                       "%' OR p.club.clubname LIKE '%" + name + "%')";
            return getHibernateTemplate().find(searchQuery);
            }

Vielen Dank und lg

 

[The_S]

Naja, wenn "Hans" nunmal irgendwo auftreten kann, dann musste auch so suchen. Alternativ kannste auch nach allen suchen, die mit Hans Anfangen und dann selbst die Liste noch filtern - ob das aber so performanter ist, wage ich zu bezweifeln. Hast du denn Performanceprobleme?

Außerdem solltest du deinen SQL nicht mit + zusammenbauen, sondern Parameter über die entsprechenden Methoden der Query (!?, habe Hibernate jetzt schon lange nicht mehr verwendet) Klasse setzen.

 

[ARadauer]

Performance >> geht in den Keller.

ist relativ... was will man den machen? gewisse dinge dauern halt.

Indizes sind manchmal eine Lösung. Leg mal einen Index über die Spalte und schau ob dir das was bring und ob die einfüge Zeiten nicht zu hoch werden.
Alternativ könnte mannoch eine externes System für die Suche verwenden zb lucene, die frage ist halt ob das nicht zu viel ist...

 

[Generic1]

Also momentan hab ich keine Performance- Probleme, der Kommentar von fassy hat mich aber zum Nachdenken gebracht.

Weiß jemand wie das geht mit einer Hibernate- Query, dass ich dort wo Parameter hinkommen ein ? setzt und mit einer Parameterliste ausfülle?

public List<Participant> searchParticipant(final String name) {
            final String searchQuery = " from " + PARTICIPANT + " as p where p.firstname LIKE '%" + name + 
                                       "%' OR p.surname LIKE '%" + name +
                                       "%' OR p.address.city LIKE '%" + name + 
                                       "%' OR p.club.clubname LIKE '%" + name + "%')";
            return getHibernateTemplate().find(searchQuery);
            }

 

[The_S]

Erst einmal musst du mit Sicherheit deinen String in der entsprechenden Query-Klasse verpacken. Anschließend solltest du einfach via setXYZ die Parameter setzen können. Ich empfehle: hibernate preparedstatements - Google-Suche

 

[Marcinek]

Diese nennt man Hostvariablen

Es ist mit hibernate auch möglich Textmarken zu setzten.

where name = :name

Dann kann man aus dem SQL String ein SQLQuery erzeugen und mit

setPropertie ("name", object) ersetzten.

Ich würde in suchen niemans hardcoded % im präfix benutzen, da du daruch keine indizes ausnutzen kannst.

Lass das den User mit wildcards selbst bestimmen...

In unseren Anwendungen wird führendes * garnicht für das Suchen erlaubt.

 

[Gast2]

Das Problem ist das du eine "Volltext" Suche machen willst. Das ist in Datenbanken normalerweise keine gute Sache, dafür sind relationale Modelle nicht geeignet. Normalerweise würd ich dein Problem so lösen:

FirstName                | LastName | some other columns
--------------------------------------------------------
Klaus-Hans-Dieter        | Schulz   | ...

Diese Tabelle aufteilen in entweder:

FirstName | MiddleName1 | MiddleName2 | LastName | some other columns
---------------------------------------------------------------------
Klaus     | Hans        | Dieter      | Schulz   | ...

Oder evtl noch normalisieren und eine Tabelle mit möglichen Vornamen sammeln.

Dann im Query:

WHERE (FirstName = 'Hans' OR MiddleName1 = 'Hans' OR MiddleName2 = 'Hans')

So kann due Datenbank mögliche Indexe und Optimierung voll ausnutzen.

 

[KSG9|sebastian]

Man sollte das schon je nach Anwendung unterscheiden.

Für eine kleine App mit ein paar hundert Zeilen in der Tabelle ist es völlig egal. Meistens ist es da sogar günstiger ohne Indizes zu arbeiten (siehe Problematik beim Update/Insert). Einige Datenbanken machen bei so kleinen Datenmengen sowieso einen Fulltablescan.

Selbst bei ein paar tausend Sätzen dürfte das nicht ins Gewicht fallen.

Somit sollte erst mal geklärt werden von welchen Datenmengen wir da reden...

 

[Gast2]

Man sollte das schon je nach Anwendung unterscheiden.

Für eine kleine App mit ein paar hundert Zeilen in der Tabelle ist es völlig egal. Meistens ist es da sogar günstiger ohne Indizes zu arbeiten (siehe Problematik beim Update/Insert). Einige Datenbanken machen bei so kleinen Datenmengen sowieso einen Fulltablescan.

Selbst bei ein paar tausend Sätzen dürfte das nicht ins Gewicht fallen.

Somit sollte erst mal geklärt werden von welchen Datenmengen wir da reden...

Da geb ich dir recht - aber oftmals ist es so das Leute grade in der Ausbildung oder im Studium mit kleinen überschaubaren Szenarien arbeiten und sich keine weitere Gedanken machen. Später wird das dann 1:1 auf large-scale Enterprise Systeme übertragen. Ich hab schon sehr viele große Datenbanken gesehn wo jemand mit [c]LIKE '%XYZ'[/c] drauf losgegangen ist und sich wunderte das die Queries eine Stunde hängen. Was wurde gemacht? Richtig - neue stärkere Hardware gekauft anstatt einmal den Query Execution / Explain Plan anzugucken

 

[fastjack]

1. Möglichkeit die mir einfällt:

Probier doch mal aus, alle Spalten die durchsucht werden sollen, zu konkatenieren (CONCAT....) und dann ein einziges Like anzuwenden.

MySQL ungefähr so:

select a, b, c, concat(a, b, c) as search from d where search like '%Hans%'

Es kann sein, das Du ein wenig casten oder konvertieren mußt, ich kenne Suchen, die so recht schnell arbeiten, auch bei größeren Datenbeständen.

2. Möglichkeit:

Baue ein spezielles Suchfeld in die Tabelle ein, daß beim Schreiben direkt mit konkatenierten Daten anderer Suchspalten mitgeschrieben wird. Also z.B.

Tabelle X,
Spalten a, b, c
+ spezielle Suchspalte search

bei Inserts/Updates wird die Suchspalte mit a, b, c gefüttert

--> select a, b, c from X where search like '%Hans%';

 

[tfa]

Bevor man wild drauf los frickelt, sollte man sich eine bewährte Lösung ansehen. Das Zauberwort hat ARadauer doch schon gesagt: lucene.
Ansonsten bin ich der Meinung, dass man Probleme, die man (noch) gar nicht hat, auch nicht zu lösen versuchen sollte. Das gilt insbesondere für Performance-Optimierung.

 

[KSG9|sebastian]

Da geb ich dir recht - aber oftmals ist es so das Leute grade in der Ausbildung oder im Studium mit kleinen überschaubaren Szenarien arbeiten und sich keine weitere Gedanken machen. Später wird das dann 1:1 auf large-scale Enterprise Systeme übertragen. Ich hab schon sehr viele große Datenbanken gesehn wo jemand mit [c]LIKE '%XYZ'[/c] drauf losgegangen ist und sich wunderte das die Queries eine Stunde hängen. Was wurde gemacht? Richtig - neue stärkere Hardware gekauft anstatt einmal den Query Execution / Explain Plan anzugucken

Hi,

die Probleme kenne ich auch. Man sollte sich schon Gedanken machen und auch im Hinterkopf behalten das solche Lösungen eben nicht skalieren und der DB-Admin einem den Kopf abreist wenn er nen Explainplan anschaut.

 

[JohannisderKaeufer]

1. Möglichkeit die mir einfällt:

Probier doch mal aus, alle Spalten die durchsucht werden sollen, zu konkatenieren (CONCAT....) und dann ein einziges Like anzuwenden.

MySQL ungefähr so:

select a, b, c, concat(a, b, c) as search from d where search like '%Hans%'

Es kann sein, das Du ein wenig casten oder konvertieren mußt, ich kenne Suchen, die so recht schnell arbeiten, auch bei größeren Datenbeständen.

Könnte das dann nicht auch einen Johan Schulz, als joHANSchulz liefern. Obwohl das eigentlich nichts mehr mit Hans zu tun hat.

 

[fastjack]

Das kann durchaus sein, hast Du Dir mal den Queryaufbau von generic angesehen? Aber ich glaube ob die Prozentzeichen jetzt so sind oder anders, das sollte dem Beispiel latte sein, es geht nur um den Aufbau.

 

[SlaterB]

> Könnte das dann nicht auch einen Johan Schulz, als joHANSchulz liefern.

Leerzeichen beim concat() einfügen, falls das eine Frage und nicht nur ein Hinweis war

 

[Gast2]

Das Like ist ja case sensitiv 'hanS' würde auf '%Hans%' nicht matchen.

 

[Marcinek]

Das Like ist ja case sensitiv 'hanS' würde auf '%Hans%' nicht matchen.

Bedingt richtig.

Hängt vom Datentyp ab und Datenbank

 

[Gast2]

Es gibt RDBMS die case insensitives like implementieren?

 

[Marcinek]

Ja klar.

MYSQL mit dem entsprechenden Zeichencodierung für einen varchar Feld. Müsste ich jetzt bei schauen.

Da wir in neuen Projekten mittlerweise spezielle Felder zur ablage von Suchstrings haben, kann ich garnicht aus dem Kopf sagen, wie sich Informix oder DB2 verhällt.

 

[JohannisderKaeufer]

Ob Casesensitive oder nicht ist ja egal.
Gerade bei Suchanfragen die Benutzer eingeben, stellt man oft selbst Suchstring und das zu vergleichende Objekt auf lowercase oder uppercase um eine Casesensitivität zu vermeiden.
Das ist hier zwar noch nicht der Fall, kann aber schon im nächsten Schritt hinzukommen.

Das mit dem concat und dem Beispiel, habe ich eigentlich nur eingebracht, um zu verdeutlichen das das auch ganz unerwartete Auswirkungen haben kann.


BTW. gibt es auch "Ähnlichkeitssuchen", weiß aber nicht mehr wie das genau heißt. Sucht man damit nach Meier werden auch Meir, Mair, Maier, Mayer, Meyer, Meyr, Mayr etc. gefunden. Das geht dann schon über die Frage der Casesensitivität hinaus.

 

[Gast2]

Meinst du Soundex ? Wikipedia

 

[Gast2]

Soweit ich den Usecase verstanden habe, geht es darum, Teilnamen zu finden. Mein Ansatz wäre folgender (vorausgesetzt die Datenmenge ist gross genug, um dies zu rechtfertigen und der Name besitzt klare Delimiter wie "-" oder " "):

neben der Usertabelle existiert eine Lookup-Tabelle:
id, lookup_name, id_user
...lookup_name ist indiziert und id_user ein Fremdschlüssel der Usertabelle.

Mit einem after_insert trigger auf der Usertabelle, könnte man den Usernamen in seine logischen Teilstrings zerlegen und für jeden einen Lookup-Entry generieren vordefiniert in upper oder lower case.

Das ganze macht natürlich nur Sinn bei entsprechend vielen Datensätzen und einer sehr häufig ausgeführten Abfrage, die vom Muster auch nicht abweicht. Sobald man hier wieder in die Verlegung kommt, mit LIKE abzufragen, wird dies noch unperformanter, da zu jedem User-Eintrag mindestens ein Lookup-Eintrag exisiteren würde.