adOpenStatic Logo
Navigation
Home
FAQ
Ken's Blog
Resources
Contact Ken
Copyright 2000 -



The challenge:
People often ask how to get the autonumber of a newly inserted record in Access. Many people assume that the only way to do this is using a Recordset and .AddNew. However, according to Microsoft's KB article Q232144 the Jet OLEDB Provider now supports @@Identity. (A third way would involve a transaction wrapped around INSERT and SELECT MAX() queries, but I don't see this being faster than @@Identity).

So which is faster?
I did some tests earlier which seemed to indicate that the OLEDB @@Identity method was faster. Having nearly finished upgrading my lab at home I decided to re-run these tests. I choose to test three methods:

  • The OLEDB Provider using @@Identity
  • The OLEDB Provider using a Recordset and .AddNew
  • The ODBC Driver using a Recordset and .AddNew

The Server
The server is an IBM Netfinity 1000 with a PentiumIII 650 MHz CPU and 384 MB of ECC SDRAM. It is running Windows2000 Server (with SP2) and IIS v5 with MDAC v2.5 (SP2) and the v5.5 VBScript Engine.

The Results
I only ran one type of test using the millisecond timer available from aspfriends.com. When I get some more client machines into my home lab I will test with WAST to see which method scales under load.

Each test was run 30 times with the results averaged. The result was that each method took exactly the same amount of time: 2003 milliseconds to insert 50 records.

Conclusion: it doesn't really matter which way you do this - with fast enough processor, and enough RAM, the limiting factor appears to be the Jet Engine itself rather than ADO, ODBC or OLEDB.

The Code
Each test involved repeating an insert operation 50 times (and extracting the new ID) and noting the total amount of time it took. The actual code used in it's entirety (plus the sample database) can be download here.

The database was a simple Access 2000 database with 1 table (table1). This contained two fields:

  • Table1ID - Autonumber
  • Table1Text - text, required, length=50, not indexed

The substantive code for the OLEDB (@@Identity) method was:

<%
For i = 1 to 50

   Set objConn = Server.CreateObject("ADODB.Connection")
   objConn.Open strConnect

   strSQL = "INSERT INTO table1 (Table1Text) VALUES ('" & i & "')"
   objConn.Execute strSQL

   strSQL = "SELECT @@Identity"
   Set objRS = objConn.Execute(strSQL)

   intID = objRS.Fields.Item(0).Value

   objConn.Close
   Set objConn = Nothing

Next
%>

The code for the OLEDB and ODBC methods using .AddNew was:

<%
For i = 1 to 50

   Set objConn = Server.CreateObject("ADODB.Connection")
   ' For OLEDB we use strConnect, for ODBC we use DSN
   objConn.Open "DSN=AutoNumberTest"

   Set objRS = Server.CreateObject("ADODB.Recordset")
   objRS.Open "Table1", objConn, adOpenKeyset, adLockOptimistic, adCmdTable
   objRS.AddNew
   objRS("Table1Text") = i
   objRS.Update
   intID = objRS.Fields.Item("Table1ID").Value
   objRS.Close
   Set objRS = Nothing

   objConn.Close
   Set objConn = Nothing

Next
%>

Any comments on the above are more than welcome!
Back to the experiments listing.