I’m using the latest version on Devart 9.1.82.0
I’ve noticed that when binding date values in LINQ, Devart is binding them as TIMESTAMP causing indexes to be ignored by Oracle. This is making the product impossible to use as we have lots of big tables indexed by date columns.
C# code
Code: Select all
public IEnumerable<Price> GetPriceBetween(DateTime startDate, DateTime endDate)
{
return _Execute(context =>
{
var query = from p in context.PRICEs
where p.SETTLEMENTDATE >= startDate && p.SETTLEMENTDATE <= endDate
select p;
return Mapping.PriceMapper.MapSequence(query);
});
}
Verified by querying:
Code: Select all
select * from v$sql_bind_capture where sql_id = XXXX;
On this latest version the datatype_string field is TIMESTAMP
Oracle doesn’t like this:
Code: Select all
select * from table(dbms_xplan.display_cursor('82udmq74dt1xm',1,'ADVANCED LAST'));
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
OPT_PARAM('_optim_peek_user_binds' 'false')
OPT_PARAM('optimizer_index_cost_adj' 20)
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "Extent1"@"SEL$1")
END_OUTLINE_DATA
*/
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1142K(100)| |
|* 1 | TABLE ACCESS FULL| DISPATCHLOAD | 17M| 2323M| 1142K (2)| 04:26:39 |
…
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(INTERNAL_FUNCTION("Extent1"."SETTLEMENTDATE")>=:P__LINQ__0)
That internal function prevents the index being used which leads me to think it is a conversion issue. This is having disastrous results on all our queries with an indexed date.