Excessive memory usage/memory leak

[sandy771]

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;i<UniTable1->RecordCount;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?

[bork]

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.

[sandy771]

set unidirection to true - left fetch rows at 25 - application crashes as soon as the database is opened (DevExpress grid displays first row before crash)

[bork]

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:

[sandy771]

[DepSoft]

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.

[sandy771]

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.

[bork]

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.