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

Source: Internet
Author: User
Tags rtrim

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)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.