SAP Knowledge Base Article - Preview

2418461 - Using table scan vs index scan when comparing to a variable instead of a literal impacts performance - SAP ASE


  • Performance differences between DEV & PROD SUPP systems and QA & PROD systems. 
  • The odd thing is that very similar SQL is performing differently within the same environment, in some environment, but not in others.
  • It appears that comparing to a variable instead of a literal is enough to make a query do a full table scan in DEV vs using an index,
  • The table has the same indexes in DEV and PROD. This is not an issue.
  • Likewise, in PROD SUPP, if you compare to a variable, and do a SELECT INTO, that will cause a full table scan.
  • It doesn’t look like caching or data size, as minor changes to the SQL can cause the query to use an index or do a full table scan.
  • Rebuilding Indexes, updating statistics, running REORG REBUILD in DEV does not help to avoid table scan while using variable in DEV.
  • This makes it tough to develop changes in DEV and to support production issues.
  • For example, the following query uses index and runs fast:
       SELECT cust_id
       FROM cust..cust_table
       where lock_dte_tme between '11/01/2010' and '11/30/2010'

    while a similar query uses a table scan and runs slow:
       DECLARE @last_run datetime, @first_run datetime
       SELECT  @first_run = '11/01/2010'
       SELECT  @last_run = '11/30/2010'
       SELECT cust_id
       FROM cust..cust_table
       where lock_dte_tme between @first_run and @last_run  



Sybase Adaptive Server Enterprise 15.5


statement, literal, autoparam, merge, join, configuration , KBA , BC-SYB-ASE , Sybase ASE Database Platform (non Business Suite) , Problem

About this page

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

Search for additional results

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