Oracle Cast to TIMESTAMP Performance Problem

GrMikeD · 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 · 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 · 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 · Post by **GrMikeD** » Wed 25 May 2011 11:56

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

AndreyR · 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.