I cut the time of my UPDATE query in half by playing with a database index

As the title suggests, I reduced the time it took to run an UPDATE query by doing something that didn’t initially occur to me.

About the table

I have a MS SQL Server database table that has ~50 million rows of data. Each week, another ~100,000 rows of data is inserted.

Throughout the week, users only perform SELECT queries on it. It’s only during my process of importing in new data that I run various INSERT and UPDATE queries.

The table’s Primary Key is a single field (auto-incrementing number). I have a few indexes (“Non-Unique, Non-Clustered”).

The Problem

I have a handful of saved queries to import and then manipulate the new weekly data. One of the steps is to run an UPDATE statement on the entire table.

For my use case, I’m unable to run an UPDATE statement on only the new rows, but need to update every record.

This takes several minutes to run, and I was looking for how to speed it up.

The Solution

The DBA suggested that before the UPDATE query, I simply drop my indexes. Afterwards, re-add the indexes.

The pseudo-code looks like this:

DROP INDEX myIndex ON dbo.myTable

UPDATE myTable SET ...

CREATE NONCLUSTERED INDEX myIndex ON myTable ...

While the query still takes several minutes to run, it does so in about 1/2 the time!

An Added Bonus

The last step of my process is to rebuild the indexes:

ALTER INDEX ALL ON dbo.myTableName REBUILD;
ALTER INDEX ALL ON dbo.myTableName REORGANIZE;

Now that the index is being re-built anyways, there’s no need to run those additional queries.

Another Take Away

I’m pretty familiar with SQL, and in my small team I’m probably the most knowledgeable. I’ve been running this process the same way for a few years, and was confident it was as efficient as possible. But there’s always someone smarter who has good ideas that can save you time. Listen to them!