Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
chedderslam
Posting Yak Master
223 Posts |
 Posted - 2003-01-28 : 23:36:31
|
| This came up in a discussion with a fellow developer:He said that, as a rule of thumb, an update query is slower than a insert or a select.I said it was nominal, as 90% of the execution time goes to the query plan. good conditional's on your selects, inserts, and updates for the pk to be hit would end up with all pretty darn close.The only factor I could figure in was disk io to write on an update or insert, vs a select.I think, with good indexing, the where clauses used in a select, insert, or update should make all three about the same for execution time/performance.On sql server 2K...On a side note, I should get out more... :D |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-01-29 : 00:05:57
|
| I'm pretty sure UPDATES are slower, because if you have indexes, especially CLUSTERED indexes, the indexes need to be updated when the record is written. If SQL Server has to put the record on a new page, that makes even more work.I think the general rule of thumb is that if you have a lots of read/writes to a table, watch the number of indexes you put on it. If you have a table that you do few writes to, you can put a good numebr of indexes on the table to speed up all the reads you are doing to the table.So, SELECT and UPDATE speed rely on the hardware in question as well as the number and type of indexes you haev on the tables in question.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-29 : 00:10:12
|
Well, it certainly will be slower than a SELECT statement will, because it will have to write information. A SELECT may have to read data from disk anyway, but the UPDATE will not only have to write it to disk, but will also incur extra overhead to write the transaction log records; a SELECT will not. Also, an UPDATE will have to modify any indexes affected by the changes in the data, with additional writes to the transaction log. The more indexes there are, the more overhead will be involved.An INSERT should be a little faster than an UPDATE because it is purely a write operation, and it is not modifying existing data, so would not have to maintain before and after values in the transaction log. Depending on how the table is clustered, an INSERT would very likely write sequentially onto the end of data page. If there are a lot of indexes, they will all need to be updated with the new entries, whereas an UPDATE would only need to update the indexes if its key(s) are updated.<edit> Yeah, what Michael said! </edit>   Edited by - robvolk on 01/29/2003 00:15:24 |
|
|
|
|
|
|
|
|
sql silly question