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 ?

Spurling

There is no explicit question here. Might just be me, but I don't know what you are asking. Why you are running out of memory? It seems you figured out how to solve it. – Renaterenato

at this time - which time? when u call Last or when you call FetchAll1 ? application freezes 30-40 sec` - of course it does, you are probably downloading 3 millions of records with all the multimillion heap memory allocations and deallocations it produces in db-access and even more in db-visual components. That is a quest in futility. There is no human being who can read 3 millions of rows at work. There is no any sense to do it. – Noseband

Welcome to SO! Please, take The Tour for a brief intro on Stack Overflow, and then read Asking to learn what you can ask about and how to present your question. Please take note that questions must be clearly expressed. – Cain

Just consider though... that opening a dataset with 3M rows is usually not the right thing to do... especially if this happens with attached UI controls (as it seems in your case). If you are executing a batch operation, then it can make sense (but Unidirectional there can help). But no user is going to be happy navigating 3M records. You should consider making a better UI that helps the user narrow down the dataset to find the data he really wants to work on. – Pinnatipartite

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

Pocketful

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

Prophase

Answer 3

When a FireDAC query is set to Unidirectional it will remove the records from memory after they have been visited. This ensures that no memory overflow happens.

The time delay when moving to the last record is due to the fact that each intermediate record is visited which obviously needs some time.

Bobo

Thank you for your answer, but third party DB tools dont freeze or delay. Immediately position to the last records. How can theese tools achieves this?? – Spurling

If you select data from one table only and that table has a primary key, you can use a TFDTable instead of TFDQuery and set CachedUpdates to false, FetchOptions,Unidirectional to false and FetchOptions.CursorKind to ckAutomatic or ckDynamic. Then the TFDTable operates in Live Data Window Mode giving better performance with large tables. – Bobo

Thank you , but unfortunately your solution didnt work.. The problem is stil going on.. – Spurling

Third party DB tools don't freeze or delay and immediately position in the exact same circumstances, @NiyaziKorkmaz? I mean you have 3 million records in a dataset if I understand correctly. That is a lot. – Renaterenato