FetchRow Bug, New or Oldstuff?

[4dk2]

hi
ive tried some very simple tests with the latest build 802.
Engine= Mysql 5.1
Mysql Table = 400.000 records, sql= select (first 20 fields) from table

i looked around in the forum and i could only find old stuff from 2005 about fetchrow, so pushed or new

Example:

Code: Select all

var c:cardinal;
begin
  c:=GetTickCount;
  ZQuery1.FetchRow:=400;
  ZQuery1.Active:=true;
  c:=GetTickCount-c;
  Memo3.Lines.Add('ZQuery: '+ IntToStr(c));
end;

this needs over 15 seks to be done.
it looks like the query is getting all the records after setting active=true.
can that be?
ZQuery1 has no connection to any datasource or DB component.
AFTER That!
i tried setting a label like

Code: Select all

  Label1.Caption:=format('%d/%d',[ZQuery1.RecNo,ZQuery1.RecordCount]);

shows that i recordcount is 400 after the start.
pressing control+end in a dbgrid also needs long time again.


greetings Karsten

[mdaems]

Karsten,

I posted this reply in your bug report (http://zeosbugs.firmos.at/view.php?id=224):

Seems like fetchrow is working, at least when it comes to stopping after loading 400 records into the dataset.

The problem may indeed be that all data are downloaded by libmysql before being processed by zeoslib. This is a known issue for mysql servers as the protocol used between client and server forbids sending other statements to the server as long as the data isn't completely fetched. Reading line by line would block all other data retrieval from the server.

Now, you should test the time difference between the end of 'Active=true' and the end of the "Label1.Caption:=format('%d/%d',[ZQuery1.RecNo,ZQuery1.RecordCount]);" line, because that's where the zeoslib fetch happens. I suspect there you will see the effect of FetchRows effectively when changing the property between different runs. If this is the case the functionality of fetchrows is proven to be working correctly I'm afraid.

However, when you know what you're doing, there MAY be a solution for this behaviour by adding 'useresult=true' to the query properties. This is not the default setting, but should instruct libmysql to only fetch the required data. The side effects I can't predict, however. I think one of the conditions for using 'Useresult' is reading to the end of the data in all circumstances.
Seems to me you better use a separate connection for this query object.

Finally, when dealing with this number of records, you must be sure you really need als those rows in your program. Nobody ever scrolls a 400000 line grid manually. So only in batch programs you can need this large datasets. (And even then there's usually a way of pushing the work to the database server) When talking about batch programs it may be more efficient and safer (with regard to useresult) to use the dbc layer directly in your program.

Mark