Скачать или смотреть Speeding Up an SQL Server Update Query on Large Tables: Effective Strategies

Discover how to optimize an extremely slow update query in SQL Server, particularly when dealing with large datasets. Learn effective strategies to enhance the performance of your SQL queries!
---
This video is based on the question https://stackoverflow.com/q/63198611/ asked by the user 'Chipmunk_da' ( https://stackoverflow.com/u/6900402/ ) and on the answer https://stackoverflow.com/a/63198725/ provided by the user 'GMB' ( https://stackoverflow.com/u/10676716/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: SQL Server update query extremely slow

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Speeding Up an SQL Server Update Query on Large Tables: Effective Strategies

If you've ever tried to run an UPDATE query on a database containing a vast amount of records, you might have run into performance issues that left you scratching your head. Just recently, a user encountered a situation where a simple update statement on a table with approximately 200 million records took an astonishingly long 40 minutes to execute, leading to a cancellation that took another 30 minutes! If you've found yourself in similar predicaments, this guide is for you.

The Problem

The initial SQL update query that sparked the issue was designed to set a flag (hybrid_trade_flag) based on whether certain records in the tbl_hybrid_trades_subset table were linked to the tbl_master_trades table. Here’s a look at the original query:

[[See Video to Reveal this Text or Code Snippet]]

Despite having an index on the trade_id of the large table, the execution took an unacceptably long time. After trying to split the operation into two queries, the performance didn't improve significantly, taking nearly 30 minutes to run both.

Understanding the Solution

To tackle the problem of slow SQL queries, it’s essential to optimize your approach. Let’s break down the suggestions into clear steps for effective query optimization.

1. Use EXISTS Instead of JOIN

One way to potentially enhance the performance of your update statement is to rewrite it using the EXISTS function. This method can streamline the query by checking for the existence of a related record rather than performing a join operation. Here’s how you might structure that query:

[[See Video to Reveal this Text or Code Snippet]]

2. Optimize Indexing

Indexing plays a crucial role in speeding up database queries. Here are some indexing suggestions to consider:

Index on tbl_hybrid_trades_subset(trade_id): This will facilitate quick lookups for trade IDs in the subquery, significantly enhancing execution speed.

Index on tbl_master_trades(trade_id): While already having an index that includes another column, consider creating a direct index on just trade_id to potentially improve performance further — though the index for the subquery is of higher importance.

3. Acknowledge the Scale of Data

It's essential to realize that working with 200 million rows will inherently lead to longer processing times, no matter what changes you implement. Even with optimization, some queries could still take a significant amount of time to execute.

4. Paginate Large Updates (If Applicable)

If you have multiple updates to apply in a sequential manner, consider breaking your updates into smaller batch processes. This technique reduces the strain on the database and can help in managing long-running queries better.

Conclusion

In conclusion, optimizing your SQL queries is vital, especially when dealing with substantial volumes of data. Implementing the use of EXISTS, ensuring appropriate indexing, and recognizing the scale of data involved are essential steps in enhancing the performance of your SQL operations. Although performance improvements may still take some time due to the sheer size of the data, with the right implementation, you can significantly reduce query execution times and avoid downtime.

By following these strategies, you can turn what seems like an insurmountable data update issue into a manageable and efficient process. Happy querying!