Sixth--optimize performance based on execution Plan (2)--lookup table/Index Scan

Original: Sixth--optimize performance according to execution plan (2)--lookup table/Index Scan

Objective:

In most cases, table scans or index scans are not an efficient way to return small amounts of data, especially from a large table. These must be found and resolved to improve performance because the scan traverses each row, finds the qualifying data, and then returns the result. This processing is quite time consuming and resource-intensive. In the performance optimization process, the general focus is on:

1. CPU

2. Network

3. disk IO

Scanning operations increase the overhead of these three resources.

Preparatory work:

The following creates two tables to see the different effects of different physical association operations. The creation script has been given in the first article of this series and is no longer displayed here.

Steps:

1. Open the execution plan and run the following query:

SELECT  sh.*from    salesorddetaildemo as SD        INNER JOIN Salesordheaderdemo as sh on sh.salesorderid = Sd.salesorderidwhere   sh.orderdate = ' 2004-07-31 00:00:00.000 ' GO

From the execution plan you can see that table scans are used for both tables, where the execution plan suggests missing indexes. At this point you should consider whether it is necessary to create:

2. to avoid table scans, create a clustered index in table Salesordheaderdemo :

CREATE UNIQUE CLUSTERED INDEX idx_salesorderheaderdemo_salesorderid on Salesordheaderdemo (SalesOrderID) GO

3. run the query in step 1 again to see if the execution plan has removed the table scan:

4. you can see that the table that created the clustered index has become a clustered index scan, but the one that was not created is still a table scan, observing a clustered index scan, which simply replaced the table scan with a clustered index scan, so there was no significant performance improvement.

5. now continue to remove the table scan for the second table by creating a unique clustered index on the table:

CREATE UNIQUE CLUSTERED INDEX idx_salesdetail_salesorderid on Salesorddetaildemo (salesorderid,salesorderdetailid) GO

6. execute the query again.

7. below you can see that the table scan has been completely removed:

Analysis:

Prior to further discussion, the preferred clarification was that the scan was not always bad, and the lookup was not always good, but in most cases, the lookup would have a better performance when it returned a small amount of data, especially in large tables. Similarly, there is not always a way to remove a scan operation in each query. If the query performance problem is because of scanning, then the removal of the scan operation is better, otherwise, see if there is any change in the way to improve performance.

In the first step, because both tables are not indexed, the optimizer can only select the scan to find the data.

In the third step, a clustered index has been created on the Salesordheaderdemo table, the table scan becomes a clustered index scan, and the clustered index lookup is the result we want, but because we don't have a predicate on the first table, So you can only scan the entire clustered index instead of scanning the entire table.

In the sixth step, the second table also creates a clustered index, and there is a predicate on the table, so a clustered index lookup instead of a clustered index scan occurs.

Sixth--optimize performance based on execution Plan (2)--lookup table/Index Scan