If you need to obtain an updatable recordset in your application and show it in a grid, the size of the data to be transferred from the server is very important. As a rule such recordsets are not that big, as it is hard for a user to handle tables containing thousands of records. In this case the most appropriate is the default SDAC behavior, when the CursorType property of the dataset is set to ctDefaultResultSet, and the FetchAll property is set to True.
Just the the same settings must be used, irrespectively of the data size, for the datasets serving as lookup sources for lookup fields.
If you want to see the result of a query execution returning a large amount of data immediately, you should set the FetchAll property to False, or use server cursors. In both cases only few records are fetched to the client immediately after opening. Other records are fetched on demand.
There are brief descriptions of advantages and disadvantages for different settings below.
If you need to get only certain values from the server, for example only record count, it is more effective to execute a query with parameters:
SET :Cnt = (SELECT COUNT(*) FROM ...)
instead of queries like this one:
SELECT COUNT(*) FROM ...
Note: Only the ctDefaultResultSet cursor allows executing batches of queries.