INSERT versus ADDNEW [RESOLVED]

[Pasvorto]

Which do you find the most efficient? Does it make a difference?

INSERT INTO tablename ([fieldnames]) values (...)"

or

Set rs as adodb.record
rs.open...
rs.addnew
...
...
..
rs.update
rs.close
set rs = nothing

[Hack]

I can't attest to speed as it isn't something that I've every bothered to check, but I do not like using data controls, so I always use SQL.

[techgnome]

My experience has been that 1000 .AddNews followed by .Update (or even .BatchUpdate) is slower than the same 1000 inserts using an SP and Insert Into.

I think it has to do with shuttling the entire data set across to the DB (even the unchanged tables) rather than just the simple command to insert the data. Generaly, if I can help it, I *always* use SPs and SQL rather than recordsets to manipulate my data. But I could just be weird like that.

Tg

[dannymking]

Insert Into every time...

Even more efficient if your value source can be loaded into a table beforehand (Imported data) rather than numerous values statement..

[RobDog888]

Recordsets are very slow and take up more memory as they are increasingly added to. Insert Into statements all the way too.

[RhinoBull]

... Even more efficient if your value source can be loaded into a table beforehand ...

What in the world would that mean? I wish you can explain.

[RobDog888]

Maybe he means to populate the table via Insert Into statements and then poulate a rs.

[RhinoBull]

Recordsets are very slow and take up more memory ...

Recordsets are slower but not very much - it depends on the size, type, cursor location. Under surcumstances using AddNew would be better choice as you can immediatley CancelUpdate for a specific record.

[RobDog888]

Not necessarily. You can use the connection objects .BeginTransaction to start a trans and then Insert Into statements can be executed. If all goes well then you can do a oCn.CommitTransaction. And if an issue occurs then a oCnn.RollbackTransaction.

You can do it on a single insert statement or like a batch commit if you want.

[RhinoBull]

And you will be creating transactions for each record while in some loop or something? Transactions are good when you do bulk insert/update but for a single record often is a waste.

[RobDog888]

Just showing an alternative to .CancelUpdate

[dannymking]

I meant that if the values were to come from something like a text file, or spreadsheet or some other importable source then the insert into would only need to be ran once and would take less code...

[Pasvorto]

Thanks for the input, folks. I have begun converting a lot of ADDNEWs to INSERTs. Some of you may remember my problem with the db not writing all the record to the database (some fields are written, others are not). Since I converted those 2 transactions, I have not had a single instance of that occurance. It gave me the idea to convert all the ADDNEWs. I was looking for opinions. Looks like I am making the right choice.