ArticleDirectory
During this period of optimization, it is found that a statement has been executed for a long time and is very inefficient. The statement is as follows:
Select ID, field015, field016, field017, field001, field020, field010, field014, field011, field013, field004, field018,
Field005, field007, field003, Null , Requestid From Ufv3a7n71178865841875 tbalias Where Requestid In ( Select ID From Workflowbase WB Where WB. isdelete <> 1 And Isfinished = 0 ) And
Exists ( Select ' X ' From Permissiondetail P Where P. objid = Tbalias. requestid And P. objtable = ' Workflowbase ' And (P. userid = ' 40282c48177be3a001177fefe73843d8 ' ) Or (P. isalluser = 1 Or P. orgid = ' 40288a7d0f55fb5a010f109c2bf01205 ' )
And (P. minseclevel <= 10 And (P. maxseclevel Is Not Null ) And ( 10 <= P. maxseclevel )) Or (P. maxseclevel Is Null ))))))
Order By Field017 Desc , ID Desc
Execution Plan:
Execution result:
( 1578 Rows affected)
Table ' Workflowbase ' . Scan count 0 , Logical read 34932 Physical reads 0 Times, pre-read 0 Times, lob logic reads 0 Physical lob reads 0 Times, lob pre-read 0 Times.
Table ' Permissiondetail ' . Scan count 8145 , Logical read 48196 Physical reads 0 Times, pre-read 0 Times, lob logic reads 0 Physical lob reads 0 Times, lob pre-read 0 Times.
Table ' Ufv3a7n71178865841875 ' . Scan count 1 , Logical read 576 Physical reads 0 Times, pre-read 0 Times, lob logic reads 0 Physical lob reads 0 Times, lob pre-read 0 Times.
Convert the exists statement into an inner join statement. The statement is as follows:
Select Distinct Tbalias. ID, field015, field016, field017, field001, field020, field010, field014, field011, field013, field004, field018,
Field005, field007, field003, Null , Requestid From Ufv3a7n71178865841875 tbalias
Inner Join Workflowbase WB On WB. ID = Tbalias. requestid And WB. isdelete <> 1 And Isfinished = 0
Inner Join Permissiondetail P On P. objid = Tbalias. requestid And P. objtable = ' Workflowbase ' And
(P. userid = ' 40282c48177be3a001177fefe73843d8 ' ) Or (P. isalluser = 1 Or P. orgid = ' 40288a7d0f55fb5a010f109c2bf01205 ' )
And (P. minseclevel <= 10 And (P. maxseclevel Is Not Null ) And ( 10 <= P. maxseclevel )) Or (P. maxseclevel Is Null )))))
Order By Field017 Desc , ID Desc
Execution Plan:
Execution result:
( 1578 Rows affected)
Table ' Permissiondetail ' . Scan count 2988 , Logical read 17298 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 ' Ufv3a7n71178865841875 ' . Scan count 1 , Logical read 576 Physical reads 0 Times, pre-read 0 Times, lob logic reads 0 Physical lob reads 0 Times, lob pre-read 0 Times.
Table ' Workflowbase ' . Scan count 1 , Logical read 1425 Physical reads 0 Times, pre-read 0 Times, lob logic reads 0 Physical lob reads 0 Times, lob pre-read 0 Times.
By replacing exists with inner join, we find that the execution planHash join)ChangedNested loop), Io details are reduced.
Summary:
I have previously written an optimization article "SQL optimization -- replace in and join queries (inner join) with exists", prompting to replace inner join with exists, which is a prerequisite, to be tested, we use inner join to replace exists, which is also used in actual situations,There is no specific formula for the two..
Purpose:Is to allow the optimizer to use the correct execution plan under specific circumstances.
