Date Parameter bound as Timestamp destroying performance

Date Parameter bound as Timestamp destroying performance

Postby SamBach » Tue 16 Aug 2016 02:15

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.
SamBach
 
Posts: 2
Joined: Tue 16 Aug 2016 01:50

Re: Date Parameter bound as Timestamp destroying performance

Postby Shalex » Tue 16 Aug 2016 10:32

SamBach wrote: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.
Shalex
Devart Team
 
Posts: 7839
Joined: Thu 14 Aug 2008 12:44

Re: Date Parameter bound as Timestamp destroying performance

Postby SamBach » Tue 16 Aug 2016 22:22

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.
SamBach
 
Posts: 2
Joined: Tue 16 Aug 2016 01:50


Return to dotConnect for Oracle