sql insert into vs. adodb.recordset addnew 3

[mlowe9]

What are the pros/cons of each?
Is there a performance difference?
Which is better for a small amount of data?
Which is better for a large amount of data?

 

[steven290]

actually insert will be better, because addnew requires you to open a recordset first. There is now need to open a recordset if you are updating or inserting.

Performance is better with insert

 

[pkailas]

Inserts will be faster as they don't need to scan any tables or indexes.

However a little trick to speed up an "addnew" method is to set up a filter that you know will always return no records.. like "SELECT * FROM TABLE WHERE 1 = 2"

Since 1 will never equal 2 you will return an empty recordset and then you can addnew.


_______
I love small animals, especially with a good brown gravy....

 

[mlowe9]

What I think I'm going to do is delete all data in the table first, then add the records back. This is because the way my web page is formatted, I won't really be able to tell what records may have been changed. The user will be able to change multiple records at once, then do a "Save Table".

So will doing an insert command for each record be better than opening a recordset, then using the addnew method for each new record?

Thanks...

 

[pkailas]

absolutely

_______
I love small animals, especially with a good brown gravy....

 

[steven290]

you can run a delete query then an insert. Can you give an example of what you mean, so we can be a little more detailed

 

[mlowe9]

I was going to do something like (except the syntax would be correct)

conn.execute("delete * from TABLE")
for each newrecord
conn.execute("insert newrecord")
next

 

[steven290]

whats "newrecord" an array

 

[steven290]

why woould you want to delete the entire table every time, maybe you should reconsider this? whats the usage in this webpage?

 

[FesterSXS]

are you actually adding new records or just editing existing ones? Your 2nd post makes me think you are just updating records. In which case then use the SQL UPDATE command.

"UPDATE myTable SET field1='test', field2='another test' WHERE id=4"

Tony
_______________________________________________________________

http://www.SpiraxSarco.com/

 

[mlowe9]

I was wanting to allow the user to change as many records as they wanted before saving the entire table. I didn't think I could do this using update (I didn't think I could identify each record), but this conversation has shed some light on me.

For that you get a star. I will re-approach my thinking on that subject.

So let's say I can do the update thing. Can I assume the sql update for each record is better than using a recordset and the update method?

 

[FesterSXS]

yes you can certainly assume that.

You should be able to do a single UPDATE to update all the edited records

Tony
_______________________________________________________________

http://www.SpiraxSarco.com/

 

[FesterSXS]

thanks for the star too

Tony
_______________________________________________________________

http://www.SpiraxSarco.com/

 

[steven290]

now do you you want to update multiple records or do you mean multiple fields within a record

because this only updates 1 record but multiple fields where the id is 4

"UPDATE myTable SET field1='test', field2='another test' WHERE id=4"

 

[FesterSXS]

I assume its multiple records and you're right the example I gave doesn't do that but it can still be done with a single UPDATE statement. I didnt want to overcomplicate things too early.

Tony
_______________________________________________________________

http://www.SpiraxSarco.com/

 

[steven290]

i know you know, i was asking mlowe

 

[mlowe9]

Multiple records

 

[steven290]

i'm assuming you know how to do that, because that was not your original question. If you don't post a new thread and give some sample code.

 

[mlowe9]

steven,

I posted a new thread because I was having trouble doing it, and all the responses I got on it are that you can't do multiple UPDATES in one statement.

Any suggestions? I'm using Access...