A series of problems caused by the large number of SQL Server database records

Source: Internet
Author: User

This is a question from my colleagues in the QQ group. At the same time, the problems he encountered also change over time, but they are all related to a large amount of data.

1. delete problems in large data volumes

2. update problems with large data volumes

The colleague said that his database has many data records, which are about several million records. Deleting a record cannot be completed within 10 minutes. He has a headache. Ask if there is any solution. I think of my article <about the architecture of large asp.net application systems-how to achieve high performance and high scalability>. I mentioned that delete should not be done in real time. It can be processed in a centralized manner. In real time, you only need to delete the record (use the update Statement ). When the system is not busy at night, it will be processed in a centralized manner to delete records. Then this colleague will do it in this way. Later, I was told that the delete problem was solved, and it was faster. Although I have never seen what his database looks like, I am also very happy to help him solve the problem.

After a few months, he had a new problem. He said that it would take a long time to mark the record to be deleted (that is, an update statement) with more data, tens of minutes. I asked a question about the upgraded version of the previous question. He now marks the records to be deleted and uses an update statement with the WHERE condition. Find the record of the WHERE condition, that is, a search process. Now it takes a long time to search by the WHERE condition. I asked him what the WHERE condition is, what the cluster index is, and what the non-cluster index is. The WHERE condition does not match both the cluster index and non-cluster index. Therefore, even an Update is time-consuming. So he talked about how to select cluster index, such as short fields, and the one-way incremental feature. He did as I said. Then it would be much faster to mark a record with tens of millions of records that need to be deleted. It takes only one second to complete. I am very happy again.

Hope to help you!

Related Article

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.