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

Symptom

  • 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  

Read more...

Product

Sybase Adaptive Server Enterprise 15.5

Keywords

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 ONE Support launchpad (Login required).

Search for additional results

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