March 7, 2014 at 5:59 am
Hi All,
Greetings!
Why the Indexes on table slow down the DML operation on table, what is the exact reason?
Many Thanks in advance..
March 7, 2014 at 6:01 am
Which DML operation?
If you're talking about updates/inserts/deletes, that's because the indexes themselves also need updating.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 7, 2014 at 6:08 am
Thank you for quick response,
It can be update\Insert.
March 7, 2014 at 6:12 am
Tony1234 (3/7/2014)
Thank you for quick response,It can be update\Insert.
There are cases where an index might speed up an update statement, as the index allows to easier find the to-be updated rows, but generally speaking updates and inserts are slowed down by indexes because the index needs to be maintained to reflect the changes.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 7, 2014 at 6:59 am
When you add a row to a table that has an index, the index has to be updated to reflect the new value added. When you modify a value that is in an index, the index has to be modified. When you delete a row, the index has to be updated. It's just extra work. That said, as was already mentioned, when you have to delete or update a row, you also have to search for that row. Having an index can make that search much faster (much, much faster), so the costs associated with maintaining the index are offset by the cost savings of using that index. That's before we even get to the concept of dealing with page splits as data gets inserted or updated in indexes causing the pages to rearrange, an added overhead. In short, it's a balancing act. Picking the right set of indexes, but not too many, for a table is work. Otherwise, you could just put an index on every column, just in case (and I've seen that done, it's a horrible idea that leads to all sorts of problems).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 7, 2014 at 7:45 am
So ... was this a job interview question, or was it a test?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply