sql server 2005 - Which is the best choice in delete-insert vs if-update else-insert? -

update:

my bad...i have primary key on tables..i meant no further indexing on tables. might have in future after seeing performance , since have many filters on data in retrieving data did not show improvement on indexing last time ran database tuning.

i have 4 huge tables on millions of records. there stored procedure called , updates these table. here scenario -

now if entry exists today need update today , else if entry not there user need go ahead , insert entry user. there 2 ways go carrying out these since there single proc -

first way -

if exists(table1) --update condn else --insert if exists(table2) --update condn else --insert if exists(table3) --update condn else --insert if exists(table4) --update condn else --insert 

second way -

delete table1 condn delete table2 condn delete table3 condn delete table4 condn  insert table1 entry insert table2 entry insert table3 entry insert table4 entry 

now second way looks simpler might more time consuming ...i not sure way best here. can please or guide me out here..thanks!

the if-exists-then-update-else-insert approach may more code delete-insert, (depending on how many , kind of indexes defined on tables) lot less work server.

• a delete or insert operation requires every index modified, period.
• an update operation requires these indexes modified fields have been updated in instance.

so unless modify every indexed field of record updates, longer approach more efficient one.

---

edit: question update says not have indexes apart primary key (which assume clustered key). in comparison:

when row exists, it's 1 clustered index seek (assuming find records pk) and:

• delete-insert: 4 write operations (delete row, delete pk index row, insert row, insert pk index row)
• check-update/insert: 1 write operation (update row)

when row not yet exist, it's 1 clustered index seek and:

• delete-insert: 2 write operations (insert row, insert pk index row)
• check-update/insert: 2 write operations (insert row, insert pk index row)

the more indexes exist, worse delete/insert perform rows exist. plus can cause non-clustered indexes become unnecessarily fragmented due avoidable write operations.