Problem with ZQuery filter

Julien DL · Post by **Julien DL** » 18.10.2006, 11:22

hello,

Here's a table named "historique" on a mySQL database with 271067 records. My query is a simple "select * from historique" called by a TZQuery instance named ZHistorique. It fills a TDataSource object named dsHistorique which is use as a datasource for a TDBGrid.

Then I try to filter my record on client side like this :

ZHistorique.Filter := 'client = ' + strClient;
ZHistorique.Filtered := true;

"Client" field is an integer, non null, non-unique and considered as an index on both side (declared in IndexFieldNames on client side). I used filters this way on other tables with less records and it works fast and well, but for this one, filtering is very slow (several minutes). I noticed that if the TDataSource is not declared as datasource for the TDBGrid, first attempt of filtering is fast enough (few seconds), but on new one, affectation on Filter property lasts too long again.

Is my table too voluminous for that use ?

zeosdbo 6.1.5-stable, unpatched, with Delphi 7.

Waiting for your help, thank you.

Post by **mdaems** » 18.10.2006, 13:29

Well, it's a lot of data indeed. I don't know how much rows (% of total) you have after filtering, but if that's only 20 % or less it would help a lot when you add the filter to the query, so the server doesn't have to send the data to your program anyway. Close the query, alter the sql statement (or work with parameters) and open the query. Now the server will do the work.

Mark

Julien DL · Post by **Julien DL** » 18.10.2006, 15:27

Thank you for your answer. I already thought about such a solution and I keep it as a "last chance solution".

What's upsetting me with the case i describe is that the first filtering operation may be quite fast, but any further modification of the Filter (or even Filtered) property take more time than the previous. It's likely index on "Client" column is lost. So I wonder if it's a normal behaviour with so many records, a bug or a misuse of me.

Julien.

gto · Post by **gto** » 18.10.2006, 15:35

The best way is always to limit the query result on server side. But, as a quick 'n dirty fix, you may try this where you set the filter:

Code: Select all

ZHistorique.DisableControls;
try
   ZHistorique.Filtered := false; 
   ZHistorique.Filter := 'client = ' + strClient;
   ZHistorique.Filtered := true;
finally
   ZHistorique.EnableControls;
end;

It may work, or not, since I've never used filters on such big queries.
[]'s

Julien DL · Post by **Julien DL** » 18.10.2006, 16:02

I tried such a fix, but the problem still exists.

So I'd rather use a more classical solution.

Thank you both for your help.