Indexes and Deletes/Updates

Question

Indexes and Deletes/Updates

@SixStringSQL

Ten Centuries

Points: 1026
More actions
June 8, 2011 at 9:38 am

#237532

OK, here's an 'easy' one that is hard to google...
Will an index help speed up a delete or update? IE, help find the row faster?
And does the same selectivity percentage apply?
Thanks!
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Mike John SSCertifiable Points: 7216 More actions · Answer 1

I would say identical for the "locate what to update" part. but then obviously a slight overhead incurred due to the need to update the index(es) if the column(s) that were being changed are included in an index.

Mike

mister.magoo SSC-Forever Points: 47174 More actions · Answer 2

I dispute that it is hard to google that question:

http://tinyurl.com/3t5p6es :w00t: 😀 😛

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

@SixStringSQL Ten Centuries Points: 1026 More actions · Answer 3

Mike John (6/8/2011)
I would say identical for the "locate what to update" part. but then obviously a slight overhead incurred due to the need to update the index(es) if the column(s) that were being changed are included in an index.
Mike

Well, Mike, the common response to an index is "It will slow down insert/update/deletes, due to maintenance."

However, I'm thinking to myself...Wouldn't also HELP a delete|update, 'cause it has to find the row in the first place?

When you say "identical", do you mean that the performance gain to locate the row is the same gain as the index gives to a select?

Thanks for your reply!

-G

Mike John SSCertifiable Points: 7216 More actions · Answer 4

As you say people will often say it "slows down updates etc" , but if suitably selective it will help the retrieval part, so overall will be often be beneficial.

If in doubt

set statistics io on

run query

add/drop index

run query

and compare the disc io figures

Mike

Revenant SSC-Forever Points: 42467 More actions · Answer 5

On tables that span several pages that are not cached - and that is an important condition -, index speeds update ops significantly. The exact percentage of improvement will be a function of your hardware, in my case (16 GB Sandy Bridge or Nehalem boxes with RAID5) 20 percent or more.

If the table is very small and sitting in cache, there is no measurable difference.

These are general results of my routine and repeated measurements on sizeable 2008 R2 databases.

@SixStringSQL Ten Centuries Points: 1026 More actions · Answer 6

@SixStringSQL

Ten Centuries

Points: 1026

June 8, 2011 at 10:09 am

#1335793

Thank you, Gentlemen! Very helpful!

-G