ADO AddNew and adOpenKeySet

[MattWoberts]

Hi,

I am using the well-documented method of adding records via ADO, using the addNew method (which allows me to get the id of the record I added).

My code does this:

rs.Open TableName, sConnect, adOpenDynamic, adLockOptimistic, adCmdTable
rs.AddNew

... add data ....

rs.Update
x = rs("ID")

Now, in most cases this is fine, but I have one table that has 300,000 records, and the rs.Open method takes about 5 seconds to execute!!! I can change the "adOpenKeyset" to "adOpenDynamic", and it then runs almost instantly, but I need the adOpenKeySet to enable me to get the ID of the record just added.

Can anyone shed any light on this? Is it something about my table?

Utterly baffled!

Thanks

 

[BogdanMBM]

1.) Are you working with Access on the same machine or with a SQL Server in a network?
2.) Do you actualy add all of those 300.000 rows to the client recordset?!? or you use a "where" clause?

 

[MattWoberts]

I use SQL Server, and Oracle.

The table currently has 300,000 records, I am only adding one.

I just pass in the table name - and I assumed that because I specify adCmdTable, it wouldn't need to load all these records first! Myabe I was wrong...

 

[MattWoberts]

Update:

I also tried this:

.Open TableName, sConnect, adOpenKeyset, adLockOptimistic, adCmdTable and adExecuteNoRecords

The difference here is I have bitwise and-ed "adExecuteNoRecords" onto the end, to tell ADO I am adding only. It still takes about 4 seconds to add one record

And, I aldo tried replacing the table name, with a "select * from [TableName] where ID = -1" - so that it would not return any records. This was fast, but doesn;t allow the addnew method

Hmmmmm....

 

[BogdanMBM]

I think you WHERE wrong. Well, some kind of optimisation IS made by ADO (I think it doesn't actualy return all of the records once, but in pages, something...).
You deffenetly should use a WHERE clause in the statement to be processed by SQL server in order to return data. Or, if you just want to insert a raw in the database, don't return any recordset. Just define an ADO.Connection object and use it's ".Execute" method, something like this:

MyADOConnection.Execute "INSERT INTO MyTableName values( ..bla, bla, bla..)".
If you need to trap the ID of the new record, hmmm..., you need a stored procedure to do this, or at least an user defined function on the server side.

 

[MattWoberts]

Thanks,

What I don't understand is that changing adOpenKeySet to adOpenDynamic works very fast, but it doesn't allow the ID of the record to be returned. I wanted to avoud the "select max(id)..." call.....

We are using ADO/AddNew for a number of reasons, its in the core of a big system so changing it to "Insert into.." is unfourtunately not an option.

 

[BogdanMBM]

Yeah... Forget about the ADDNew method. This is for small databases with small tables and it is limited (you can not trap errors, perform other things on the server side, etc).
Use an ADO.Connection instead! Is faster. And, ... on top of an ADO.Connection, you can open anytime a recordset for displayng purposes. But when you insert/update data in a table on a remote server, you should deffinetly use ADO.Connection, not to mention a stored procedure with a transaction in it. Very important the transaction is! Think about an error in inserting a raw and with no transaction to roll back, you will have some problems...

 

[BogdanMBM]

Heh! You are ahed of me (you are typing faster then me ).
If it is a core problem, hmm, bad luck...
Sadly, I can't help you with the differences between adOpenKeySet and adOpenDynamic...

 

[MattWoberts]

Thanks for the advice, I'll give that a try.

 

[ArtieChoke]

Instead of using the table, create a select statement like:

"select * from <tablename> where 1=0"

but don't include the adExecuteNoRecords option. This will "prime" the recordset with the table schema, but not read in any records. It should go a lot faster.

And the difference between keyset and dynamic is the keyset only loads the table keys, so it's probably loading the entire table's (or a subset) primary keys into memory. Dynamic allows you to know if someone else has modified any of the records you have in your collection, so it needs all the data.

"I think we're all Bozos on this bus!" - Firesign Theatre