FDQuery causing Out of memory exception

Question

I have a Firebird database with 3 million records. My FetchOptions are RowsetSize := 1000 with FetchAll disabled. I work on localhost.

I have no problem browsing page by page, but when I click on "goto last record" button in the DBNavigator, it throws "Out of memory" exception.

If I set UniDirectional property to True, there is no problem. However, moving to the last record causes application to freeze for 30-40 seconds.

What is the problem and the solution ?

Answer 1

The problem is that your approach really consumes all available memory and you are notified properly by getting the "out of memory" error. All records are fetched from the server and kept in memory. OS can't allocate more to your application.

What to do? Just avoid to load millions of records. Your users in any case will not be able to traverse/browse/inspect each record from that a huge dataset.

Options to consider:

• retrieve just needed by user records, limiting/filtering the dataset
• select just needed fields not all from the server source data
• use unidirectional to avoid local caching in one-pass scenarios

Answer 2

The solution is to use TFDTable dataset instead of default one TFDQuery. TFDTable dataset supports Live Data Window Mode. In this mode dataset is bidirectional so it can be used with visual components like for example TDBGrid. The difference in comparison with TFDQuery is that old records are discarded. At every time instant dataset keeps in memory only 2 * FetchOptions.RowsetSize records (default value 2 * 50 = 100 records), a window into the table data. When the application navigates through the table data, FireDAC automatically scrolls or positions the Live Data Window to the required position. This offers the following benefits:

• Minimizes memory usage and allows you to work with large data volumes, similarly to an unidirectional dataset.
• Enables bidirectional navigation, in contrast to an unidirectional dataset.
• Gives always fresh data, reducing the need to refresh the dataset.
• Does not give a delay to fetch all result set data, required to perform sorting, record location, jumping to last record, etc. The Filter property, range filtering, the IndexFieldNames and IndexName properties, the Locate and Lookup methods, key locating, setting RecNo, setting a bookmark, etc., are performed by additional SELECT commands or by setting additional phrases for the main SELECT command. After changing of Filter, IndexFieldNames or IndexName, the current position in dataset can change, too. To preserve the current position, save a bookmark before the change and restore it after the change.

In LDW mode, the Filter property value is substituted as is into the WHERE phrase. By design, LDW mode always applies the ORDER BY phrase to the SELECT commands. The key requirement for the correct LDW work, are:

• A table must have unique or primary key. See Unique Identifying Fields for more details.
• The server-side sort collation and client-side sort collation must be the same. Otherwise, TFDTable may produce duplicated rows and raise "unique key violation" error.

Although FireDAC minimizes the number of generated and executed SQL commands in LDW mode, it still produces a heavier DB load than TFDQuery. So, application developers should carefully choose when to use TFDTable and LDW mode.

Setting the LDW Mode

The LDW mode is used when all the following conditions are met:

• CachedUpdates is False by default.
• FetchOptions.Unidirectional is False by default.
• FetchOptions.CursorKind is set to ckAutomatic or ckDynamic (ckAutomatic is the default value).
• The table has primary or unique key.

Otherwise, the standard mode is used.

More information can be found in Embarcadero documentation: http://docwiki.embarcadero.com/RADStudio/Rio/en/Browsing_Tables_(FireDAC)#Live_Data_Window_Mode