Explore SQL to add nonclustered indexes, dozens of times times the puzzle of performance improvement

Last week, technical support reflects: a customer's query operation takes about 6.1min, after the follow-up code, simplifying the database query still has little effect. Later, after analyzing SQL, the technical director adds a nonclustered index (three fields) to one of the tables, and the same query operation takes only 6s-7s.

The original SQL will probably need to leftist left join more than 10, the left join before and after the two table is a Cartesian product. So as long as there is a large number of data in one of the tables, the data set that SQL needs to retrieve is extremely large. After adding a nonclustered index based on the field that is filtered in the where ( at least two fields ), the database automatically replicates a copy of the data, and then the SQL query goes to the replicated data retrieval, which greatly reduces the time of the database response. So is the more nonclustered indexes the better? Typically, a table can have only one clustered index, but there may be multiple nonclustered indexes . The main purpose of database indexing is to improve the performance of SQL Server system, speed up the data query and reduce the response time of the system . But this does not mean that the more indexes (nonclustered indexes) the better. An appropriate index design can significantly improve performance, and vice versa. When our technical support to the customer Design index, the query time is still not improved as scheduled. This is an example of an inappropriate nonclustered index design. Because the three fields (mentioned earlier) contain a field: field time, and this field is dynamically changing, a filter for the client query operation is time, so it does not take effect as scheduled, because the SQL query did not hit the copied dataset. So when this field is reduced, the query time goes back to 6s-7s, and the hit rate increases significantly.

In addition, the increase in nonclustered indexes increases the cost of hard drives (or memory) , and there is no double benefit in the world, so it is particularly important to design indexes appropriately.

Explore SQL to add nonclustered indexes, dozens of times times the puzzle of performance improvement