ZQuery memory problem

[piper62]

Hi,

we have a strange problem.
This code below gets the data from a table which holds ca. 3.5 million records.
-----------------------------------
procedure TForm1.Button1Click(Sender: TObject);
begin
ZQuery1.SQL.Clear;
ZQuery1.SQL.Add('select md_row_type,md_row_txt from md_row_tab');
ZQuery1.Open;
while not ZQuery1.EOF do
begin
ZQuery1.Next;
end; { of while }
end;
-----------------------------------

When I start this test procedure the application eats ca. 6 MByte per second !!!
We work with FPC (2.0.4) and Lazarus 0.9.20 under Debian Linux with MySQL 5.0 database (client and server).

This problem occured in our development when we import data to our new hospital information system.
Now we are really concerned about the memory consumption and the stability of our system if the people really work with the program all the day.

It seems to me that buffers of the ZQuery are not cleared when the .Next method is called.

Please help!

Thanks,
Tibor

[mdaems]

First : FPC has memory issues. AFAIK not too bad for normal use, however.
Second : Query data is fully cached, so all data is read into memory. normal (and good) practice is to limit your queries to the data really needed. I know data migration is a special case... If you have a sequential primary key it shouldn't be too costly to split the result on this key. That way the query result isn't that big constantly. When possible it's better to use mysqldump for this kind of migrations.

Mark

[piper62]

Hi Mark,
FPC is not the problem, I tested that. You have often situations where you need to access a huge amount of data and that's why we use SQL-servers.
I'm also concerned about the daily use of such extensive caching methods in an environment where the application runs all the data and really a lot of data must be processed.
I looked in the forum and the "memory problem" occurs quite often. Is there no possibility to clear the cache or to limit the memory size or to switch it off?
Where can I find it in the source? We'll look for that on our own if there's no solution.

[mdaems]

Hi Piper62,

I do disagree on the point that the application (=client side) should run all the data for day to day use. As you say yourself : that's why you use SQL servers.
If you need to do something on a data item you can ask the server to find it and do an update (unless you manually browse the whole table, select one specific record and change a value in a window). If you need sums, averages,... you ask the server. That's what SQL is invented for.

I have no idea about a switch to turn this caching off.
Concerning the place in the source code where data is cached : look in the dbc layer for the statement and resultset classes. I suppose the TDataset does some caching as well. That's implemented in the component/ZAbstract(RO)Dataset.pas units

Some question : do the mysql connection components from the lazarus standard palette have the same memory problem?

Mark

[piper62]

Hi Mark,
at first thanks for your efforts to help!
I think there was a misunderstanding: Of course the application shouldn't hold the complete data. That's why I'm looking for a method to flush the cache in a ZQuery where I only want to read a resultset record by record.
I tested the "problem" with the SQLdb components and there is the same behaviour.
I solved it at the moment with limiting the resultset via "LIMIT" but I would like to stay database independent...

I looked shortly into the source code and I found that there are TList in use. So, it should be possible to clear the TList to flush the cache. I'll test that...

[piper62]

Hi,
some news. I looked into the source code and the caching is deeply implemented. So, I have no quick solution.
Best would be to limit the cache structure for select queries.
From Delphi I know it this way: Using a Table loads all records but using a Query loads only a limited number of records according to your buffer definitions in the BDE. If the BDE needs more records it loads them and throws the old ones out.
Pretty same behaviour like a DBMS is doing that with the system buffer. Otherwise no DBMS could work any more.
I tried to limit the Rows by deleting the first entry in the list in the ZDbcCachedResultSet.pas and there in the .Fetch method before a new Row is added to the list. But I missed some references in the short time.

If somebody has an idea how we can implement a limited cache please post a message.

Regards,
Tibor

[barko]

First : FPC has memory issues. AFAIK not too bad for normal use, however.

I'm using freepascal on linux (ubuntu) from trunk revision 5191 (this is version 2.1.1) on commercial project with great results (from programming perspective) with latest zeosdbo from testing branch. No memory leaks for me anymore. Ah, yes... firebird is used for database engine. I think that is important information for you guys with memory leak problems.

Thanks for reading this.

EDIT: I was testing freepascal for linux 2.2.1 (latest from svn) and it's ok (no memory leaks too)... so, I'll move on...

[mse]

@piper62:
MSEide+MSEgui has tsqlresult, a resultset with a single record buffer.
Please compile the IDE with -dmse_with_zeoslib to get the ZeosLib components integrated.
http://sourceforge.net/projects/mseide-msegui

Martin

[piper62]

Hello Martin,

thank you for the hint. That's what I meant!
I'll look to it. At the moment we have to finish our project but this is really an important tip.

Reagrds,
Tibor