![]() |
Datenbank: Firebird • Version: 2.5 • Zugriff über: dll
Performanceproblem parambyname und sql like
Hallo zusammen.
Nach einigen herumprobieren habe ich festgestellt, dass es ein extremes Performanceproblem bei folgendem Code gibt.
Delphi-Quellcode:
Bei einer Tabelle von 9 Millionen Einträgen dauert die Suchanfrage 20 Sekunden. Wobei ca 100 Einträge gefunden werden.
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; Ändere ich den Code wie folgt ab
Delphi-Quellcode:
Dann beträgt die Antwortzeit 0.2 Sekunden.
q.sql.text:='select * from PROTOKOLL where PRO_KEY like '''+suche+'%'' ';
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 |
AW: Performanceproblem parambyname und sql like
Sicher, dass der Cache bei der Änderung (Versuch ohne Parameter) nicht zuschlägt?
Welche Zugriffskomponenten verwendest du? |
AW: Performanceproblem parambyname und sql like
Entfernt, das war genau das gleiche wie Morphie vor mir schrieb.
|
AW: Performanceproblem parambyname und sql like
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 |
AW: Performanceproblem parambyname und sql like
Na AnyDAC, IBDAC/UniDAC, Zeos, FIBPlus und was es da sonst noch so alles gibt...
|
AW: Performanceproblem parambyname und sql like
Die ganz normalen IB Komponenten, welche in Delphi vorhanden sind.
PS : Mit Delphi 6 und XE4 getestet. Gleiches Resultat. |
AW: Performanceproblem parambyname und sql like
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 |
AW: Performanceproblem parambyname und sql like
Ansonsten einfach mal den Plan anzeigen lassen...
edit: zu spät |
AW: Performanceproblem parambyname und sql like
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:
Davon das Ergebnis
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; 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 |
AW: Performanceproblem parambyname und sql like
'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. |
AW: Performanceproblem parambyname und sql like
Würde denn ein q.prepare vor dem q.open etwas ändern?
|
AW: Performanceproblem parambyname und sql like
Zitat:
|
AW: Performanceproblem parambyname und sql like
Wenn man testweise auf Parameter verzichten würde, sollte das Ergebnis zufriedenstellend sein, oder?
|
AW: Performanceproblem parambyname und sql like
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.
|
AW: Performanceproblem parambyname und sql like
Bitte berichte über den Ausgang! Danke!
|
AW: Performanceproblem parambyname und sql like
Das "like" hebelt alle Indexe aus.
|
AW: Performanceproblem parambyname und sql like
Zitat:
|
AW: Performanceproblem parambyname und sql like
Aber in welcher Unit findet denn diese mysteriöse "Optimierung" statt?
btw: Firebird 2.5 stimmt? denn: ![]() |
AW: Performanceproblem parambyname und sql like
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.
|
AW: Performanceproblem parambyname und sql like
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 |
AW: Performanceproblem parambyname und sql like
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. |
AW: Performanceproblem parambyname und sql like
Man könnte auch das Like durch STARTING ersetzten, dann klappt es auch mit dem Parameter
|
AW: Performanceproblem parambyname und sql like
das wäre ja dann so was : "%Suche". Jo, könnte besser sein.
Problem ist jedenfalls das LIKE. |
AW: Performanceproblem parambyname und sql like
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.
|
AW: Performanceproblem parambyname und sql like
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... |
AW: Performanceproblem parambyname und sql like
Zitat:
|
AW: Performanceproblem parambyname und sql like
Meine ich doch ;-)
|
AW: Performanceproblem parambyname und sql like
Zitat:
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 :) |
AW: Performanceproblem parambyname und sql like
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.
|
AW: Performanceproblem parambyname und sql like
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 ;-) |
AW: Performanceproblem parambyname und sql like
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. |
Alle Zeitangaben in WEZ +1. Es ist jetzt 22:50 Uhr. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
LinkBacks Enabled by vBSEO © 2011, Crawlability, Inc.
Delphi-PRAXiS (c) 2002 - 2023 by Daniel R. Wolf, 2024 by Thomas Breitkreuz