We are using ADO, and currently create our recordsets using adUseClient and adOpenStatic since that way we can guarantee all the results will be loaded from the server and cached into the client memory without holding locks open on the server.
However there are great performance benefits to adUseServer and adOpenForwardOnly! I have tried using adAsyncFetch and adAsyncExecute to load things in the background when necessary.
Unfortunately the trouble is that despite the value that I've set the CacheSize to, when the recordset is opened it does not load all of the records into memory. This is a concern since some operations on each record in the client application may take a
while or even block waiting on user input. While this occurs, this holds locks on the tables in SQL Server until we read through all the records.
I have tried setting CacheSize to see if it would load everything all at once, but to no avail.
Since all the pieces seem to be here, I don't quite understand why the server-side cursor can't simply load everything into memory in the background the same way the (static) client-side cursor does.
I've tried some of the recordset's dynamic properties (like Maximum Open Records, which is readonly) to no avail either.
I know ADO is a bit dated now, but I would be overjoyed if anyone could guide me to a solution
View Complete Post