[fpc-pascal] TSQLQuery and buffering.

[Gary Doades]

On Fri, 24 Mar 2017, Gary Doades wrote:
>
>> Really, the question is simple: Is there ANY way of TSQLQuery NOT 
>> reading the entire result set into memory?

> Set Unidirectional to True, and it will keep only 1 row in memory.
> When you are simply scanning through the result set, this is all you need.

Hi Michael,

Many thanks for your reply. I had read about UniDirectional and I have indeed tried this. It doesn't seem to make any significant difference. Looking through the source code for TBufDataset it looks like UniDirectional just turns off building various indexes/structures etc. and fetches the result set all in one go instead of 10 row chunks. It still buffers everything in memory.

I can see this from running a test program. When I call the SQL query .Open method then I can see from Task Manager that it is fetching all rows from the database. My test program reaches about 1.7GB of ram fetching 4.8 million rows.

Only after the complete fetching does the next line of code execute and my processing loop start. Clearly this is looping over the now buffered dataset. The memory is finally freed when the Query's Close method is called.

It may be that UniDirectional is meant to not buffer all rows, but at the moment it certainly seems to. This is FPC 3.0.2 BTW. I've also tried setting ReadOnly to true on the SQL Query.

I Understand about the "normal" use of TSQLQuery and data sets and I also use this for small result set that need the data changing some way and writing back to the DB. What I need now is an equally convenient and powerful way of processing "big data".

Thanks,
Gary.