Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Why the Indexes on table slow down the DML operation on table, what is the exact reason? Expand / Collapse
Author
Message
Posted Friday, March 7, 2014 5:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 3:00 AM
Points: 21, 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..
Post #1548702
Posted Friday, March 7, 2014 6:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:34 AM
Points: 14,513, Visits: 12,556
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?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1548703
Posted Friday, March 7, 2014 6:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 3:00 AM
Points: 21, Visits: 160
Thank you for quick response,

It can be update\Insert.
Post #1548706
Posted Friday, March 7, 2014 6:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:34 AM
Points: 14,513, Visits: 12,556
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?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1548707
Posted Friday, March 7, 2014 6:59 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 11:46 AM
Points: 15,519, Visits: 29,733
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
Post #1548722
Posted Friday, March 7, 2014 7:45 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 2, 2015 4:09 PM
Points: 939, Visits: 3,142
So ... was this a job interview question, or was it a test?
Post #1548743
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse