Performanceproblem parambyname und sql like

[stalkingwolf]

Hallo zusammen.

Nach einigen herumprobieren habe ich festgestellt, dass es ein extremes Performanceproblem bei folgendem Code gibt.

Delphi-Quellcode:

q := TIBquery.create(nil);
q.database:=mymightydatabase;
q.sql.text:='select * from PROTOKOLL where PRO_KEY like :KEY';
q.parambyname('KEY').asstring := suche+'%';
q.open // 20 Sekunden
.
.
q.close;
q.free;

Bei einer Tabelle von 9 Millionen Einträgen dauert die Suchanfrage 20 Sekunden. Wobei ca 100 Einträge gefunden werden.

Ändere ich den Code wie folgt ab

Delphi-Quellcode:

q.sql.text:='select * from PROTOKOLL where PRO_KEY like '''+suche+'%'' ';

Dann beträgt die Antwortzeit 0.2 Sekunden.

Was ist kaputt? Was übersehe ich?
Sogar hier in dem Forum wird die obere Schreibweise in einem Beitrag vorgeschlagen. Es tritt auch nur im Zusammenhang mit like auf. ändere ich like auf = ( und nehme das % raus ) ist das Performanceproblem auch weg.

MFG
Sven

[Morphie]

Sicher, dass der Cache bei der Änderung (Versuch ohne Parameter) nicht zuschlägt?
Welche Zugriffskomponenten verwendest du?

[jobo]

Entfernt, das war genau das gleiche wie Morphie vor mir schrieb.

[stalkingwolf]

Nein der schlägt nicht zu, weil ich das Programm auf das System unsere Kunden übertrage und dort neu starte.
Und ich die Anfrage nach diversen Suchkriterien überprüft habe.

Ich bin darauf gestoßen, weil wir auch eine Web Anwendung haben und dort per Firebirdmodul in PHP das gleiche SQL auch nur 0.2 Sekunden benötigt.
Auch die Anfrage in Flamerobin dauert nur 0.2s.

Bei kleineren Datenbanken fällt das gar nicht so extrem auf. Aber die Test hier sind mit einer Tabelle gemacht worden, welche 9 Millionen Einträge hat.

Was genau verstehst du unter Zugriffskomponenten? Wir nutzen die gds32.dll

[Morphie]

Na AnyDAC, IBDAC/UniDAC, Zeos, FIBPlus und was es da sonst noch so alles gibt...

[stalkingwolf]

Die ganz normalen IB Komponenten, welche in Delphi vorhanden sind.

PS : Mit Delphi 6 und XE4 getestet. Gleiches Resultat.

[hstreicher]

bei 9 Mio eintraegen in 0,2 Sekunden wird wahrscheinlich ein Index verwendet
bei den 20 Sekunden tippe ich auf einen Fulltablescan

versuchs doch mal wie folgt

Ohne das % beim Suchbegriff
um das Query wie folgt

'select * from PROTOKOLL where PRO_KEY starting with :KEY'

oder
das ganze mal in einem DB Tool wie Flamerobin, IBExpert, DB Workbench u.s.w. ausprobiert und den Plan anzeigen lassen
wie die Abfrage umgesetzt wird

[Morphie]

Ansonsten einfach mal den Plan anzeigen lassen...

edit: zu spät

[stalkingwolf]

starting with liegt auch bei 0.2s.

Wie schon oben geschrieben, das Problem liegt in der Kombination like und parambyname.
Und natürlich ist ein Index auf dem Such-, wie auch auf dem Sortierfeld. Dennoch dürfte die Kombination like+parambyname nicht so extrem aus der Reihe fallen.

Und mich würde interessieren warum dem so ist.

Ich habe ein kleines Test mit einer 7 Millionen Datensatztabelle gemacht.
Nun also ohne irgendwelche externen Zugriffe über andere andere Tools/Schnittstellen.

Delphi-Quellcode:

procedure Tfmain.Button1Click(Sender: TObject);
var q : TIBQuery;
    t : integer;
