CREATE INDEX x1_HHH ON #HHH(aid, bid, did, ls_indicator)
    CREATE INDEX x1_AAA ON #AAA(aid, bid, did, ls_flag)
    CREATE INDEX x1_CCC ON #CCC(aid, bid, did, ls_flag)

    SELECT
                aid                  = a.aid,
                bid                  = a.bid,
                did                  = a.did,
                ls_indicator         = a.ls_indicator,
		quantity             = a.quantity,
                pdate                = a.pdate,
		amort                = ( CASE @basis 
						WHEN "S" 	THEN  0  
						WHEN "T"	THEN  0 
						ELSE 		ISNULL(b.premiuminc  + b.discountinc , 0 ) 
					   END ),
		cost                  = ( CASE @type 
						WHEN 'EFF' 	THEN ISNULL(c.amtbase, 0 )
						ELSE 		ISNULL(c.amtbase, 0 )
					   END ),

    FROM
             #HHH         a,
      	     #AAA         b,
             #CCC         c
    WHERE         a.aid                 *= b.aid
      AND         a.bid	       	        *= b.bid
      AND         a.did		        *= b.did
      AND         a.ls_indicator        *= b.ls_flag
      AND         a.aid			*= c.aid
      AND         a.bid		        *= c.bid
      AND         a.did			*= c.did
      AND         a.ls_indicator        *= c.ls_flag

QUERY PLAN FOR STATEMENT  8  (at line  12 ). 

    STEP  1  
        The type of query is SELECT. 


        FROM TABLE 
            #HHH 
            a 
        Nested iteration. 
        Table Scan. 
        Forward scan. 
        Positioning at start of table. 
        Using I/O Size  16  Kbytes for data pages. 
        With LRU Buffer Replacement Strategy for data pages. 


        FROM TABLE 
            #AAA 
            b 
        Nested iteration. 
        Index : x1_AAA 
        Forward scan. 
        Positioning by key. 
        Keys are: 
            aid  ASC 
            bid  ASC 
            did  ASC 
            ls_flag   ASC 
        Using I/O Size  2  Kbytes for index leaf pages. 
        With LRU Buffer Replacement Strategy for index leaf pages. 
        Using I/O Size  2  Kbytes for data pages. 
        With LRU Buffer Replacement Strategy for data pages. 


        FROM TABLE 
            #CCC 
            c 
        Nested iteration. 
        Index : x1_CCC 
        Forward scan. 
        Positioning by key. 
        Keys are: 
            aid  ASC 
            bid  ASC 
            did  ASC 
            ls_flag  ASC 
        Using I/O Size  2  Kbytes for index leaf pages. 
        With LRU Buffer Replacement Strategy for index leaf pages. 
        Using I/O Size  2  Kbytes for data pages. 
        With LRU Buffer Replacement Strategy for data pages. 


Table: #HHH00005830004985700 scan count  1 , logical reads: (regular= 2004  apf= 0  total= 2004 ), physical reads: (regular= 306  apf= 0  total= 306 ), apf IOs used= 0  
Table: #AAA___00005830004985700 scan count  84148 , logical reads: (regular= 290609  apf= 0  total= 290609 ), physical reads: (regular= 1852  apf= 0  total= 1852 ), apf IOs used= 0  
Table: #CCC___00005830004985700 scan count  84148 , logical reads: (regular= 338962  apf= 0  total= 338962 ), physical reads: (regular= 3757  apf= 0  total= 3757 ), apf IOs used= 0  
Total writes for this command:  0  
( 84148  rows affected)

select @@version
Adaptive Server Enterprise/ 12 . 5 . 3  ...

forceplan on

QUERY PLAN FOR STATEMENT  111  (at line  670 ). 

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


        FROM TABLE 
            #HHH 
            a 
        Nested iteration. 
        Table Scan. 
        Forward scan. 
        Positioning at start of table. 
        Using I/O Size  16  Kbytes for data pages. 
        With LRU Buffer Replacement Strategy for data pages. 


        FROM TABLE 
            #AAA
            b 
        Nested iteration. 
        Table Scan. 
        Forward scan. 
        Positioning at start of table. 
        Using I/O Size  16  Kbytes for data pages. 
        With LRU Buffer Replacement Strategy for data pages. 


        FROM TABLE 
            #CCC
            c 
        Nested iteration. 
        Table Scan. 
        Forward scan. 
        Positioning at start of table. 
        Using I/O Size  16  Kbytes for data pages. 
        With LRU Buffer Replacement Strategy for data pages. 


        FROM TABLE 
            #DDD
            d 
        Nested iteration. 
        Table Scan. 
        Forward scan. 
        Positioning at start of table. 
        Using I/O Size  16  Kbytes for data pages. 
        With LRU Buffer Replacement Strategy for data pages. 


        FROM TABLE 
            #QQQ
            e 
        Nested iteration. 
        Table Scan. 
        Forward scan. 
        Positioning at start of table. 
        Using I/O Size  16  Kbytes for data pages. 
        With LRU Buffer Replacement Strategy for data pages. 
        TO TABLE 
            Worktable1.