Benefits of different recordset types

[Dan]

What are the benefits of specifying recordset types?

I mainly use the dbopentable... have been having
performance issues with losing connections... is one
recordset type better than another?

Is dbOpenSnapshot faster than dbOpenTable, use less
memory,...what about dbOpenDynaset or dbOpenForwardOnly...

Thanks,

Dan

 

[Lance McGonigal]

Seems to me that Microsoft gave me more options with dbopendynaset. I've
used it for years without any problems.

hth

 

[Allen Browne]

Use dbOpenDynaset unless you have a reason to use something else.

dbOpenTable works only on tables in the database you have open. It does not
work on linked tables, so don't use it if could split your database
(frontend/backend).

In most cases, the most efficient solution is to use a SQL statement that
gets the minimum number of records and fields, sorted in the order you need.
Again, you cannot use dbOpenTable with a SQL statement.

dbOpenTable does allow the very fast Seek method, so this approach is useful
where you must programmatically and randomly locate entries in a local
temporary table inside a loop, and you need most of the records and fields.
Other than that, use dbOpenDynaset.

 

[John Vinson]

What are the benefits of specifying recordset types?

I mainly use the dbopentable... have been having
performance issues with losing connections... is one
recordset type better than another?

Is dbOpenSnapshot faster than dbOpenTable, use less
memory,...what about dbOpenDynaset or dbOpenForwardOnly...

Thanks,

Dan

Just to add to the suggestions... if you don't need to edit the data
(or don't want it to be editable), and don't need it to automatically
refresh when other users enter data, OpenSnapshot is apparently very
fast.

 

[Tim Ferguson]

Just to add to the suggestions... if you don't need to edit the data
(or don't want it to be editable), and don't need it to automatically
refresh when other users enter data, OpenSnapshot is apparently very
fast.

Just to add again, using the ForwardOnly option for the snapshot is faster
again (and uses less memory) if you are only going to read forward through
the recordset without going backwards.

HTH


TimF

 

[timdadd]

Folks, just a bit of a beware, I have a very normalised address database (lots of addresses and 2Gb limit per table). One of those tables is AddrFields which is the name of each field of an address and the address field type. So within AddrFields, Address Field Type ID 2="Town" and Name is "London" as an example.

On the address fields table I have 4 fields, AddrFieldID being a unique auto number key, AddrFieldTypeID being the type of address field (Town, locality, street, building name, postcode etc.), Name being the actual name, SoundexName being the Soundex version of the name.

I have 3 indexes on the table 1) the primary key AddrFieldID, 2) A concatentaion of AddrFieldTypeID and Name for looking up the AddrFieldID for the Town London quickly, 3) A similar index to (2) but using the soundex name so that even if user doesn't know how to spell Nottingham I still find it.
So indexes as follows:
AddrField_IDX, AddrFieldTypeID+AddrFieldText, Unique=Yes, IgnoreNulls=No
AddrField_SDX, AddrFieldTypeID+AddrFieldSoundex,Unique=No, ignoreNulls=No
PrimaryKey,AddrFieldID,PrimaryKey=Yes,Unique=Yes,IgnoreNulls=no

So when looking something up, to up the performance I usually use the dbOpenForwardOnly, dbReadOnly options when opening the recordset.

But for this particular table if I run the planner I note that if I put dbOpenForwardOnly on the openrecordset the plan result is as follow:
- Inputs to Query -
Table 'AddrFields'
Database 'C:\Tim\AddrFields.mdb'
- End inputs to Query -
01) Restrict rows of table AddrFields
using rushmore
for expression "AddrFieldTypeID=4"
then test expression "AddrFieldText="82-84""

So basically it finds all records with AddrFieldTypeID=4 (e.g. House Number) and then scans thru looking for the value "82-84" - so really slow

If I take the dbOpenforwardOnly option off the openrecordset statement I get:
- Inputs to Query -
Table 'AddrFields'
Database 'C:\Tim\ordi\AddrFields.mdb'
- End inputs to Query -
01) Restrict rows of table AddrFields
using index 'AddrField_IDX'
for expression "AddrFieldTypeID=4 And AddrFieldText="82-84""

So as you can see it uniquely finds the AddrField record that relates to house number "82-84"

If I remove the other two indexes from the AddrFields table and just leave the AddrField_IDX index then the output is as follows:
Table 'AddrFields'
Database 'C:\Tim\AddrFields.mdb'
- End inputs to Query -
01) Restrict rows of table AddrFields
using rushmore
for expression "AddrFieldTypeID=2 And AddrFieldText="CARDIFF""

So for some unknown reason the two other indexes are making the optimiser stop using the unique index which the optimiser finds when no other index exists.

Just for the record, this problem was found on JET 4.0 9756.0 on x64

I just throw this in here as dbOpenForwardOnly is a usual trick for speeding up lookups but apparently not always