3135644 - Query using wrong join order impacting performance - SAP ASE

• The server had been performing fine, but one query started running very slowly.
• The Abstract Plan in another environment shows the correct plan being used:
  ( insert ( nl_join ( t_scan ( table ( rt #tmp_txn ) ) ) ( i_scan idx1 ( table ( trn [mydb..lrg_tbl] ) ) ) ) )
  ( prop ( table ( rt #tmp_txn ) ) ( parallel 1 ) ( prefetch 16 ) ( lru ) ) ( prop ( table ( trn [mydb..lrg_tbl] ) )
  ( parallel 1 ) ( prefetch 16 ) ( lru ) )
• The Abstract Plan (AP) on the problem server shows:
  ( insert ( nl_join ( i_scan idx1 ( table ( lrg_tbl [mydb..lrg_tbl] ) ) ) ( i_scan idx1 ( table ( rt #tmp_txn ) ) ) ) )
  ( prop ( table ( lrg_tbl [mydb..lrg_tbl] ) ) ( parallel 1 ) ( prefetch 16 ) ( mru ) ) ( prop ( table ( rt #tmp_txn ) )
  ( parallel 1 ) ( prefetch 2 )( lru ) )
• The join order is being flipped and it using mru rather than lru
• The difference in performance is seconds vs hours.
• Why is this happening?

• SAP Adaptive Server Enterprise 15.7
• SAP Adaptive Server Enterprise 16.0

KBA , BC-SYB-ASE , Sybase ASE Database Platform (non Business Suite) , How To