openrecordset is SLOW

[meyerryang]

How do I speed up this recordset (for time stamp). I am adding a new record
and it is taking a long time - before the records were added it was pretty
fast. As more and more users use the system and create time stamps the
system is slowing down dramatically. The underlying table that the record is
added to is a server table. I would like it to open a "new record" and then
add a record (not just opening the table). Code is provided, please help:

Dim rs as DAO.Recordset
Set rs = CurrentDb.OpenRecordset("MyTable", dbOpenDynaset)
rs.AddNew
rs!MyField = now()
rs.update
rs.close: set rs = Nothing

 

[ruralguy via AccessMonster.com]

I would try using Tony's suggestion of a persistant connection.
http://www.granite.ab.ca/access/performancefaq.htm

 

[Marshall Barton]

How do I speed up this recordset (for time stamp). I am adding a new record
and it is taking a long time - before the records were added it was pretty
fast. As more and more users use the system and create time stamps the
system is slowing down dramatically. The underlying table that the record is
added to is a server table. I would like it to open a "new record" and then
add a record (not just opening the table). Code is provided, please help:

Dim rs as DAO.Recordset
Set rs = CurrentDb.OpenRecordset("MyTable", dbOpenDynaset)
rs.AddNew
rs!MyField = now()
rs.update
rs.close: set rs = Nothing

Another point is that you are loading all the records in the
table just to add a new record. Let's try to find a speedy
way to load just 0 (or at most one record).

First, try using the OpenRecordset's Options argument:

....OpenRecordset("MyTable", dbOpenDynaset, dbAppendOnly)

If that doesn't speed it up enough, use a query with a WHERE
clause that uses the primary key index (preferably where
there will be no matching records):

Dim SQL As String
SQL = "SELECT MyField FROM MyTable " _
& "WHERE PKfield = 0"
....OpenRecordset("MyTable", dbOpenDynaset)

One of these ideas may or may not be sufficient. If not,
try them with Allan's (Tony's) persistant connection too.

 

[meyerryang]

Yes, this is exactly right. I created a query called TimeStamp (instead of
using the MyTable directly). The query had a criteria of ID is null, which
only allows new records to be added. This sped it up ten fold. Thanks.

 

[Marshall Barton]

Yes, this is exactly right. I created a query called TimeStamp (instead of
using the MyTable directly). The query had a criteria of ID is null, which
only allows new records to be added. This sped it up ten fold.

Good idea! I should have thought of using Null.

The really important point is to match an indexed field
(unique, Nulls not allowed). It is not uncommon to see
suggestions of using a where clause that is the equivalent
of WHERE False. While that will indeed return zero
records, with Jet it requires a complete table scan because
it does not allow indexes to be brought into play.

It would be possible for a database engine to recognize an
intrinsically false where clause and completely skip the
record retrieval process, but Jet does not do this. Many
years ago, I had assumed that such a seemingly obvious
optimization was included, but I had no situations that
actually demonstrated its existence. Fortunately David
Fenton did and posted a performance test that dramatically
highlighted that the use of a field with a unique index
provided the orders of magnitude performance gain you have
seen.