Which is faster insert query or update in sql?

Question

In my app,I have a requirement to insert 600 rows and each row have 10 parameters that means I am inserting 6000 data for every 5 seconds.So I am deleting the previous value and inserting 600 rows for every 5 seconds.The other way is I can update the previous data using where Clause.So wondering to know which will have least performance issue.Also if I have some millions of rows and If i want to select the data for ID 50000.Then I will write this way

select * from mytable where ID=50000.

After some time If i need the data for id 90000 then Will the sql server search from beginning ID(ID=1) for every select statement or it will directly move to ID=90000 My query is will sql server starts looking from id=1 till it matches the where clause or how it searches?

EDIT ID is Primay key and auto increment

Answer 1

I totally agree with Aakash' answer that UPDATE is most probably the faster approach here.

---

However I would like to clear up a potential misunderstanding (or lack of understanding) on how a relational database works:

If i need the data for id 90000 then will the sql server search from beginning ID(ID=1) for every select statement or it will directly move to ID=90000

Assuming ID is the primary key in your table (or has a unique index defined) then SQL Server will look up the row with ID=90000 directly (actually pretty much every relational database will do it that way).

Finding the row with id=1 takes the same amount of time as finding the row with ID=90000 or ID=90000000

I suggest you take some time to read the SQL Server manual to understand how a relational database works. The following topics might be interesting for you:

• http://msdn.microsoft.com/en-us/library/ms190659%28v=sql.100%29.aspx
• http://msdn.microsoft.com/en-us/library/ms190623%28v=sql.100%29.aspx
• http://msdn.microsoft.com/en-us/library/bb500155%28v=sql.100%29.aspx

Additionally you might want to have a look at "Use The Index Luke": http://use-the-index-luke.com/

There is a lot of very good information on how indexes work and how the database uses them.

Answer 2

Generally, UPDATE is much faster than DELETE+INSERT, it being a single command.

The bigger the table (number of and size of columns) the more expensive it becomes to delete and insert rather than update. This is because you have to pay the price of UNDO and REDO.

Also, keep in mind the actual fragmentation that occurs when DELETE+INSERT is issued opposed to a correctly implemented UPDATE will make great difference by time.

Answer 3

Insert would be faster because in case of update you need to first search for the record that you are going to update and then perform the update.

This hardly seems like a valid comparison as you never have a choice whether to insert or update as the two fill two completely different needs. :)