Yesterday afternoon, Manager Ji of Xuzhou baida Supermarket Co., Ltd. called again and said that the "sales query" module in the supplier service system could not run normally. Why? Open the computer and use "remote login" to log on to their server. It's no surprise that they have recently changed the optical fiber, however, we recommend that you disable remote logon ......
Let's get down to the truth and read CodeNo problem. It's strange ...... The SQL statement is intercepted and executed in the query analyzer. The result is returned, but it takes nearly two minutes. What is the problem? Count, we found that there are already more than 5.7 million rows in the table, and the running time is too long. The daily sales details have reached so much. It is no wonder that the query is slow. But it shouldn't be so slow. Well, let's take a look at the table structure and there is no primary key inverted (the data is imported by another guy from the MIS system, and it's not surprising if there is no primary key ), even if the index is not created, the index is created, and the operation speed is much faster. it slightly increases the query time limit length and the IIS page timeout time, and basically runs, but it is still difficult, probably because the server performance is not good. After analyzing the business requirements, the Kao and sales query pages only provide the latest three-month data queries. Why are there so many data in the table? Reflect the situation to them.
The data has been cleared this morning. They have created another table to back up expired data. After reading it, there is no index for the new table, count is used to calculate the total number of records at 1 minute 40 seconds. There are more than 3 million data workers. The original table (I created an index yesterday) contains more than 2 million data records, and count is used to calculate the total number of records, the index takes only two seconds. Index database ProgramYou do not need to scan the entire table to find the required data. Otherwise, the specified query cannot be completed even when the machine is exhausted.
Note: nearly 6 million of the data is really attractive.
