Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

SQL Server and Azure SQL: How to Delete or Update Data in Large Tables

Post Views 1,273 views
Reading time 4 minutes

Introduction

Hey guys!
In this article I would like to share with you a small code that I needed to use today to UPDATE a relatively large table (55M+ records) in an Azure SQL Database base and, after 1h and 30 mins waiting, there was a connection error and I had to do it all over again.

Not only in this case, but it can happen to overflow the log and give an error in the operation too, and, as we know, when an error occurs during UPDATE or DELETE, the automatic rollback is started and no row is actually changed. Breaking this single operation into smaller, segmented operations will allow the log to be flushed and thus minimize possible log overflows.

You may also want to break these large, time-consuming operations into smaller chunks to be able to track the progress, or your maintenance window is not long enough to process the UPDATE/DELETE on all the necessary lines and you want to continue in another window.

I've also seen cases of tables with triggers and that can end up generating a very large overhead when executing an UPDATE/DELETE that changes many rows. And we also have to remember locks on the table, which, when broken into smaller parts, can be released quickly while the next batch is starting processing.

There are several reasons that can influence the decision to participate in a large UPDATE/DELETE and in this article I will show some easy ways to do this.

Important: In case you just want to delete or change the first or last records of a table, you can use the solution I shared in the article SQL Server - How to perform UPDATE and DELETE with TOP x records.

How to delete or update data in large tables

UPDATE partitioned by integer field
In the example below, I'm using an integer type autoincrement column to assemble the ranges of values that I'm going to update. The @Increase variable defines the number of lines of each batch that will be updated and I defined in this example that the number of lines updated at a time will be 1 million lines.

UPDATE partitioned by integer field

Result:

UPDATE partitioned by date field
In the example below, I'm using a date column to assemble the ranges of values that I'm going to update. The @Aumento variable defines the number of days for each batch that will be updated and I defined in this example that this number of days that will be updated for each block will be 30 days.

UPDATE partitioned by date field

Result:

DELETE TOP(N) partitioned using percentage
In the example below, I am deleting 10% of my table every iteration. As the data is erased, I don't need to control intervals, I just erase every 10%.

DELETE TOP(N) partitioned using percentage

Result:

Observation: This solution can have problems with very large tables, as 10% can represent a very large volume of rows. And when few records are left, the 10% can take many iterations to clear.

DELETE TOP(N) partitioned using number of rows
In the example below, I am deleting 500K rows from my table with each iteration. As the data is erased, I don't need to control intervals, I just erase every 500,000 lines until there are no more lines left that meet the filter criteria.

DELETE TOP(N) partitioned using number of rows

Result:

And that's it, folks!
I hope you liked it and a big hug!