Performanceproblem parambyname und sql like

[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 von baumina:

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?