litequery.last loads all query data in memory
-
- Posts: 8
- Joined: Thu 26 Jan 2017 11:12
litequery.last loads all query data in memory
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
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
Re: litequery.last loads all query data in memory
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
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
-
- Posts: 8
- Joined: Thu 26 Jan 2017 11:12
Re: litequery.last loads all query data in memory
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?
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?
Re: litequery.last loads all query data in memory
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
-
- Posts: 8
- Joined: Thu 26 Jan 2017 11:12
Re: litequery.last loads all query data in memory
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?
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?
Re: litequery.last loads all query data in memory
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.
-
- Posts: 8
- Joined: Thu 26 Jan 2017 11:12
Re: litequery.last loads all query data in memory
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?
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?
Re: litequery.last loads all query data in memory
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
https://www.devart.com/litedac/docs/?de ... tional.htm
-
- Posts: 8
- Joined: Thu 26 Jan 2017 11:12
Re: litequery.last loads all query data in memory
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?
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?
Re: litequery.last loads all query data in memory
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.