Fog Creek Software
Discussion Board

The Phenomenon of Disconnected Recordsets

I posted a question before under 'Batch Processing', and it was there that I discovered Disconnected Recordsets in the context of asp.

I've come across this tutorial.

Is it really as easy as it seems?. As I'm about to venture into this new phenomenon (to me anyway).
Any advice I should know about Disconnected Recordsets before taking it on?. Has anyone made use of it and found it to be a valuable method?.
Sam Livingston-Gray stated in the former post
"ASP inherently assumes that you have a continuous connection to the web server.  You could conceivably make this work, but:  (a) you've got to be careful not to rely on any session variables (if people are disconnecting to do their edits, then reconnecting, their sessions will inevitably time out), and (b) you've got to give them a really heavy page with enough scripting on it that they can do all the work they need to do without having to reload the page (and thus dial up again)."

Now does the short tutorial in the link address these issues in any sort of way.?. Because currently, my asp pages has form functions (eg combo boxs, list boxs) that need to retrieve data from the db on the server. Does Disconnected Recordsets address this issue?. Anyones advice would be very much appreciated.

Wednesday, July 30, 2003

Disconnnected recordsets are usefull when you want to use / persist the recordset longer than you can or want to keep open the database connection. Storing the recordset in the session object or a disk file are examples of such a situation.

You need to keep an eye on concurrency issues though. On fast changing data the disconnectred recordset can quickly become outdated. This may not be a problem for read only reference of the data but when you're adding, changing and deleting on the recordset with the intend of persisting the changes to the database using UpdateBatch, this might get you into trouble as changed records may no longer exist or new primary keys may not be unique.

As for performance, it is not recommended to do data manipulation on recordsets. Use INSERT, UPDATE and DELETE statements for optimized performance.

Geert-Jan Thomas
Wednesday, July 30, 2003

The prior thread is at:

Anyway, Drake, while disconnected recordsets might be helpful to you, and they work really well in ASP, I don't think you'll be able to make them work in an ASP environment to do what you want to do.

Here's a line from the first page of the tutorial you linked:  "One requirement of disconnected Recordsets is that they must (for obvious reasons) be maintained by the database client, rather than the database server."

However, something here may not be immediately apparent to you:  the "database client" in an ASP application is really the IIS server, not the end user.

I know you're using an Access database, but let's pretend for a moment that there are three separate computers involved:  a database server, an IIS server, and the user's PC.  The first two are on the same network, and the last one is at the other end of an expensive dialup link.  In this environment, the Recordset only exists on the IIS server.  Data is transferred from the physical tables on the DB server to a Recordset object on the IIS server, and *whether you disconnect it or not*, the Recordset will live and die in the memory of the IIS server.  All the client PC can see (with caveats, which I'll get to in a minute) is the HTML that was rendered using the Recordset.  When the page is finished loading, the Recordset (connected or disconnected) will be thrown away.

I'll try to repeat that, because it's important.  If you just rewrite your ASP pages to use disconnected recordsets instead of whatever you're doing now, your clients will still be in *exactly* the same boat they're in now.  The only difference is that, instead of your IIS server keeping the DB connection open for the 0.8 seconds (for example) it takes to render the entire page, it'll be closing the connection after the 0.1 seconds it takes to read the data from the DB server.  The clients only see the end result, which is pure HTML -- and they have to stay online to keep loading new ASP pages.

Now, the caveats I mentioned.  If your clients are using Internet Explorer, you get to bend the "HTML only" rule a little.  I've seen someone transfer recordsets (using RDO, I think -- this was a few years back) to an IE browser and then use DHTML to work with them within a single page.  Theoretically, you could tell your users to run IE, give them one huge page with a Recordset object in it, let them change the data, and then at the end, have them reconnect and click a "submit" button.  (This is what I talked about in (b) above).

I wouldn't recommend this, though.  For one, it's a lot of complex code, and to be frank, you don't seem ready for that yet.  (=  For another, the user experience is likely to be pretty awful -- if users aren't careful, they could navigate away from that page in their browser window and easily lose all their changes, and let's face it, editing data in a browser is pretty uncomfortable.  Ultimately, it's the proverbial square peg (n-tier objects) in a round hole (HTML-aware browser).  You'd essentially be using IE to try to do the job that a desktop app (in, for example, VB or Access) could do better -- and if you're restricting your clients to IE only, you might just as well give them a separate program to do it right.

Bottom line?  If your users need to download data, disconnect, and work with it, disconnected Recordsets will probably only help if you use them from within a program (*NOT* a Web browser) running on the end-user's PC.

Sam Livingston-Gray
Wednesday, July 30, 2003

If you are using .NET, you may have endless and very hard to solve problems with data syncronization between the "data on disk" and "data in memory".

Wednesday, July 30, 2003

In reply to Geert-Jans comments - If you use ADO disconnected recordsets against SQL server, making data changes to the recordset instead of using manual UPDATE / INSERT statements actually comes to exactly the same result. 
What happens is that ADO generates the SQL statement itself - if you use SQL profiler to watch the server you will be able to see the generated SQL as you re-connect the disconnected recordset to an active connection.

We actually tested both methods - ADO's was faster than doing it ourself, and with out the hassle of formating dates / numbers / apostrophes etc.

Wednesday, July 30, 2003

The article says you can store disconnected Recordsets in Session -- Don't do this unless your application will only have a few tens of concurrent users. For a web app I generally recommend that you hold onto as little state as possible, and just get the db to serve up the data as and when needed.

Duncan Smart
Wednesday, July 30, 2003

Thanks Sam, your advice really helped alot. I just spoke to the management, and they've accepted my recommendation to use Access app for this approach. Now I have to find a way to replicate the asp approach for access. Is there specific keywords I should look up in search engines in order to look into the access approach? eg linked tables.

Wednesday, July 30, 2003


Linked tables would be one way to get at the data, but again, they only work while you're connected.  (=  To create the links, search help for the TransferDatabase method (and remove them with the DeleteObject method).  However, you'll also have to figure out how to reconcile your data when you update it -- and that's beyond the scope of my advice or interest.  ;>

Also keep in mind that because Access is a file-based DBMS, your users will have to be able to map a drive letter to the server with the database -- meaning they've got to be inside the firewall.  Hopefully your existing setup addresses this, but again, that's out of scope!

(Obligatory Muppet reference:  Phenomenon.  Doo dooooo de doo-do... damn.  Now I'm gonna have that song in my head for a while.)

Sam Livingston-Gray
Thursday, July 31, 2003

"The only difference is that, instead of your IIS server keeping the DB connection open for the 0.8 seconds (for example) it takes to render the entire page, it'll be closing the connection after the 0.1 seconds it takes to read the data from the DB server.  The clients only see the end result, which is pure HTML -- and they have to stay online to keep loading new ASP pages"

Some one said this, which is cool and all, but not exactly accurate. Some page generation can happen by appending anything you ever want to write out to the web client as one huge blob of text buffered to a temporary variable. Before you response.Write, close the db connection to conserve connection open periods.

Li-fan Chen
Thursday, July 31, 2003

hhhhmmm, this is getting confusing.

Thursday, July 31, 2003

Is Replication a viable alternative?

Thursday, July 31, 2003

*  Recent Topics

*  Fog Creek Home