Performance on Large Recordsets

Problem

Connecting to a large database table or result set (10,000 rows and more), a standard ADO application will take a lot of time to start, several minutes or even more, depending on the database and hardware. This delay is usually proportional to the size of the data. No matter what settings you use with ADO recordset, it still is that slow.

Solution

Using True DataControl with CursorLocation=adUseServer, you won't encounter such long delays.

Note

True DataControl Lite will provide the same improvement for ComponentOne True DBGrid 8.0. So you can use True DataLite (free of charge) to solve ADO performance problems for True DBGrid 8.0. To benefit from this improvement using controls other than TDBGrid, you must purchase the full True DataControl version.

Explanation

If you use CursorLocation=adUseClient in ADO, all rows of data are first fetched from the server and only then your recordset becomes operational. It does not only take long time, it also uses up large amounts of memory. That is why adUseClient is completely inappropriate for large recordsets. True DataControl can not improve performance with adUseClient either.

So, the solution seems obvious: use CursorLocation=adUseServer. In practice, however, this does not really alleviate the performance problem with ADO, unless you use True DataControl. The long time delay on start up will still be there if you use the ADO recordset with some data-bound controls and other OLE DB consumers. It is caused by the provider’s implementation of OLE DB IRowsetScroll interface using row count that triggers realization of the recordset, in other words., fetching all its rows on the server. Although it is somewhat faster than using adUseClient, it still depends on the data size and can take very long time to perform.

Using True DataControl with CursorLocation=adUseServer will remedy this situation, help you avoid the long delay that can't be eliminated by any standard ADO means. True DataControl, working as an intermediary between the ADO recordset and data-bound controls, eliminates this problem.