Problem with RecordCount

[lonely01]

Hi folks, check this out.

Take a "medium" query(~70.000 Records) and try this.

Code: Select all

query.open;
while not query.eof do
begin
    query.next;
end;

On my machine this loop(not the time to open the query) takes 2 seconds.
Then, with only one little modification:

Code: Select all

query.open;
var_x := query.recordcount; //  <=== Only get RecordCount
while not query.eof do
begin
    query.next;
end;

The same procedure now takes 5-10 minutes.
Remove the line with the RecordCount and it is
as fast as before.
Can anybody explain me this strange behavior.

MfG to all,
Sorry for my poor english

[fduenas]

When you call RecordCount TZQuery will bring to the client machine all the records resultig from the server. that what it will slow a little, but only in the line where you call recordcount, after that line all works because all records are now in the client's memory.

For your code there no other solution than removing the recordcount line, because you are doing the sam thing in the while...do statement. So you can use an Inc(Var_X) sentence in the while..do to increment in 1 every time you call query.next.

If you use any DBGrid or any similar component you can prevent tzquery to load all records at start, by setting TZQuery.Filtered := true, even if there is no filter, this will cause the grid not to call recordcount and browse records from the server when nedded.

[mdaems]

Medium query??? Who will ever effectively look at 70000 records in an application?
If you need data crunching, try if you can do it on the server with your query or some stored procedures/temporary tables/...
If you need only the first X records, limit them by your query (eg LIMIT clause for mysql).

And if you realy need all data : look at Fduenas' advice. He knows what he's talking about.

Mark

[fduenas]

Mdaems is right.
Your query is not a small nor medium. 70,000 will surely slow down a lot.
If you need to do searches is better to filter the query or limit the query by Adding a "LIMIT nnn" at the end of SELECT statement, where nnn is the max of records to obtain from the resulting query at the server side.

Now if you need all your record to be loaded while starting you application you can run it in a thread, while doing other things. Just be sure not to acces the query before it browse all the queries. Remember that MySQL client dll is not thread safe. Also Zeoslib is not threadsafe, yet .

The current Implementation for RecordCount is not the best one. The best way is to obtain such info directly from the server API instead of browsing all the records, at least fopr non-filtered TZQueries this could help, but that can be a little dificult beacuse we have to add it to all drivers and we don´t have such info, for MySQL yes, but not for all the others. maybe adding this in the to-do list could help. We have other things to do first.

regards

[chris_had]

RecordCount is always a difficult one to optimise due to the fact you need the records in the client side application before you can get a total (as already said by the devs).

The way I get round this problem is to let the SQL server (in my case MySQL) do the work for me.

I set up a separate "generic" ZQuery which I can use to get recordcount of what ever table/result set i want to process.

For example, if I just wanted to know the total number of rows in an Orders database, I would do the following:-

MyQuery.Close;
MyQuery.SQL.Clear;
MQuery.SQL.Add('Select count(recordID) as totalrecs from Orders');
MyQuery.Open;
MyQuery.First;
iTotalNumberOfRecords := MyQuery.fieldbyname('totalrecs').asInteger;
MyQuery.Close;

The above might look like a long way round to do it, but of course you can wrap it up in a simple function where you pass in a Select statement, and receive back an integer value of recordcount.

Naturally you might want to wrap this up with a Try...Except handler.

I hope that provides you with one possible solution.

Regards,
Chris

[zippo]

I still use the good-old-fashioned "Select Count(*) From TABLE" instead of RecordCount.. (of course, where it is possible to do this).