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

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!