UniQuery cannot handle large tables

[kneighbour]

I have been using Uni components for years and currently am on Version 9.2.1 and in Delphi XE7 (32 bit) and Firebird 3.

If I setup a connection to a Firebird DB and then setup any UniQuery component to run a query of the type

"SELECT * FROM sometable" on a table with something in the order of 5 million records (or more). It does not matter what the table is - it is the number of records in that table.

and then I do something like
qryAction.sql.text := 'SELECT * FROM CUSTOMERS';
qryAction.ReadOnly:=true;
qryAction.open;
qryAction.first;
while not qryAction.EOF do
qryAction.next;

This will fail - in my case this seems to fail at around record 4,286,250. I get an 'Out of Memory' error.

I normally never do anything like this of course. I only came across this problem as I am trying to export all of the records in a table to a CSV file, hence I have to loop through all records. But it is not anything to do with the export stuff - I can just run the empty loop above and the thing will fail.

The UniQuery does not have any special properties set. Just drop the default on a form and run this simple query.

Any suggestions?

[frickler]

Did you try compiling this as a 64 bit program? Other suggestion: use ROWS, eg. SELECT * FROM foo ROWS 1 TO 100000, and so on.

[ertank]

Hello,

Your query reads all records in memory as you use qryAction.next
UniDAC has feature called UniDirectional keeping only single record in memory.
UniDirectional query can only use Next. Hence the name.

If all you need to walk in all records, you can use UniDirectional as below

Code: Select all

qryAction.sql.text := 'SELECT * FROM CUSTOMERS';
qryAction.ReadOnly:=true;
qryAction.UniDirectional:=true;
qryAction.open;
//qryAction.first;  -- no need for this line
while not qryAction.EOF do
qryAction.next;

You can find additional information about UniDirectional here
https://docs.devart.com/unidac/devart.d ... tional.htm

[kneighbour]

Thank you - very useful suggestion.