Date Parameter bound as Timestamp destroying performance

[SamBach]

Hello

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 older versions (7.4) the datatype_string field is DATE
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.

[Shalex]

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.

We cannot reproduce the issue at the moment.

You are working with Entity Framework, aren't you? Please make sure that the Type attribute of your SETTLEMENTDATE property is set to DATE in the SSDL part of the model: open your *.edml, navigate to Entity Developer > Model Explorer > *.Store > PRICE > SETTLEMENTDATE and press F4 (Properties).

In case of LinqConnect, make sure that the Server Data Type attribute of your SETTLEMENTDATE property is set to DATE.

JIC: you can check the types of parameters used also via dbMonitor.

If this doesn't help, send us a small test project with the corresponding DDL/DML script for reproducing.

[SamBach]

Just realized this doesn't happen when connecting from my local machine. The only difference I can think of is that I haven't installed the latest version of dotconnect on the runtime server. I have other applications that are still using 7.4 on the production server and wasn't sure if I had to install dotconnect on the server or whether it was wholly contained in my project.