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 |
|
malachi151
Posting Yak Master
152 Posts |
 Posted - 2012-02-13 : 17:55:47
|
| I have a situation where when I create a SELECT query and run it on a database the execution time is about 50 seconds, however when I run the same query as part of an update statement, i.e. updating columns in a table using the exact same columns that are returned in the prior query, the execution time goes from 50 seconds to over 10 minutes, BUT, the execution plan says that the UPDATE portion of the statement is only 20% of the cost, so how can this be?I would think that if the query takes 50 seconds, and the update takes 20%, then the execution time with the UPDATE would be around 1 minute, perhaps a little more... |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-02-13 : 18:15:47
|
| First off, the percentages are relatively meaningless, especially if you're mixing SELECT and UPDATE/INSERT/DELETE.A SELECT statement only reads data, and may be reading only from data cache in RAM and not accessing the disk at all. It also may be seeking or scanning an index and not touching the table either.There are a number of things that can slow down an update: An UPDATE has to write data to the data cache, then the transaction log, then data pages on disk. It also has to update all the indexes that contain columns being updated, and these also have to update in cache/transaction log/disk. If there are triggers on the table, these fire and may perform other actions. And if you're using database mirroring in high-safety mode, the log must be written to the mirror before it can commit on the principal. |
 |
|
|
|
|
|
|
|
UPDATE taking a long time, but EP says no...