SQL optimization -- replace subquery with join query
Test example:
Subquery:
Select A. * ,
( Select Workflowname From Workflowbase Where ID = Workflowid) workflowname
From [ [Zping.com ] ]
Where A. Operator = ' 402882ed1112669201%a8%92f33 '
Execution result:
( 360 Rows affected)
Table ' Worktable ' . Scan count 360 , Logical read 142334 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 1589 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 366 Physical reads 0 Times, pre-read 0 Times, lob logic reads 0 Physical lob reads 0 Times, lob pre-read 0 Times.
Join query:
Select A. * , B. workflowname
From [ [Zping.com ] ] Inner Join Workflowbase B On A. workflowid = B. ID
Where Operator = ' 402882ed1112669201%a8%92f33 '
Execution result:
( 360 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 1589 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 366 Physical reads 0 Times, pre-read 0 Times, lob logic reads 0 Physical lob reads 0 Times, lob pre-read 0 Times.
Here: the number of subqueries I/O:142334 +1589+366 = 144289
Associated query Io count:1589 +366 = 1922
The associated query is 75 times that of the subquery.
Summary:
Subqueries and associated queries are used. Generally, subqueries are not used if associated queries are used,
