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.