Memory Consumption

[JensFudge]

Hi,
I am currently working for a customer who has a TUniQuery retrieving data from a MS SQLServer database.
Once the data is received from the database, the application stuffs all the data into a list of delphirecords.

Basically this is what is being done:
UniQuery.open; //This fetches half a million records with 150 fields from the database, and allocates 750MB memory
while not UniQuery.Eof do
begin
aDelphiRecord.Value0 := UniQuery.Fields[0].value;
aDelphiRecord.Value1 := UniQuery.Fields[1].value;
...
aDelphiRecord.Value150 := UniQuery.Fields[150].value;
addToList(aDelphiRecord);
UniQuery.next;
end;
Now I have the data in memory twice. In my list of records, and in my UniQuery instance. I want to get rid of the data in the UniQuery instance:
UniQuery.Close; //I would expect this to deallocate the 750 MB memory that was allocated in Uq.open;

However, the total memory usage decreases with absolutely nothing when I close the query. I have also tried UniQuery.Free; which again doesn't give me my 750 MB memory.

I have gotten a few suggestions like Fetchall, UniDirectional, FetchRows and so on, which all have no affect on the memory not being released back to Windows when the UniQuery is closed.

I have also read quite a few posts on Stack Overflow and other forums. So just to save time, I will put some "standard" questions for this and similar problems, along with my answers:

Q1) Can you limit the number of records and/or fields, surely you dont need all half million records and all 150 fields:
A1) I have limited it to half a million records, the table holds 300 million records, I only need the half million, but I do need all the half million. I also need all the fields.

Q2) Call disableControls while fetching data
A2) The query is not hooked to anything in the UI

Q3) Setting the FetchAll to false and the FetchRows to 1000 will let you retreive only 1000 records at a time
A3) But traversing the half million records will fetch the next 1000 and the next 1000 until all records are fetched, which makes no difference what so ever.

So, the question remains:
How do I clear up the memory (750 MB) that the actual data is consuming?

Thanks in advance
Kind Regards
Jens Fudge

[invent]

Hello Jens,

I just tested this behaviour in my software. I opened a big table with TUniQuery. The memory raised from 15 MB up to 552 MB. After TUniQuery.close the memory was back to 15 MB.

All properties of TUniConnection and TUniQuery are the default settings. Maybe you changed one property to the "bad side".

Kind regards,
Gerd Brinkmann
invent GmbH

[azyk]

We cannot reproduce the specified issue. Please compose a small test project in which the memory allocated by dataset is not released when calling Close or Free and send us using the contact form on our site: https://www.devart.com/company/contactform.html . In the sample include also scripts to create test tables and filling them with test data.

[JensFudge]

Hi,

I cannot reproduce the problem either when I create a blank small project trying to reproduce.
So I guess that the code somewhere has one or more props set to keep the memory consumption.
I have however made an acceptable workaround by setting the Fetchall to false, fetchrows to 1000 and uniDirectional to true, the memory consumption is 2 MB and it stays at 2 MB

Thanks for great components though

Cheers
Jens Fudge