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")
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.
|
