[fpc-pascal] TSQLQuery and buffering.

[Gary Doades]

Hi everyone,

 

Firstly, I realise this is an old subject, but I still can't find any easy
answer.

 

Really, the question is simple: Is there ANY way of TSQLQuery NOT reading
the entire result set into memory?

 

This is really killing me. I've got quite a lot of web code using sqldb and
it works really well. The classes for connection, transaction, query etc.
are perfect for general use and make it so easy to write database
applications. I find it excellent that I only need to change the connection
class in my code to use a different database.

 

For web code (mine included), fetching a few dozen, hundred or even a few
thousand rows is no real problem. The sqldb classes are *very* fast and very
easy to use.

 

The problem comes when I need to write some apps to either analyse or move a
lot of data. Initially all I need to do is analyse some data by simply
reading through rows, doing some calculations and other analysis. For even 1
million rows this is very fast (10 seconds using MySQL) so no major
problems. However, it does use quite a lot of memory.

 

Further use of this simply explodes. I have tables for analysis (and later
extracting/moving) that are over 40 million rows. This currently is simply
not possible in fpc, at least using the excellent sqldb classes. After
blowing 16GB of RAM+swap it takes a while to recover.

 

To be fair I've tried looking through the code and see how it's implemented,
but I can't see any easy way of avoiding the memory buffering of the entire
result set.

 

Apart from re-implementing a lot of classes or going down to the driver
level I can't currently see any easy way round this. I know there is some
old discussion on this, but I'm kind of hoping that there may have been a
few changes or something recently that I've missed.

 

Any help appreciated.

 

Regards,

Gary.

 

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freepascal.org/pipermail/fpc-pascal/attachments/20170324/d95f88f1/attachment.html>