Fetching Rows (FireDAC)
Go Up to Working with Commands (FireDAC)
Contents
Handling cursors
When a SQL command is executed and it has to return rows, the DBMS creates a cursor on the DBMS server. An application uses the cursor to retrieve rows from a database. Depending on the DBMS, there may be several cursor kinds. To choose the cursor kind, use the FetchOptions.CursorKind property. By default, FireDAC chooses the fastest cursor kind (ckAutomatic). Firstly, this property is meaningful for the Microsoft SQL Server.
The DBMS cursors are sensitive to the transaction context, where they were opened. For more details, read the "Managing Transactions" chapter.
Rowset Fetching
Rowset Fetching allows you to specify the number of records that will be fetched from the server in one network round trip. You can optimize this separately for each SELECT statement that you execute, thereby minimizing the number of network round trips by specifying the RowsetSize option.
The rowset size is controlled by the FetchOptions.RowsetSize property. Higher the number, faster FireDAC fetches the full result set, but the delay to fetch the next rowset is higher too. Also, starting from some "high" values, the performance starts to decrease. The practically "high" value is 2000-3000.
- Note: Not all DBMS are supporting rowset fetching. However, FireDAC has the ability to emulate it at the cost of speed. Emulated rowset fetching has a 50% speed reduction compared to the actual fetching.
FireDAC is fetching rowsets according to the FetchOptions.Mode property:
- fmOnDemand--the rowset is automatically fetched when the dataset is trying to move the current position beyond the last fetched record.
- fmAll--all the rowsets are automatically fetched right after executing the SQL command. This is similar to calling the FetchAll method.
- fmManual--the programmer manually fetches the rowsets using the FetchNext or FetchAll methods.
- fmExactRecsMax--all rowsets are automatically fetched right after executing the SQL command. If the number of rows is different from FetchOptions.RecsMax, an exception is raised.
When the FetchOptions.Unidirectional is True, and before fetching the next rowset, the previous one is discarded from memory. That allows preserving the PC memory when fetching large result sets.
When all records are fetched, TFDDataSet.SourceEOF is set to True, and the underlying command is closed, based on FetchOptions.AutoClose. This does not close the dataset itself.
- Note: Read Command Batches for more details.
The application gets access to each fetched record using the TFDDataSet.AfterGetRecord event handler.
Rows Paging
FetchOptions.RecsSkip and RecsMax allow paging through the result set. After the cursor is opened, the first RecsSkip records is skipped. The rest of the records, up to RecxMax will be fetched. Changing the RecsSkip and RecsMax property values has no effect, when a statement is prepared. So, before fetch the next rows page, the command must be unprepared, then executed again. For example:
FDQuery1.FetchOptions.RecsSkip := 0;
FDQuery1.FetchOptions.RecsMax := 100;
FDQuery1.Open;
// process rows
FDQuery1.Disconnect;
FDQuery1.FetchOptions.RecsSkip := 100;
FDQuery1.Open;
// process rows
FDQuery1.Disconnect;
FDQuery1.FetchOptions.RecsSkip := 200;
FDQuery1.Open;
// process rows
If possible, when the RecsSkip and/or RecsMax properties are specified, FireDAC modifies the original SELECT command, to apply TOP / ROWS and similar phrases.
Delayed Fetching
The result set may include BLOB and/or nested datasets columns. In general, such columns slowdown the result set fetching. FireDAC allows postponing such columns fetching until their values are really needed. The FetchOptions.Items property controls that:
- When fiBlobs is excluded, FireDAC delays the BLOB values fetching. The FetchBlobs method performs BLOB values fetching for the current dataset record. Alternatively, the first reading of a BLOB value automatically calls FetchBlobs, when Mode <> fmManual.
- When fiDetails is excluded, FireDAC delays the nested dataset fetching. The FetchDetails method performs nested dataset fetching for the current record. Also, fiDetails controls the master-details handling.
For the delayed fetching SQL command generation, read Update Command Generation.
- Note: Excluding fiBlobs or fiDetails from FetchOptions.Items does not modify the SELECT list of a SQL command. If a DBMS transfers BLOB values by value (e.g., Oracle LONG, MySQL, SQL Server, SQLite), the BLOB values will be delivered to the client through the network. However, they will be not stored in the client records cache. But if a DBMS transfers BLOB values by reference (e.g., Oracle CLOB / BLOB, Interbase, Firebird), the BLOB values will be not delivered nor stored.
Refetching Rows
Sometimes, an application needs to append the new result set to the existing one or to refetch rows, etc. For that, the FetchAgain method is used.
- Note: Use the Refresh method for dataset refreshing.
General Usage Cases
The following table provides the common usage cases and corresponding FetchOptions setups:
| Case | Description |
|---|---|
| Minimum fetching time of large volume of records with limited memory usage. | CursorKind = ckDefault or ckForwardOnly
Mode = fmOnDemand RowsetSize = 1000 Unidirectional = True |
| Minimum fetching time with delay at query opening. | CursorKind = ckDefault
Mode = fmAll RowsetSize = 1000 |
| Minimum delay at query opening. | CursorKind = ckDynamic
Mode = fmOnDemand RowsetSize = 50 Exclude fiMeta from Items. |
| Read-only dataset. | Exclude fiMeta from Items, or set UpdateOptions.RequestLive to False. |
| Batch command with multiple result sets. | AutoClose = False |
| Minimum fetching time of large volume of records with multiple large BLOB values, and limited memory usage. | Exclude fiBlobs from Items. Combine with above. |