Problem with ZQuery filter

Forum related to version 6.1.5 of ZeosLib's DBOs

Moderators: gto, cipto_kh, EgonHugeist

Julien DL
Fresh Boarder
Fresh Boarder
Posts: 3
Joined: 18.10.2006, 10:57

Problem with ZQuery filter

Postby 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.

User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Postby 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
Fresh Boarder
Fresh Boarder
Posts: 3
Joined: 18.10.2006, 10:57

Postby 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
Zeos Dev Team
Zeos Dev Team
Posts: 278
Joined: 11.11.2005, 18:35
Location: Porto Alegre / Brasil
Contact:

Postby 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
Use the FU!!!!!IN Google !

gto's Zeos Quick Start Guide

Te Amo Taís!

Julien DL
Fresh Boarder
Fresh Boarder
Posts: 3
Joined: 18.10.2006, 10:57

Postby 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.


Return to “6.1.5”

Who is online

Users browsing this forum: No registered users and 1 guest