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

query performance, join order, tempdb cache, data cache configuration, MRU vs LRU, abstract plan, cache partitioning, HK Ignore option, named caches, strict LRU replacement, troubleshooting SAP ASE, database optimization, temporary tables, cache settings, performance tuning. , KBA , BC-SYB-ASE , Sybase ASE Database Platform (non Business Suite) , How To