- 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:
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'
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 pageThis 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.