Oracle Cast to TIMESTAMP Performance Problem

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
GrMikeD
Posts: 22
Joined: Tue 10 Nov 2009 20:24

Oracle Cast to TIMESTAMP Performance Problem

Post by GrMikeD » Mon 23 May 2011 10:15

Hello,

I am currently evaluating the Devart Oracle EF driver and it performs very well.

Yet, yesterday I noticed a non-negligible performance problem:

When I join two tables on two DATE fields that are both non-nullable, the query returns instantly: A.date1 == B.date2

When one of the two fields are nullable, it makes a casting to TIMESTAMP that increases a lot the query cost and it takes several seconds to return:
A.date1 == CAST(B.date2 AS TIMESTAMP(9)) // date1 is nullable

If I change from Oracle Sql Developer the query to the simple A.date1 == B.date2, it returns instantly! It even returns instantly if I cast to DATE instead of Timestamp: A.date1 == CAST(B.date2 AS DATE)

I suppose, for some reason, EF instructs Devart to cast the value because one of the two values is nullable.

How could I avoid this behavior and instruct devart to only use DATE for the casting or no casting at all?

Thanks in advance!

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Mon 23 May 2011 11:51

Thank you for the report, I have reproduced the issue.
I will let you know about the results of our investigation.

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 25 May 2011 11:24

Redundant casts for the DATE and TIMESTAMP columns will be excluded from the generated query starting from the next build of dotConnect for Oracle. We will post here when the corresponding build is available for download.

GrMikeD
Posts: 22
Joined: Tue 10 Nov 2009 20:24

Post by GrMikeD » Wed 25 May 2011 11:56

Thanks a lot for the prompt investigation and the positive response!

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Wed 01 Jun 2011 15:37

We have released the new 6.30.165 build of dotConnect for Oracle. This build can be dowloaded from here (the trial version) or from Registered Users' Area (for users with active subscription only).
For the detailed information about the improvements and fixes available in dotConnect for Oracle 6.30.165, please refer to this announcement.

Post Reply