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 few cursor kinds. To choose the cursor kind use the FetchOptions.CursorKind property. By default AnyDAC will choose most fast cursor kind (ckAutomatic). At first 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 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. For example, Zeos DBO does not support Rowset Fetching, so AnyDAC is a few times faster than Zeos DBO at fetching. 

The row set size is controlled by the FetchOptions.RowsetSize property. Than higher is the number, then faster AnyDAC will fetch the full result set, but the delay to fetch next row set will be higher too. Also, starting from some "high" value the performance stops to grow and even may start to degrade. The practically "high" value is 2000-3000. 

Not all DBMS are supporting row set fetching. If so, AnyDAC will emulate it. Even emulation speedups fetching for <= 50%. 

AnyDAC is fetching row sets according to the FetchOptions.Mode property:

• fmOnDemand - row sets will be fetched automatically, when dataset is trying to move the current position beyond the last fetched record.
• fmAll - all row sets will be fetched automatically right after executing the SQL command. This is similar to call the FetchAll method.
• fmManual - programmer will manually fetch row sets using the FetchNext or FetchAll methods.
• fmExactRecsMax - all rows sets will be fetched automatically right after executing the SQL command. If the number of rows will be different from FetchOptions.RecsMax, then an exception will be raised.

When the FetchOptions.Unidirectional is True, then before fetching the next row set, the previous one will be discarded from memory. That allows to preserve the PC memory when fetching large result sets. 

When all records are fetched, then TADDataSet.SourceEOF will be True, and depending on the FetchOptions.AutoClose the underlying command will be closed. This will not close the dataset itself. Also, read "Command Batches" for more details. 

The FetchOptions.RecsSkip and RecsMax allows to page through the result set. After the cursor will be opened, the first RecsSkip records will be skipped, then the next up to RecxMax records will be fetched. Changing RecsSkip and RecsMax property values has no effect, when a statement is prepared. So, before to fetch the next rows page, the command must be unprepared, then executed again. For example: 

ADQuery1.FetchOptions.RecsSkip := 0;
ADQuery1.FetchOptions.RecsMax := 100;
ADQuery1.Open;
// process rows

ADQuery1.Disconnect;
ADQuery1.FetchOptions.RecsSkip := 100;
ADQuery1.Open;
// process rows

ADQuery1.Disconnect;
ADQuery1.FetchOptions.RecsSkip := 200;
ADQuery1.Open;
// process rows

When RecsSkip and / or RecsMax properties are specified, AnyDAC if possible will modify the original SELECT command, to apply TOP / ROWS and similar phrases. 

The result set may include BLOB or / and nested datasets columns. In general, such columns slowdowns result set fetching. AnyDAC allows to postpone such columns fetching until they values will be really needed. The FetchOptions.Items property controls that:

• when fiBlobs is excluded, then AnyDAC will delay BLOB values fetching. The FetchBlobs method performs BLOB values fetching for the current dataset record. Alternatively, the first reading of a BLOB value will automatically call FetchBlobs, when Mode <> fmManual.
• when fiDetails is excluded, then AnyDAC will delay 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 the "Update Command Generation" chapter. 

Sometimes an application may need to append new result set to the existing one or to refetch rows or etc. For that the FetchAgain method may be used. Note, use the Refresh method for dataset refreshing. 

The following table provides the common usage case and corresponding FetchOptions setup:

Executing Command, Executing Stored Procedure, Command Batches, Update Command Generation