SQL Optimization combat: from 1353 seconds to 135 seconds (delete index + Modify data + Rebuild index)

Recently, in the optimization of the daily-knot stored procedure, the daily-knot stored procedure probably contains more than 20 stored procedures.

Found that there is a problem with a stored procedure code, further found that the balance of the data there is a date field business_date is problematic, this field corresponds to the type varchar, but the type of the stored procedure passed in the parameter is char, resulting in the last balance of the data at the end of a few more spaces.

For example, it should be ' 2016-12 ', but now it's ' 2016-12 '.

To solve this problem, to modify the value of this field, remove the trailing space, and then run the following statement:

[SQL]View Plaincopy print?

1. Update Tb_customer_frozen
2. Set business_date = RTrim (business_date)

Update Tb_customer_frozenset business_date = RTrim (business_date)

Operation Result:

(10483163 row (s) affected)

Data volume of about 1000w, running time 22 minutes 33 seconds, that is, 1253 seconds, really is too slow ...

There must be a reason to be so slow.

So I looked at it. There are 2 indexes in the original table, a clustered index (business_date field), a nonclustered index (also containing the Business_date field), which is the equivalent of changing the index value each time the data is modified, which is why it is slow.

Think, You can delete the index, and then update the data, in rebuilding the index, the last total time: 6 seconds + 1 minutes 29 seconds + 60 seconds = 135 seconds.

Summarize:

This fast reason is the bulk operation, not every time you modify a piece of data to modify the index, but to delete the index, so that the modified data will not involve the index, and then bulk modify the data, and then rebuild the index is a batch operation, so the speed is fast.

SQL Optimization combat: from 1353 seconds to 135 seconds (delete index + Modify data + Rebuild index)