How can you best cache disconnected ADODB recordsets application wide in a classic asp scenario?
It does not seem like it is possible to store disconnected recordsets in application state, or am I missing something?
I found an example of how you can persist recordsets to xml and load them again, but I was hoping to stay in memory - but it might be the best option.
Technically you can assign a disconnected ADODB recordset into the Application object. However I wouldn't recommend it.
In order to assign an object into the application object it needs to be free-threaded. An ADODB recordset is Single threaded but it will provide a free-threaded proxy when assigned to the application object.
What this would mean is whenever a request needs to access the recordset the current thread on which the request is running on will block as the proxy will marshall the call to the thread where the recordset was originally created. If another request happens to be using the recordset the marshalled call will be queued.
The problem with this is that in effect all uses of the recordset will be serialised thus creating a scalability issue. Only one request can access the recordset at the same time.
But it gets worse. The thread that originally created the recordset could itself be processing a request, in which case nothing (except the thread itself) can access the recordset regardless of whether the recordset is actually being used or not. In addition the ASP dispatcher may still give a request to the thread even though there is an existing queue of recordset accesses on the thread.
A Solution
However you have got close to the solution. Place convert the contents of the recordset into XML but instead of saving it to a file, load it into a FreeThreadedXMLDocument. This object as its name suggest is a truely free threaded object, its not a proxy to a single threaded object.
Now you can place this xml document in the application object and access it from various requests at the same time.
A disconnected recordset is essentially already a cached recordset, isn't it?
[Disconnected recordsets are] recordsets that have been disconnected from the data source, thus allowing the user to work off-line and move freely between records. If a Recordset is created with write permission, the user can also alter and delete records, or add new ones. These changes will be cached locally and not affect the main database. Later a connection can be re-established to the database, which can then be updated with the changes.
http://www.devguru.com/features/tutorials/DisconnectedRecordsets/tutDisconnRS.asp
