CODEWITHSUNDEEP
sql-serverindexingsql-updatequery-optimization
Strayda
Strayda

Reputation: 27

Can indexing optimize UPDATE statements?

I am currently trying to optimize an UPDATE statement that performs once a day to update a column for over 10 million rows and it takes hours. It is very simple:

UPDATE table SET column = ''

This table has only 2-3 indexes which don't include the mentioned column.

Since the majority of the rows are already updated, can an index only for the mentioned column make sql server seek the column faster for a faster update? Thanks!

Upvotes: 0

Views: 235

Answers (1)

at54321
at54321

Reputation: 11728

Since you say that "the majority of the rows are already updated", you should probably add a WHERE condition, so as to only update the rows that really need to (as others have already said). Without a WHERE clause, all records will be updated, even those that already have the same value ''.

So even if 90% of your records needed to be updated, it would still be a bit faster if you avoid the redundant updates. But in such cases having an index on that column can only slow the query, because every index has to be updated when the underlying table records are updated.

In order to benefit from an index, you need a query with low selectivity, just as with SELECT-queries. What can be considered "low enough" depends on many factors. Sometimes an index can help for 10-20% selectivity. Other times, contrary to popular belief, it can make things worse even for 1% selectivity.

Upvotes: 1

Related Questions