SQL statement Optimization-or statement optimization case

I came to Wenzhou from Shanghai and saw the SQL statements and data changes monitored a few days ago. I found that there was a large number of Io statements, reaching 1.5 million I/O, the data in the two tables is less than 0.2 million. Why are there so many Io times? The following is the execution statement:

Select WS. nodeid, wi. laststepid, wi. curstepid From Workflowinfo WI,
Workflowstep WS Where WS. workflowid = ' 402881db1b441e6f011c0cff320e4766 '   And (WI. laststepid = WS. ID Or (WI. curstepid = WS. ID And Isinclued = 1   And Issubmited = 1 )) The IO statistics are as follows: ( 22 Rows affected)
Table ' Workflowstep ' . Scan count 1 , Logical read 23 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 4 , Logical read 1490572 Physical reads 0 Times, pre-read 0 Times, lob logic reads 0 Physical lob reads 0 Times, lob pre-read 0 Times.
Table ' Workflowinfo ' . Scan count 4 , Logical read 12208 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. The execution plan is as follows:

We found that nested loops are mainly used.AlgorithmThe largest overhead. I personally felt that it was a performance problem caused by "or" and was later rewritten according to the business logic. As follows:

Statement modification: Select WS. nodeid, wi. laststepid, wi. curstepid From Workflowinfo WI, workflowstep WS
Where WS. workflowid = ' 402881db1b441e6f011c0cff320e4766 '   And (WI. laststepid = WS. ID)
Union   All  
Select WS. nodeid, wi. laststepid, wi. curstepid From Workflowinfo WI, workflowstep WS Where WS. workflowid = ' 402881db1b441e6f011c0cff320e4766 '   And (WI. curstepid = WS. ID And Isinclued = 1   And Issubmited = 1 )

 

The number of Io queries is as follows: ( 22 Rows affected)
Table ' Workflowinfo ' . Scan count 36 , Logical read 142 Physical reads 0 Times, pre-read 0 Times, lob logic reads 0 Physical lob reads 0 Times, lob pre-read 0 Times.
Table ' Workflowstep ' . Scan count 2 , 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.

The execution plan is as follows:

It is found that the cost is not the overhead of nested loops, and the number of Io operations is greatly reduced.

Summary:

Here, rewrite"Or"Statement into"Union"Statement, the performance is greatly improved. If the or statement is used, the database optimizer cannot be optimized. Here, the nested loop algorithm is used, but the usage is different and different results are also obtained.

For similar statements, you can rewrite them"Union"Or"Union all"Statement.