begin
    db.DatabaseName:=eddatabasename.Text;
    try
        db.Connected:=TRUE;
    except
        on e : exception do begin
            messagedlg(e.Message,mterror,[mbok],0);
            exit;
        end;
    end;
    q := TIBQuery.create(nil);
    try
        q.unidirectional:=TRUE;
        q.Database := db;
        for t := 1 to 3 do begin
            q.sql.text := '';
            q.sql.add('select * from PROTOKOLL where ');
            if t = 1 then q.sql.add('KEY like '''+edsuche.text+'%''');
            if t = 2 then q.sql.add('KEY starting with :KEY ');
            if t = 3 then q.sql.add('KEY like :KEY ');
            q.sql.Add(' order by SNR desc');
            if t = 2 then q.ParamByName('KEY').AsString := edsuche.text;
            if t = 3 then q.ParamByName('KEY').AsString := edsuche.text+'%';

            memo1.lines.add(format('Test %d : Davor  : %s',[t,formatdatetime('hh:nn:ss:zzz',now)]));
            q.open;
            memo1.lines.add(format('Test %d : Danach : %s',[t,formatdatetime('hh:nn:ss:zzz',now)]));
            q.close;
        end;
    finally
        q.free;
    end;
    db.Connected:=FALSE;
end;

Davon das Ergebnis
Test 1 : Davor : 12:44:42:138
Test 1 : Danach : 12:44:42:185

Test 2 : Davor : 12:44:42:192
Test 2 : Danach : 12:44:42:195

Test 3 : Davor : 12:44:42:196
Test 3 : Danach : 12:44:54:066

[Blup]

'KEY starting with :KEY '
Vergleicht immer den Text am Anfang des Feldes, das heist der Index kann benutzt werden.

'KEY like '''+edsuche.text+'%'''
Wird vermutlich beim Prepare zur Optimierung durch starting with ersetzt, damit greift der Index.

'KEY like :KEY '
Hier ist beim Prepare noch nichts über die Suchbedingung bekannt, diese könnte auch so ausschaun '%123%abc%'.
Deshalb kann der Index nicht benutzt werden und die komplette Tabelle wird durchsucht.

[baumina]

Würde denn ein q.prepare vor dem q.open etwas ändern?

[mkinzler]

Zitat:

Zitat von baumina (Beitrag 1231589)

Würde denn ein q.prepare vor dem q.open etwas ändern?

Nein denn der Prepare wird implizit ausgeführt.

[Furtbichler]

Wenn man testweise auf Parameter verzichten würde, sollte das Ergebnis zufriedenstellend sein, oder?

[baumina]

Also wenn das alles auch bei mir mit UniDAC und mysql zutreffen sollte (ich muss das wohl mal testen), werde ich mich dran setzen und alle Parameter-Geschichten rauswerfen.

[Lemmy]

Bitte berichte über den Ausgang! Danke!

[Hansa]

Das "like" hebelt alle Indexe aus.

[Furtbichler]

Zitat:

Zitat von Hansa (Beitrag 1231598)

Das "like" hebelt alle Indexe aus.

Dann wäre FB aber ziemlich blöd.

[Morphie]

Aber in welcher Unit findet denn diese mysteriöse "Optimierung" statt?
btw: Firebird 2.5 stimmt?
denn: http://www.firebirdfaq.org/faq308/

[mkinzler]

Die Optimierung hat mit dem Zeitpunkt der Planbildung zu tun. Beim Absetzen der Abfrage ohne Parameter ist bekannt, das das LIKE durch ein STARTING ersetzt werden kann. Bei der Verwendung von Parametern nicht, das ja der/die Wildcard(s) im Parameter stecken.

[stalkingwolf]

klingt plausible.
Wir konnten es auch nur bei like feststellen. Bei key = :key gab es keine Performanceprobleme. Man muss also nicht alle Programm ändern.

Wegen der 2.5 FB Frage. Laut Whatsnew v2.5.1

[Morphie]

Stimmt... Wobei es dann Verbesserungspotential bei Firebird gibt.
Klar, wenn die Abfrage Parameter verwendet, kann Firebird nicht wissen, ob der Index möglicherweise doch benutzt werden kann (im Falle von "Text%")
Aber spätestens wenn der Parameter übergeben wird, weiß Firebird doch was zutun ist.

Natürlich müsste Firebird dafür den Ausführungsplan neu aufbauen, aber das wäre mit Sicherheit schneller als einen Fulltablescan zu fahren.
Wird der Parameter ersetzt, muss der Ausführungsplan ebenfalls neu aufgebaut werden.

[mkinzler]

Man könnte auch das Like durch STARTING ersetzten, dann klappt es auch mit dem Parameter

[Hansa]

