Performance of Obtaining Data

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.

• CursorType = ctDefaultResultSet, FetchAll = True - This is the default SDAC settings. Opening is pretty slow, but navigation is fast. All records are fetched on opening, and cached on the client.
• CursorType = ctDefaultResultSet, FetchAll = False - Opening is fast irrespectively of the total records count. Only several records are fetched on opening. You can specify the number of records in the FetchRows property. Other records are retrieved from the server on demand, and cached. Additional records may be demanded when scrolling through the linked grid, calling to Locate, Last, etc.
  However, these settings may cause certain problems related transaction conflict and deadlocks. For more details please refer to the description of the TCustomMSDataSet.FetchAll property.
• CursorType in [ctStatic, ctKeyset, ctDynamic] - All these cursors are server cursors. They are characterized by quick opening, low client memory utilization, and slow navigation. Only data required at the moment is cached. For more details about these cursor types please refer to the description of the CursorType property.

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.

See Also

• FetchAll
• CursorType
• The ServerCursors demo in the SDAC General demo
• The FetchAll demo in the SDAC General demo