DBGrid with read ahead capability using ADO

Question

I'm working with ADO connecting to SQL Server 2005.

My TADODataSet selects 1 million records. using a TDBGrid and setting the TADODataSet.CursorLocation to clUseServer works. but the TDBGrid chokes!

How can I select 1 million records, avoid paging, and still be able to display records in the grid without fetching ALL records to the client side, letting the Grid read ahead as I scroll up and down?

SQL enterprise manager can execute a query and select 1 million records asynchronously without any problems (also MS-ACCESS).

Answer 1

TGrid is not your problem. Your problem is TADODataset is trying to load all the records. If you must run a query that returns so many records, you should set ExecuteOptions, try eoAsyncExecute and eoAsyncFetch. It may also help to set CacheSize.

Answer 2

• Why do you need to fetch 1M records into a grid? No human being can look at so many records. Usually is far better to reduce the number of records before loading them into a UI.
• If you have a good reason to show so many records into a grid, you'd need a dataset that 1) doesn't load the whole recordset when opened 2) doesn't cache previous record or it could run out of memory (under 32 bit Windows especially) far before reaching the end of the recordset if the record size is not small enough. To obtain such result beyond CursorLocation you have to set CursorType and CacheSize properly.
• You can use a TClientDataset to implement incremental fetching setting the ADO dataset CursorType to ForwardOnly and CacheSize to a suitable value. Because TClientDataset caches read records, you want to avoid the source dataset to load all of them as well. A standard DB grid needs a bidirectional cursor, thereby it won't work with an unidirectional one. With so many records the client dataset cache can exhaust memory anyway. I'd suggest to use the Midas Speed Fix unit if you're using a Delphi version before 2010.
• To avoid "out of memory" errors you may need to implement some kind of pagination. Anyway, check if the behaviour of other CursorType can help you.