SQL statement Optimization-in statement optimization case

ArticleDirectory

• Summary:

After the customer's system was upgraded today, we checked through the dmvs performance analysis. After the upgrade, we found that the execution of a statement takes several seconds. The Calling statement is as follows:

Select   Distinct Field003 From Ufi2j0n11179717502375 Where  
Field003 Not   In ( '' , ' 40288135120d660501120de2f8870140 ' , ' 40288135120d660501120de4b9ee014b ' ,
' 40288135120d660501120de9c3ba016c ' , ' 40288135120d660501120df0460c01b2 ' , ' 40288135120d660501120df1dc2d01d3 ' ) And Requestid In ( Select Requestid From Ufi8s6u81179717475734 Where Field001 In ( Select Requestid From Uft3a6h61176948132312 Where Field066 Is   Not Null   And Field197 Between   Convert ( Datetime , ' 2008-08-16 ' ) And   Convert ( Datetime , ' 2008-09-15 ' )))

Later, let's look at the data in these tables.

-- Table DBO. uft3a6h61176948132312:988 rows
-- Table: DBO. ufi2j0n11179717502375:713 rows
-- Table: DBO. ufi8s6u81179717475734:273 rows

It was found that none of the three tables had more than rows of data. It is of little significance to create an index. Why is it so slow? Let's look at the execution plan:

Analysis: TableDBO. uft3a6h61176948132312The maximum access overhead, but less than one thousand rows of data in the table. Run the following command to check the result:

( 5 Rows affected)
Table ' Uft3a6h61176948132312 ' . Scan count 1 , Logical read 27161 Physical reads 0 Times, pre-read 0 Times, lob logic reads 0 Physical lob reads 0 Times, lob pre-read 0 Times.
Table ' Worktable ' . Scan count 0 , Logical read 0 Physical reads 0 Times, pre-read 0 Times, lob logic reads 0 Physical lob reads 0 Times, lob pre-read 0 Times.
Table ' Ufi8s6u81179717475734 ' . Scan count 1 , Logical read 37 Physical reads 0 Times, pre-read 0 Times, lob logic reads 0 Physical lob reads 0 Times, lob pre-read 0 Times.
Table ' Ufi2j0n11179717502375 ' . Scan count 1 , Logical read 46 Physical reads 0 Times, pre-read 0 Times, lob logic reads 0 Physical lob reads 0 Times, lob pre-read 0 Times.

Table found hereUft3a6h61176948132312 has nearly 30 thousand I/O accesses.At first, I thought it was in, and the result of changing in to exists is the same. In this case, I want to use inner join to re-write the SQL statement. The statement is as follows:

Select   Distinct A. field003 From Ufi2j0n11179717502375
Inner   Join Ufi8s6u81179717475734 B On A. requestid = B. requestid
Inner   Join Uft3a6h61176948132312 C On B. field001 = C. requestid
Where A. field003 Not   In ( '' , ' 40288135120d660501120de2f8870140 ' , ' 40288135120d660501120de4b9ee014b ' , ' 40288135120d660501120de9c3ba016c ' , ' 40288135120d660501120df0460c01b2 ' , ' 40288135120d660501120df1dc2d01d3 ' ) And C. field066 Is   Not Null   And C. field197 Between  
Convert ( Datetime , ' 2008-08-16 ' ) And   Convert ( Datetime , ' 2008-09-15 ' )

View the execution plan:

Analysis: The execution plan changes, and the table on the outermost layer changesDBO. ufi2j0n11179717502375. The execution result is as follows:

( 5 Rows affected)
Table ' Worktable ' . Scan count 0 , Logical read 0 Physical reads 0 Times, pre-read 0 Times, lob logic reads 0 Physical lob reads 0 Times, lob pre-read 0 Times.
Table ' Ufi2j0n11179717502375 ' . Scan count 1 , Logical read 46 Physical reads 0 Times, pre-read 0 Times, lob logic reads 0 Physical lob reads 0 Times, lob pre-read 0 Times.
Table ' Ufi8s6u81179717475734 ' . Scan count 1 , Logical read 37 Physical reads 0 Times, pre-read 0 Times, lob logic reads 0 Physical lob reads 0 Times, lob pre-read 0 Times.
Table ' Uft3a6h61176948132312 ' . Scan count 1 , Logical read 421 Physical reads 0 Times, pre-read 0 Times, lob logic reads 0 Physical lob reads 0 Times, lob pre-read 0 Times.

At this time, I/O times are much less than before.

Summary:

According to the analysis of the two execution plans, the SQL Server 2005 OptimizerIn statementThe link is not selected correctly.Algorithm, The incorrect use of"Nested loop algorithm".

Io times based on nested loop algorithm: 421 * (number of associated matched rows in the other two tables) ≈27163(Access Table"Uft3a6h61176948132312"I/O times). Because the number of returned rows is large and no index is created, the best algorithm is to useHash join algorithm"