ArticleDirectory
- 1. Example of replacing inner join with exists:
When using exists, if you can use it correctly, it may increase the query speed:
1. Replace inner join with exists
2. Replace in with exists
1. Example of replacing inner join with exists:
When writing SQL statements, you may encounter the following statements:
When two tables are connected, the data of one table is obtained. Generally, the join query is used ):
Select A. ID, A. workflowid, A. Operator, A. stepid
From DBO. [ [Zping.com ] ]
Inner Join Workflowbase B On A. workflowid = B. ID
And Operator = ' 4028814111ad9dc10111afc134f10041 '
Query results:
( 1327 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 ' Workflowbase ' . Scan count 1 , Logical read 293 Physical reads 0 Times, pre-read 0 Times, lob logic reads 0 Physical lob reads 0 Times, lob pre-read 0 Times.
Table ' [Zping.com] ' . Scan count 1 , Logical read 1339 Physical reads 0 Times, pre-read 0 Times, lob logic reads 0 Physical lob reads 0 Times, lob pre-read 0 Times.
Another method is to use exists to retrieve data.
Select A. ID, A. workflowid, A. Operator, A. stepid
From DBO. [ [Zping.com ] ] Where Exists
( Select ' X ' From Workflowbase B Where A. workflowid = B. ID)
And Operator = ' 4028814111ad9dc10111afc134f10041 '
Execution result:
( 1327 Rows affected)
Table ' [Zping.com] ' . Scan count 1 , Logical read 1339 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 291 Physical reads 0 Times, pre-read 0 Times, lob logic reads 0 Physical lob reads 0 Times, lob pre-read 0 Times.
Here, I/O times of two numbers,Exists has two less Io than inner join., Compare the execution plan cost is different, look at the two differences:
In this case, we found that the efficiency of using exists is slightly higher than that of inner join.
2. Replace in with exists
Requirement: Write the row where the ID of the workflowbase table is not in DBO. [[zping.com] of the table:
General Syntax:
Select * From Workflowbase
Where ID Not In (
Select A. workflowid
From DBO. [ [Zping.com ] ])
Execution result:
( 1 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 ' [Zping.com] ' . Scan count 5 , Logical read 56952 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 3 , Logical read 1589 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.
Use existsl to write:
Select * From Workflowbase B
Where Not Exists (
Select ' X '
From DBO. [ [Zping.com ] ] Where A. workflowid = B. ID)
View execution results
( 1 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 ' [Zping.com] ' . Scan count 3 , Logical read 18984 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 3 , Logical read 1589 Physical reads 0 Times, pre-read 0 Times, lob logic reads 0 Physical lob reads 0 Times, lob pre-read 0 Times.
Two Io gaps: 56952 + 1589 = 58541 (in)
18984 + 1589 = 20573 times (using exists)
The use of exists is 2.8 times that of in, and the query performance is greatly improved.
Exists makes the query faster, because the RDBMS core module will return results immediately after the subquery conditions are met.