das wäre ja dann so was : "%Suche". Jo, könnte besser sein.
Problem ist jedenfalls das LIKE.

[DeddyH]

Nee, sowas wäre das nicht, das % gehört ans Ende. Mich verwundert, dass FB nicht in der Lage sein soll, zu erkennen, ob es bei LIKE einen Index benutzen kann oder nicht. Bei Wildcards am Ende ließe sich ein ggf. vorhandender Index ja sehr wohl benutzen.

[Morphie]

Wie wir eben gelernt haben, tritt das nur bei Parametern auf. Sonst ist Firebird sehr wohl in der Lage einen Index bei Like zu verwenden.
Das Problem bei Parametern ist, dass Firebird beim Prepare ggf. noch keine Parameter kennt. Erst wenn ein Parameter gesendet wird, KÖNNTE Firebird darauf reagieren. Dazu müsste Firebird den Parameter aber auch analysieren...
Und dann ist es auch möglich, weitere Parameter abzuschicken, die das selbe Prepared-Statement verwenden. Das Prepare müsste also erneut durchgeführt werden. (Und somit den Ausführungsplan erneut aufbauen) Das würde die Sache mit dem Prepared-Statements aber wieder sinnlos machen...

Wie ich schon schrieb: Das ginge in diesem Fall zwar sicher schneller als der FullTableScan, aber ggf. ist es in anderen Szenarien nicht erwünscht...

[mkinzler]

Zitat:

Das Problem bei Parametern ist, dass Firebird beim Prepare ggf. noch keine Parameter kennt.

Die Parameter schon, aber deren Inhalt nicht.

[Morphie]

Meine ich doch ;-)

[hstreicher]

Zitat:

Zitat von Morphie (Beitrag 1231606)

Stimmt... Wobei es dann Verbesserungspotential bei Firebird gibt.
Klar, wenn die Abfrage Parameter verwendet, kann Firebird nicht wissen, ob der Index möglicherweise doch benutzt werden kann (im Falle von "Text%")
Aber spätestens wenn der Parameter übergeben wird, weiß Firebird doch was zutun ist.

Natürlich müsste Firebird dafür den Ausführungsplan neu aufbauen, aber das wäre mit Sicherheit schneller als einen Fulltablescan zu fahren.
Wird der Parameter ersetzt, muss der Ausführungsplan ebenfalls neu aufgebaut werden.

Das wiederspricht aber dem Konzept des Prepared Queries, das ja _gerade_ durch den vorbereiteten Plan (meist) schneller ist
also einfach die Prepared Queries so lassen , und im Zweifellsfall das normale Query verwenden
man kann nicht erwarten dass jedes Werkzeug für jede Aufgabe optimal ist.

Das heisst dass für uns Programmierer noch etwas Arbeit übrig bleibt , und uns die Computer nicht so schnell ersetzen werden :)

[Union]

Sehr interessantes Thema. Ich habe aber noch eine Anmerkung zu der Testroutine: Da die Daten in der ersten Abfrage ja im Cache landen, hat man nicht mehr die gleichen Bedingungen bei den beiden weiteren Tests. Für eine exakte Messung müsste man den Service jeweils neu starten.

[stalkingwolf]

Ich habe die Routinen bei mir auch einmal umgedreht und 1 vor 2 gesetzt. Das Resultat war immer identisch. Dabei lieferte "starting with" immer die beste Performance.

Aber ja sonst hast du recht. Allerdings ist bei meiner Testumgebung mit so vielen Daten es irrelevant ;-)

[tsteinmaurer]

Grundsätzlich kann Firebird einen Index bei z.B. LIKE 'Test' bzw. LIKE 'Test%' verwenden, allerdings nicht bei einem Prepared Statement.

Beim Prepare erstellt Firebird unter anderem den Ausführungsplan. Da zu diesem Zeitpunkt noch nicht bekannt ist welche Parameterwerte daherkommen können, was bei einem LIKE alles bzgl. Platzhalter (% ...) sein kann, wird Firebird dafür nie einen Index verwenden können.

Anders sieht es aus, wenn man STARTING WITH anstatt von LIKE in einem Prepared Statement verwendet. Mit dieser Klausel ist für Firebird sichergestellt, dass keine Platzhalter daherkommen können. Somit wird im Ausführungsplan auch ein Index aufscheinen, sofern natürlich einer für das Feld vorhanden ist.