I have connected a UniTable to a sqlite database with 90K rows
If I run the following code I eventually use about 400MB of memory
for(int i=0;iRecordCount;i++)
{
UniTable1->RecNo = i;
Application->ProcessMessages();
}
My databases will eventually be a few millions rows and I will likely run out of memory - is there something I should (could) be doing to free this memory up?
Excessive memory usage/memory leak
Hello
Standard mode of TUniQuery or TUniTable is implemented for small, medium-sized or large recordsets and for connecting visual components like DBGrid to these recordsets. If you don't keep all records in the memory then you cannot scroll DBGrid forward and backward without reopening a query. For huge recordsets we made UniDirectional mode that allows not to keep all records in the memory.
You can limit the number of records in the memory by setting the FetchRows property if UniDirectional = True.
Standard mode of TUniQuery or TUniTable is implemented for small, medium-sized or large recordsets and for connecting visual components like DBGrid to these recordsets. If you don't keep all records in the memory then you cannot scroll DBGrid forward and backward without reopening a query. For huge recordsets we made UniDirectional mode that allows not to keep all records in the memory.
You can limit the number of records in the memory by setting the FetchRows property if UniDirectional = True.
I wrote before that the UniDirectional mode is incompatible with any DB grids. The UniDirectional mode allows to move through the records only forward.
This mode was created for the following way of database usage:
If you are going to use any DB grid for showing your data then it will be required to load to the memory all records that will be shown.
This mode was created for the following way of database usage:
Code: Select all
begin
UniQuery1.Open;
UniQuery1.First;
while not UniQuery1.Eof do
begin
UniQuery1.Edit;
// update record
UniQuery1.Post;
UniQuery1.Next;
end;
end;bork wrote:If you don't keep all records in the memory then you cannot scroll DBGrid forward and backward without reopening a query.
If you are going to use any DB grid for showing your data then it will be required to load to the memory all records that will be shown.
Bork thanks for the feedback.
I have a problem and it may mean that I need to find another DAC, which I really dont want to do.
For various reasons I must, on occasion, display all of the rows in my table in a grid. A short delay while a query is reopened would be acceptable. Is it not possible to buffer the data, say a few 100/1000 records, to allow scrolling backwards and forwards - loading all rows into memory seems so excessive and unnecessary.
Without this functionality my application will not scale and would need to be shelved.
I think you would still have issues selecting so many rows.
Are you not able to process all the records but in batches. e.g. As to Zs you would do all keys beginning with A first, then all Bs and so on.
Alternatively, if you want to 'page' forward and backwards through all rows, you may be able to use LIMIT and OFFSET in your query to fetch records 1..99, 100..199, etc which is just one chunk at a time in order to display them. This is a bit like web pages handle paging through large datasets but only fetch enough each time to fill a grid or table.
Regards, Paul.
Are you not able to process all the records but in batches. e.g. As to Zs you would do all keys beginning with A first, then all Bs and so on.
Alternatively, if you want to 'page' forward and backwards through all rows, you may be able to use LIMIT and OFFSET in your query to fetch records 1..99, 100..199, etc which is just one chunk at a time in order to display them. This is a bit like web pages handle paging through large datasets but only fetch enough each time to fill a grid or table.
Regards, Paul.
Thanks Paul
No unfortunately I am not able to restrict access as you say, or rather I can for a lot of the time (and I have) but there are occasions when my users will want to just scroll around and explore everything, although it sounds daft it is something they expect (and when I am in their position I would expect it to).
I am not sure I can use limit and offset using the grid of choice (DevExpress) although I will look into it.
No unfortunately I am not able to restrict access as you say, or rather I can for a lot of the time (and I have) but there are occasions when my users will want to just scroll around and explore everything, although it sounds daft it is something they expect (and when I am in their position I would expect it to).
I am not sure I can use limit and offset using the grid of choice (DevExpress) although I will look into it.
Hello
If you use DevExpress, then please make sure that DevExpress doesn't cache all records in the memory. If DevExpress caches records in the memory, then you can use any components to access to SQLite and it will be always very slow.
I don't know any easy way to resolve your issue. I can offer the following way:
- Create TUniQuery that will load only primary key from your table like this:
select ID from test_table
- Add calculated columns for all other fields.
- Handle event OnCalcFields and load values for records that are visible for user. To improve performance you should load, for example, 100 records per one data loading query. Data loading for calculated fields should be performed in a separate TUniQuery.
If you use DevExpress, then please make sure that DevExpress doesn't cache all records in the memory. If DevExpress caches records in the memory, then you can use any components to access to SQLite and it will be always very slow.
I don't know any easy way to resolve your issue. I can offer the following way:
- Create TUniQuery that will load only primary key from your table like this:
select ID from test_table
- Add calculated columns for all other fields.
- Handle event OnCalcFields and load values for records that are visible for user. To improve performance you should load, for example, 100 records per one data loading query. Data loading for calculated fields should be performed in a separate TUniQuery.