2005401 - Runtime for subquery in UPDATE is 10 x longer after migrating from v.12.5 to v.15.7 - SAP ASE

• The subquery in an UPDATE statement is running 10 times longer after migrating from ASE 12.5 to ASE 15.7.

update mytable
set SupervisorID = '', 
     SupervisorName = '', 
     SupervisorEmail = ''
from mytable  a
where not exists
(select * from table1 b, table2 c
where a.SupervisorID = b.global_usr_id
and b.emp_status_cd <> 'T'
and b.foreign_emp_id = c.foreign_emp_id
and b.entity_id = c.entity_id
and b.tax_entity_id = c.tax_entity_id)

• The 15.7 showplan:

QUERY PLAN FOR STATEMENT 1 (at line 1).
Optimized using Serial Mode

    STEP 1
        The type of query is UPDATE.
 
    7 operator(s) under root
 
       |ROOT:EMIT Operator (VA = 7)
       |
       |   |UPDATE Operator (VA = 6)
       |   |  The update mode is deferred_varcol.
       |   |
       |   |   |SQFILTER Operator (VA = 5) has 2 children.
       |   |   |
       |   |   |   |SCAN Operator (VA = 0)
       |   |   |   |  FROM TABLE
       |   |   |   |  mytable 
       |   |   |   |  a
       |   |   |   |  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.
       |   |   |
       |   |   |  Run subquery 1 (at nesting level 1).
       |   |   |
       |   |   |  QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 7).
       |   |   |
       |   |   |   Correlated Subquery.
       |   |   |   Subquery under an EXISTS predicate.
       |   |   |
       |   |   |   |SCALAR AGGREGATE Operator (VA = 4)
       |   |   |   |  Evaluate Ungrouped ANY AGGREGATE.
       |   |   |   |  Scanning only up to the first qualifying row.
       |   |   |   |
       |   |   |   |   |NESTED LOOP JOIN Operator (VA = 3) (Join Type: Left Semi Join)
       |   |   |   |   |
       |   |   |   |   |   |SCAN Operator (VA = 1)
       |   |   |   |   |   |  FROM TABLE
       |   |   |   |   |   |  table1 
       |   |   |   |   |   |  b
       |   |   |   |   |   |  Table Scan.
       |   |   |   |   |   |  Forward Scan.
       |   |   |   |   |   |  Positioning at start of table.
       |   |   |   |   |   |  Using I/O Size 16 Kbytes for data pages.
       |   |   |   |   |   |  With MRU Buffer Replacement Strategy for data pages.
       |   |   |   |   |
       |   |   |   |   |   |SCAN Operator (VA = 2)
       |   |   |   |   |   |  FROM TABLE
       |   |   |   |   |   |  table2 
       |   |   |   |   |   |  c
       |   |   |   |   |   |  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.
       |   |   |
       |   |   |  END OF QUERY PLAN FOR SUBQUERY 1.
       |   |
       |   |  TO TABLE
       |   |  mytable 
       |   |  Using I/O Size 2 Kbytes for data pages.

•  The 12.5 showplan:

QUERY PLAN FOR STATEMENT 1 (at line 1).
 
    STEP 1
        The type of query is INSERT.
        The update mode is direct. 
        Worktable1 created for REFORMATTING.
 
        FROM TABLE 
            table1 
            b 
        Nested iteration.
        Table Scan.
        Forward scan.
        Positioning at start of table.
        Using I/O Size 16 Kbytes for data pages.
        With MRU Buffer Replacement Strategy for data pages.
        TO TABLE
            Worktable1.
 
    STEP 2
        The type of query is INSERT.
        The update mode is direct. 
        Worktable2 created for REFORMATTING.
 
        FROM TABLE 
            table2 
            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.
        TO TABLE
            Worktable2.
 
    STEP 3
        The type of query is UPDATE.
        The update mode is deferred_varcol.
 
        FROM TABLE 
            mytable 
            a
        Nested iteration.
        Table Scan.
        Forward scan.
        Positioning at start of table.

        Run subquery 1 (at nesting level 1).
        Using I/O Size 16 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.
        TO TABLE 
            mytable 
        Using I/O Size 2 Kbytes for data pages.
    STEP 1
 
NESTING LEVEL 1 SUBQUERIES FOR STATEMENT 1.
 
QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 7).
 
    Correlated Subquery. 
    Subquery under an EXISTS predicate.
 
    STEP 1
        The type of query is SELECT.
        Evaluate Ungrouped ANY AGGREGATE.
 
        FROM TABLE 
            Worktable1.
        EXISTS TABLE : nested iteration.
        Using Clustered Index.
        Forward scan.
        Positioning by key.
        Using I/O Size 16 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.
 
        FROM TABLE 
            Worktable2.
        EXISTS TABLE : nested iteration.
        Using Clustered Index.
        Forward scan.
        Positioning by key.
        Using I/O Size 16 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.
 
END OF QUERY PLAN FOR SUBQUERY 1.

SAP Adaptive Server Enterprise (ASE) 15.7

index, missed, missing, compatibility, mode , KBA , BC-SYB-ASE , Sybase ASE Database Platform (non Business Suite) , Problem