2508549 - Driver binds to BIGDATETIME causing table scan instead of index use if local variable is used as a search argument | SAP Knowledge Base Article

SAP Knowledge Base Article - Preview

2508549 - Driver binds to BIGDATETIME causing table scan instead of index use if local variable is used as a search argument

Symptom

A query that manipulates datetime is choosing table scan when it sent by ODBC or ADO. The column definition is datetime and the parameters are defined as DATETIME, but the ribo capture indicates that driver is sending them as BIGDATETIME (this is how driver works):

1) Query

select cola, colb, colc from table01 where close_date between @date_begin and @date_end

2) Showplan

QUERY PLAN FOR STATEMENT 4 (at line 5).
Optimized using Serial Mode
Optimized using simulated statistics.


STEP 1
The type of query is SELECT.

2 operator(s) under root

|ROOT:EMIT Operator (VA = 2)
|
| |RESTRICT Operator (VA = 1)(5)(0)(0)(0)(0)
| |
| | |SCAN Operator (VA = 0)
| | | FROM TABLE
| | | table01
| | | Table Scan.
| | | Forward Scan.
| | | Positioning at start of table.
| | | Using I/O Size 16 Kbytes for data pages.
| | | With MRU Buffer Replacement Strategy for data pages.


3) Ribo

$prm = $cmd.CreateParameter()
$prm.ParameterName = "@date_begin"
$prm.DbType = "DateTime"
$prm.Value = '2017-02-17'
$cmd.Parameters.Add($prm)

$prm = $cmd.CreateParameter()
$prm.ParameterName = "@date_end"
$prm.DbType = "DateTime"
$prm.Value = '2017-02-17'
$cmd.Parameters.Add($prm)


LANGUAGE Token (0x21); variable length.
Length [4]: 110
Status [1]: PARAMETERIZED (0x01)
Text Length [0]: [109]
Text [109]: "select cola, colb, colc from table01 where close_date between @date_begin and @date_end"
…..
…..
……
Param 1
Name Length [1]: 12
Name [12]: "@date_begin"
Status [1]: PARAM_NULLALLOWED (0x20)
User Type [4]: 0
Data Type [1]: BIGDATETIMEN
Length [1]: 8
Locale Length [1]: 0
Param 2
Name Length [1]: 9
Name [9]: "@date_end"
Status [1]: PARAM_NULLALLOWED (0x20)
User Type [4]: 0
Data Type [1]: BIGDATETIMEN
Length [1]: 8


Read more...

Environment

  • SAP Adaptive Server 15.7 SP138 and higher

Product

SAP Adaptive Server Enterprise 15.7

Keywords

table scan performance BIGDATETIMEN datetime smalldatetime  compatibility mode statement cache literal autoparam enabled disabled ribo , 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.