litequery.last loads all query data in memory

Discussion of open issues, suggestions and bugs regarding LiteDAC (SQLite Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
andrea.mauri
Posts: 8
Joined: Thu 26 Jan 2017 11:12

litequery.last loads all query data in memory

Post by andrea.mauri » Thu 26 Jan 2017 11:18

I am working with big DBs and I am testing litedac.
If I have a db with 1 big table and I set a query like 'select * from MY_TABLE' when I open the query only few records are loaded in memory but if I do litequery.last; all the table is loaded in memory by my application.
Is it a correct behaviour?

I am using lazarus 1.6.2 fpc 3.0

Andrea Mauri

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: litequery.last loads all query data in memory

Post by MaximG » Thu 26 Jan 2017 13:51

To ensure a high level of performance LiteDAC components allow to load data returned by a query in specific portions. The FetchRows property is responsible for this behavior: https://www.devart.com/litedac/docs/?de ... chrows.htm
When addressing the last data row returned by a query, it is necessary to subtract all the previous strings — this behavior is correct. In addition, you can load all the data returned by a query at once when using the FetchAll property :
https://www.devart.com/litedac/docs/?de ... tchall.htm

andrea.mauri
Posts: 8
Joined: Thu 26 Jan 2017 11:12

Re: litequery.last loads all query data in memory

Post by andrea.mauri » Thu 26 Jan 2017 14:20

It seems that instead to load a specific portion it loads everything.
If I have a table with 500k rows 300 cols of floats if I activate the query everything goes right, but if I do litequery1.last; all the table is loaded in memory (2 Gbytes)!
I set fetchrows 25 and 1000, same behaviour (FetchAll is False).

Am I doing something wrong?

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: litequery.last loads all query data in memory

Post by MaximG » Tue 31 Jan 2017 10:05

This behavior is correct, because when refering to the last record of the returned data, you need to obtain prior to this, all previous records. To speed up such operations, you can use a special mechanism SmartFetch available in LiteDAC components : https://www.devart.com/litedac/docs/?de ... tfetch.htm . Also note the LiveBlock option availability : https://www.devart.com/litedac/docs/?de ... embers.htm

andrea.mauri
Posts: 8
Joined: Thu 26 Jan 2017 11:12

Re: litequery.last loads all query data in memory

Post by andrea.mauri » Wed 01 Feb 2017 07:39

I don't care about speed actually.
I care about memory usage.
I would like to move along my dataset without loading all the dataset in memory.
i.e. I understand that moving to the last row of my dataset litequery needs to read all the rows before, but I cannot understand why it keeps all dataset in memory.
If I compile my app for win32 I cannot use litequery.last if my dataset is too big since I get a out of memory error.

Is it possible to move up and down along my query without keeping everything in memory? How can I do this?

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: litequery.last loads all query data in memory

Post by MaximG » Thu 02 Feb 2017 14:46

Just to implement the required functionalities there is SmartFetch mode: https://www.devart.com/litedac/docs/?de ... tfetch.htm. Learn carefully this mode description. To reduce the amount of memory used in SmartFetch mode there is the LiveBlock property: https://www.google.com.ua/webhp?sourcei ... =translate . Keep in mind that LiveBlock use can lead to performance decrease.

andrea.mauri
Posts: 8
Joined: Thu 26 Jan 2017 11:12

Re: litequery.last loads all query data in memory

Post by andrea.mauri » Fri 03 Feb 2017 14:10

It does not work as expected. I tested it with a big dataset, one big table 500 cols x 500k rows (2 Gb).

SmartFetch = False

doing:
LiteQuery1.Active:= True;
it opens the query fast.
LiteQuery1.Last;
it loads all dataset in memory (2 Gb).

but with SmartFecth = True
doing:
1) LiteQuery1.Active:= True;
it loads immediately all the dataset in memory (2 Gb). Query activation is slower than with SmartFecth = False.

2) LiteQuery.Last;
this opertaion is faster than with SmartFetch = False (all dataset is already in memory). Same behaviour with LiveBlock True/False and with FetchRows = 25 and 2000.

I am not able to reduce memory consumption using SmartFecth and LiveBlock as reported in Smartfetch (https://www.devart.com/litedac/docs/?de ... tfetch.htm) and liveblock documentation (https://www.devart.com/litedac/docs/?de ... eblock.htm).

Why it does not work as expected?
Am I missing something?

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: litequery.last loads all query data in memory

Post by MaximG » Wed 08 Feb 2017 09:52

To reduce memory consumption while executing the described scenario, try to use UniDirectional. Its detailed description can be found by the link:
https://www.devart.com/litedac/docs/?de ... tional.htm

andrea.mauri
Posts: 8
Joined: Thu 26 Jan 2017 11:12

Re: litequery.last loads all query data in memory

Post by andrea.mauri » Wed 08 Feb 2017 10:08

I know that using Unidirectional I can reduce memory consumption. But I need to move in both directions, unidirectional is not an option.

Anyway could you please explain the SmartFetch behaviour with Unidirectional = False?
I would like to know if SmartFetch and LiveBlock are useful to reduce memory consumption or not. In the documentation it is stated that these properties can be used to reduce memory consumption. I tested them with no success. I detailed my tests and I need a specific answer. Why Smartfetch and LiveBlocks do not act as reported in LiteDac documentation?

Is it a bug or I am doing something wrong?

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: litequery.last loads all query data in memory

Post by MaximG » Thu 09 Feb 2017 14:49

Please for further investigation send us via the e-support form a script to create the table which you are using in the described project.

Post Reply