SAP Knowledge Base Article - Preview

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

Symptom

  • 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?


Read more...

Environment

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

Product

SAP Adaptive Server Enterprise 15.7 ; SAP Adaptive Server Enterprise 16.0

Keywords

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

About this page

This is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP ONE Support launchpad (Login required).

Search for additional results

Visit SAP Support Portal's SAP Notes and KBA Search.