SQLQuery eats a LOT of memory

[piper62]

Hi,

I posted this also at the Zeos forum and I hope someboy can help.
Don't know if this a caching problem in Zeos or SQLdb or if it's a FPC problem.
At the Zeos forum a developer gave the hint it might be a FPC/Lazarus problem, so I post it here too.
The code below behaves in the same way with Zeos and with SQLdb.

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

When I start this test procedure the application eats ca. 6 MByte per second !!! :shock:
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 SQLQuery/ZQuery are not cleared when the .Next method is called.

At the moment for the import problem I solved it with "LIMIT" clause in the SQL statement but I expect the database abstraction layer not to read the complete resultset of a query COMPLETE in the memory of the client. In Delphi this behaviour was only for DBTables and not for Queries. There is only a limited buffer space and older records are thrown out as new come in (e.g. the user scrolls through a table).

Has somebody an idea?

Please help!

Thanks,
Tibor

[tatamata]

Hi, Tibor.
I experienced similar memory consumption problems with some procedures intensively iterating through records, especially with nested iterations.
The worst is that memory stays occupied even when all datasets are closed, after procedure finished the job...
Please, let me inform if you find a solution.

Regards,

Zlatko

[Marc]

I'm not surprised if you want to have all 3.5 M records.
Usually when working with databases you try to search specific data by using a where clause and not just fetch all.

[piper62]

Hi Marc,
of course normally we limit our result set in "normal" situations. But the behaviour is not correct to cache everything of an result set. As I wrote (see above) it should be the way that we can limit the cache buffer size.
We have for example big lookup tables or tables for order/entry in the hospital or in large companies, there you need sometimes to grap thousands of records. And believe me it's already a problem if you just scroll through a result set of some thousand.
And as you can see from the code I don't want to have every record on the client! I only want to have one record at one time! And this should be possible! If you do that with Delphi and BDE than you have no problem (see above).

I just downloaded FPC 2.2 and I'll test it with this version.
If somebody has a good idea, I'm looking forward to it.

Regards,
Tibor

[pch]

This is why I use Libsql ( http://sourceforge.net/projects/libsql ) despite sqldb is nice and can connect to more kind of database.

The default behavior is also to buffer all the result but you can do something like that so nothing is buffered :

var DB: TSqlDB;
...
procedure big_batch_process;
begin
DB.CallBackOnly:=true;
DB.OnFetchRow:=@ProcessMyRow;
DB.Query('select * from mybigtable);
DB.CallBackOnly:=false;        
DB.OnFetchRow:=nil;    
end;

Procedure ProcessMyRow(Sender:TObject; Row:TResultRow);
begin
   processdata(row.[0],row.[1],row.[2]);
   ....
end;

This work at least with Sqlite and Mysql.

[ssamayoa]

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

Did you try changing  IsUniDirectional property?

[piper62]

I tried to set the IsUniDirectional property to True. But that changes nothing. Still memory comsuming.