sqlite, big datasets and memory management lazarus / 16-Databases ---------------------------------------------------------------------- apeoperaio · 2017-02-01 07:52 I need to work with huge datasets and sqlite (datasets with tens/hundreds of columns and even million of rows). If I use queries to load this kind of datasets I easily get a huge memory usage. e.g. If I have a query that selects a big dataset I have no problem when I activate it but if I scroll the query the dataset is gradually loaded in memory,  when I reach the end of the dataset (e.g. query.last) the whole dataset is loaded in memory. I would like to have always only a portion of my dataset in memory. Is that possible? Thanks, Andrea ---------------------------------------------------------------------- Zoran · 2017-02-01 08:42 I'm afraid it is difficult to implement. But, did you test with unidirectional query? Try it, set TSQLQuery.Unidirectional to True and see if it is same. Yes, it would be a very useful feature of TSQLQuery if it could work this way (for example, when loading next 100 records, forget the oldest 100 and keep only 1000 in memory). At least for unidirectional queries. Feel free to ask for this feature in FPC bugtracker. Who knows, maybe it will be implemented in future. At least, when TSQLQuery.Unidirectional is set to True. ---------------------------------------------------------------------- apeoperaio · 2017-02-01 09:45 I tested unidirectional and it works as expected, faster and with low memory usage. The query does not load the whole dataset but only a portion. It is useful but if TSQLQuery could work in the same way (keeping in memory only a portion of dataset) would be the best. Since it would be useful to show data in a dbgrid. I will ask this feature in bugtracker. Thanks. ---------------------------------------------------------------------- Thaddy · 2017-02-01 10:18 Quote from: Zoran on February 01, 2017, 09:42:25 amI'm afraid it is difficult to implement. Since when is a windowed stream difficult to implement? Just curious.. ---------------------------------------------------------------------- Zoran · 2017-02-02 07:48 Quote from: Thaddy on February 01, 2017, 11:18:22 amQuote from: Zoran on February 01, 2017, 09:42:25 amI'm afraid it is difficult to implement. Since when is a windowed stream difficult to implement? Just curious.. I don't know, I said just that I was afraid it was difficult to implement. Well... i guessed it was difficult just because it hasn't been implemented so far; I would expect such a thing to be there already if implementing it is easy. I don't know much about how TSQLQuery works internally. I'm glad if implementing this is not difficult, using "windowed stream" (not that I know what it is), or whatever . ---------------------------------------------------------------------- Zoran · 2017-02-07 10:42 Quote from: Zoran on February 02, 2017, 08:48:17 amQuote from: Thaddy on February 01, 2017, 11:18:22 amQuote from: Zoran on February 01, 2017, 09:42:25 amI'm afraid it is difficult to implement. Since when is a windowed stream difficult to implement? Just curious.. I don't know, I said just that I was afraid it was difficult to implement. Well... i guessed it was difficult just because it hasn't been implemented so far; I would expect such a thing to be there already if implementing it is easy. I don't know much about how TSQLQuery works internally. I'm glad if implementing this is not difficult, using "windowed stream" (not that I know what it is), or whatever . However, it seems that it is not so easy (and even possible), after all. See what LacaK says: http://bugs.freepascal.org/view.php?id=31311#c97881 ---------------------------------------------------------------------- Thaddy · 2017-02-07 11:40 What Lacak says means it is not possible *at all*, what I say it is - partly - possible within the locally cached window on the stream and with a risk of confilicts if there are many users with commits pending. Those things are both valid. In any language. For any database. This always comes back on any forum about database technology on poor hardware. Yes your PC is poor hardware.... It is more or less a limitation, unless overhead can be kept at a bare minimum. But that is rarely the case because current PC technology is too limited to do what you want. The case looks completely different on big irons communicating with each other. Or sufficiently efficient message busses. ---------------------------------------------------------------------- ASerge · 2017-02-07 13:27 Quote from: apeoperaio on February 01, 2017, 10:45:12 amIt is useful but if TSQLQuery could work in the same way (keeping in memory only a portion of dataset) would be the best. Since it would be useful to show data in a dbgrid. Thanks. Quote from: Zoran on February 07, 2017, 11:42:57 am...See what LacaK says: http://bugs.freepascal.org/view.php?id=31311#c97881 As say LacaK Quote...and store fetched records in local buffered dataset...For example use 'create temp table xxx as select ...'