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

Preface:

In most cases, a table scan or index scan is not an efficient way to return a small amount of data, especially from a large table. These must be found and resolved to improve performance because the scan traverses each row, finds the eligible data, and returns the results. This kind of processing is quite time-consuming and resource-consuming. In the performance optimization process, generally focused on:

1, CPU

2, Network

3. Disk IO

The scan operation increases the cost 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 in this series and is no longer shown 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.salesorderid
WHERE   sh.orderdate = ' 2004-07-31 00:00:00.000 '
go

From the screenshot of the execution plan, you can see that both tables use a table scan, where the execution plan suggests missing indexes. You should consider at this point 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, the above figure can see that the table created the clustered index has become a clustered index scan, but did not create a table scan, the observation of the clustered index scan, which simply replaced the table scan with a clustered index scan, so there is no significant performance improvement.

5. Now continue the table scan of the second table to remove, by creating a unique clustered index on this table:

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

6, execute the query again.

7, the following screenshot can see the table scan has been completely removed:

Analysis:

Before delving into it, the first priority to clarify is that the scan is not always bad, and the lookup is not always good, but in most cases, especially when a small amount of data is returned in a large table, the lookup will perform better. Similarly, there is not always a way to remove a scan operation in each query. If the query's performance problem is due to a scan, then it would be better to remove the scan, otherwise, see if there is any way to improve performance.

In the first step, because neither table has an index, the optimizer can only select scans to find 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 since we don't have any predicates on the first table, we can only scan the entire clustered index instead of scanning the entire table.

In step sixth, the clustered index is also created in the second table, and there is a predicate on the table, so a clustered index lookup, rather than a clustered index scan, appears.