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  

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