Last week, technical support reported that a customer query operation took about 6.1 minutes. After following up the code, the simplified database query still had little effect. Later, the TECHNICAL director analyzed the SQL and added a non-clustered index (three fields) to one of the tables, and the same query took only 6s-7s.
The original SQL may require a dozen left join, and the two tables before and after left join are cartesian products. Therefore, as long as one of the tables has a lot of data, the set of data that SQL needs to retrieve is extremely large. When a non-clustered index is added based on the filtered fields behind WHERE (at least two fields), the database automatically makes a copy of the data, which is then retrieved by SQL queries, greatly reducing the database response time. So is it better to have as many non-clustered indexes as possible? Generally speaking, a table can have only one clustered index, but can have multiple non-clustered indexes. The main purpose of database index is to improve the performance of SQL Server system, speed up the data query speed and reduce the response time of the system. This does not mean that more indexes (non-clustered indexes) are better. Proper index design can significantly improve performance, but not vice versa. When our technical support designed the index for the customer, the query time still did not improve as expected. This is an example of improper design of non-clustered indexes. Because those three fields (mentioned above) contain a field: field time, and this field is dynamic change, one of the filter criteria of customer query operation is time, so it did not take effect as scheduled, because the SQL query did not match the replicated data set. So when this field is reduced, the query time is back to between 6s-7s, and the hit ratio is significantly improved.
In addition, the increase of non-clustered indexes can increase the cost of hard disk (or memory), so it is important to design the indexes appropriately because there is no such thing as killing two birds with one stone.