select distinct TBD_ID
from TAKING_BLOOD_DOSE, ILLNESS_HISTORY, TAKING_BLOOD
where IH_DNRT_ID<> 3 
  AND TBD_ID not in ( select DM_TBD_ID from DOSE_MOVEMENT )
  AND TBD_IS_PROCESSED= 0 
  AND TBD_IS_GIVEN= 0 
  AND TB_IH_ID=IH_ID
  AND TBD_BD_ID is null
  AND TBD_TB_ID=TB_ID
order by TB_NUMBER, TB_DATE

 The sort for Worktable1 is done in Serial

 QUERY PLAN FOR STATEMENT  1  (at line  1 ).


 STEP  1 
 The type of query is INSERT.
 The update mode is direct.
 Worktable1 created, in allpages locking mode, for ORDER BY.

 FROM TABLE
 TAKING_BLOOD_DOSE
 Nested iteration.
 Table Scan.
 Forward scan.
 Positioning at start of table.

 Run subquery  1  (at nesting level  1 ).
 Using I/O Size  2  Kbytes for data pages.
 With LRU Buffer Replacement Strategy for data pages.

 FROM TABLE
 TAKING_BLOOD
 Nested iteration.
 Using Clustered Index.
 Index : PK_TAKING_BLOOD
 Forward scan.
 Positioning by key.
 Keys are:
 TB_ID ASC
 Using I/O Size  2  Kbytes for data pages.
 With LRU Buffer Replacement Strategy for data pages.

 FROM TABLE
 ILLNESS_HISTORY
 EXISTS TABLE : nested iteration.
 Using Clustered Index.
 Index : PK_ILLNESS_HISTORY
 Forward scan.
 Positioning by key.
 Keys are:
 IH_ID ASC
 Using I/O Size  2  Kbytes for data pages.
 With LRU Buffer Replacement Strategy for data pages.
 TO TABLE
 Worktable1.

 STEP  2 
 The type of query is SELECT.
 This step involves sorting.

 FROM TABLE
 Worktable1.
 Using GETSORTED
 Table Scan.
 Forward scan.
 Positioning at start of table.
 Using I/O Size  2  Kbytes for data pages.
 With MRU Buffer Replacement Strategy for data pages.
 STEP  1 

 NESTING LEVEL  1  SUBQUERIES FOR STATEMENT  1 .

 QUERY PLAN FOR SUBQUERY  1  (at nesting level  1  and at line  1 ).

 Correlated Subquery.
 Subquery under an IN predicate.


 STEP  1 
 The type of query is SELECT.
 Evaluate Ungrouped ANY AGGREGATE.

 FROM TABLE
 DOSE_MOVEMENT
 EXISTS TABLE : nested iteration.
 Index : FK_DM_TBD
 Forward scan.
 Positioning at index start.
 Index contains all needed columns. Base table will not be read.
 Using I/O Size  2  Kbytes for index leaf pages.
 With LRU Buffer Replacement Strategy for index leaf pages.

 END OF QUERY PLAN FOR SUBQUERY  1 .


 Parse and Compile Time  0 .
 SQL Server cpu time:  0  ms.
 Table: TAKING_BLOOD_DOSE scan count  1 , logical reads: (regular= 3851  apf= 0  total= 3851 ), physical reads: (regular= 722  apf= 3129  total= 3851 ), apf IOs used= 3129 
 Table: ILLNESS_HISTORY scan count  50000 , logical reads: (regular= 150000  apf= 0  total= 150000 ), physical reads: (regular= 13251  apf= 0  total= 13251 ), apf IOs used= 0 
 Table: TAKING_BLOOD scan count  50000 , logical reads: (regular= 152991  apf= 0  total= 152991 ), physical reads: (regular= 26597  apf= 1646  total= 28243 ), apf IOs used= 653 
 Table: DOSE_MOVEMENT scan count  50000 , logical reads: (regular= 50000  apf= 0  total= 50000 ), physical reads: (regular= 0  apf= 0  total= 0 ), apf IOs used= 0 
 Table: Worktable1 scan count  0 , logical reads: (regular= 53389  apf= 0  total= 53389 ), physical reads: (regular= 1116  apf= 0  total= 1116 ), apf IOs used= 0 
 Total writes for this command:  1729 
 Statement:  1  Subquery:  1  cache size:  0  hits:  0  misses:  0 

 Execution Time  141 .
 SQL Server cpu time:  14100  ms. SQL Server elapsed time:  19203  ms.

select count(*) from TAKING_BLOOD_DOSE

 100000 

select count(*) from ILLNESS_HISTORY

 50000 
select count(*) from TAKING_BLOOD
 100000 
select count(*) from DOSE_MOVEMENT
 5 

...
from TAKING_BLOOD_DOSE ...
where ... TBD_ID not in ( select DM_TBD_ID from DOSE_MOVEMENT )