Thursday, February 28, 2008

Microsoft Access: Snapshot and Dynaset recordset queries - what the heck are they?

Using Microsoft Access is a bit like being in an Indiana Jones movie. There are dark and mysterious ancient temples to explore, hidden treasures, snakes, spiders and the occasional death dealing trap. The scrolls that would explain everything are all losts in the mists of time, before there was a web.

It keeps me from boredom.

Yesterday a complex and powerful set of nested subqueries began retuning unpredictable results. Yes, I'd again broken Microsoft Access. It seems that Access was getting variable results depending on the sequence of subquery execution, such that it couldn't decide if a value were NULL or not.

So I tried to figure out if setting the sub-query(s) (recordset) to behave as a "Dynaset", "Dynaset (lazy update)" or "Snapshot"  would make any difference.

First, of course, I looked for documentation on what the differences are. It's not in the Access Help files. Google found precious little at first -- one of my page one results was a post I wrote in January 2006!)

This search worked better: recordset, dynaset, snapshot.

From it I got a few references:

O'Reilly Network -- Access Design and Programming Tips

But it is important to remember that DAO (Data Access Object) provides several forms of recordsets: table-type, dynaset, snapshot, and forward-only. The point here is that if a snapshot or forward-only recordset will suit your purposes, you should use it, because it will be more efficient and therefore your code will run more quickly.

Snapshot recordsets are faster to create and to access than dynaset recordsets, but they use more memory because the entire record is stored in memory. The downside is that you cannot update the records in a snapshot recordset.

Forward-only recordsets are like snapshot recordsets, but you can only move forward through the recordset.

Ultimately, the only way to tell whether you will save significant processing time is to try the various suitable recordset types in a given situation. The point here is to not fall into the habit of always creating a table-type or dynaset-type recordset.

and

DAO Object Model The Definitive Reference Chapter 8 Recordsets Collection and Recordset Object

...There are five types of recordsets: Table-type, Dynaset, Snapshot, Forward-only, and Dynamic. (See the Type property section for a discussion of recordset types.) Each Recordset object contains a collection of Fields that represents the fields in the underlying table(s). You can list the field names and values, but you will just get the values in the current record, unless you first go to a particular record. For example, the following code moves to the last record in a recordset and lists the field names and values for that record:...

Recordset Property Summary which shows almost NO difference between Snapshot and Dynaset.

But then we come to the precious table 8-13:

Dynaset: Represents a table or an updatable query. Supports the AddNew, Delete, and Find* methods, but not the Seek method.

Snapshot: A read-only recordset; useful for finding data or printing. Does not allow updating, except in the case of an updatable Snapshot in an ODBCDirect workspace. Supports the Find* methods.

Elsewhere I read

Dynaset:
1. Can be formed by large number of tables resulting in Virtual Tables.
2. Does not store on the Machine but occupy temporary memory that is RAM.
3. The changes made in the database can be reflected in the this.
4. Allows operations like edit and update.

SnapShot:
1. Creates table on the machine and occupies space of the memory.
2. Does not allow edit and update operations.
3. Can be used for small amount of data.

Another source (lost) said a Snapshot is often quite a bit faster in a query than a Dynaset, but the speed requires more memory use.

By extension of the above, I imagine "Dynaset (lazy update)" is an extension of Dynaset with some of the speed of a Snapshot but the possibility of doing updates (delayed or lazily).

So, after all that, did changing some of my subqueries to "Snapshot" fix the problem of the "Schrodinger's Cat" results that were sometimes NULL and sometimes NOT NULL?

Yes, it did. The results were much faster too.

4 comments:

Anonymous said...

I have a number of queries that are available to database users, and I'd like to write some code to make them all Snapshot type.

I can loop through the querydef collection, but I can't find any way to force the Snapshot property onto each query.

You'd think of:
qdf.[some property] = "snapshot"

... but what would [some property] be?

Any advice?

Anonymous said...

I stumbled onto your blog looking for some insight into the "mysteries of the DAO" - very entertaining post. I hate Access but have to use it quite a bit.

My basic question is -- and I realize you're no Access expert either but maybe someone reading this can tell me -- does a snapshot recordset consume resources related to retaining a database connection? Or once it's open, are these resources freed up? It would be very useful to have a lightweight recordset object, but I'm guessing it's not. We are talking about Microsoft.

In response to the other commenter, my understanding is that the type of recordset you open is determined when you open it (qdf.OpenRecordset(..) in your example). So it's not a property of the Query definition.

Eric
ericgj72@gmail.com

Stacy said...

Interesting post, thank you! I was trying to figure out if I should be setting a query of mine as Snapshot or not, and this post was really helpful.

PS: Ran through a pile of your other posts and am having quite a laugh. It's nice to see someone who loves Access so much!

JGF said...

"Love" is ironic I hope :-).

Access is a weird old power tool. It is encrusted and funky, but there are things it does nothing else can do.

Doing queries across multiple data connections - ODBC and local ... for example ...

I can't imagine it will last much longer. I'm still on 2003 when I can use it ...