Query Performance

[barnie00]

Hello!

I have a Firebird Database with 65000 Rows.
When I try to filter the data with

Code: Select all

Query->Filter="ID like '%2007%'";
Query->Filtered=true;

it takes 30 seconds when I do it for the first time.
A second Filter with another value like

Code: Select all

Query->Filter="ID like '%2008%'";
Query->Filtered=true;

takes less than 1 second.

Is it possible to improve the performance in the first case?
Thanks.

[Plash]

When you set the Filter property, sorting is performed locally on the client. So that all rows from the table are fetched when you set the Filter property first time.
If you set the FetchAll property to True, all rows are fetched on opening the query. In that case opening takes long time, but filtering is fast.

To improve performance, you should use server side filtering by setting WHERE clause in your query and reopening it. You can use AddWhere and DeleteWhere methods of TIBCQuery to add and remove WHERE clause automatically.