SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Why the Indexes on table slow down the DML operation on table, what is the exact reason?


Why the Indexes on table slow down the DML operation on table, what is the exact reason?

Author
Message
Tony1234
Tony1234
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 160
Hi All,

Greetings!

Why the Indexes on table slow down the DML operation on table, what is the exact reason?

Many Thanks in advance..
Koen Verbeeck
Koen Verbeeck
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43024 Visits: 13286
Which DML operation?

If you're talking about updates/inserts/deletes, that's because the indexes themselves also need updating.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Tony1234
Tony1234
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 160
Thank you for quick response,

It can be update\Insert.
Koen Verbeeck
Koen Verbeeck
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43024 Visits: 13286
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.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71943 Visits: 32910
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Nevyn
Nevyn
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2498 Visits: 3149
So ... was this a job interview question, or was it a test?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search