Peformance Issue: Entity Provider uses TIMESTAMP not DATE

laurent.christen · Post by **laurent.christen** » Thu 25 Nov 2010 14:15

Hi,
I am having a performance issue when use the entity data provider. In a where statement with a date value the entity provider transforms the Linq statement into a Oracle timestamp using TO_TIMESTAMP. The query runs against a multimillion and partioned table where the date value in the primary key. The whole result is limited using Take/ROWNUM<=.

Code: Select all

SELECT blabla
WHERE "Extent1".EFFECTIVE_DATE = TO_TIMESTAMP('20100921', 'YYYYMMDD') AND ROWNUM <= (50)

The statement needs approx. 100x more time then the where clause using TO_DATE(). Oracle probably transforms for the comparison operation the DATE value of the table to TIMESTAMP instead the opposite.
I also saw other topics in the forum that TO_TIMESTAMP is needed because of the milliseconds. Why you don't use TO_TIMESTAMP when milliseconds are given from the DateTime value and if not use TO_DATE.
Thanks!

dotConnect V-5.70.190.0
Oracle 11g

AndreyR · Post by **AndreyR** » Thu 25 Nov 2010 16:39

Thank you for the suggestion.
We will investigate the possibility to change the behaviour in the requested way.
I'll notify you about the results of our investigation.

motuzko · Post by **motuzko** » Mon 14 Mar 2011 19:06

AndreyR wrote:I'll notify you about the results of our investigation.

So, what were the results of the investigation?

AndreyR · Post by **AndreyR** » Tue 15 Mar 2011 09:36

Sorry for the notification delay. The issue was fixed several builds ago, I recommend you to upgrade to the latest build of dotConnect for Oracle. It can be downloaded from Registered Users' Area.

laurent.christen · Post by **laurent.christen** » Mon 29 Aug 2011 13:59

Sorry, but the component still creates queries passing date parameters as Timestamps. Most of the application will not need the precision of timestamps. This is a really serious problem for us because of the peformance issues that this causes.
As promissed, please provide the solution as soon as possible.
l

We use the latest build 6.30.202.

Shalex · Post by **Shalex** » Wed 31 Aug 2011 10:01

laurent.christen wrote:the component still creates queries passing date parameters as Timestamps

The previous discussion in this thread was about DateTime-constants in LINQ queries which were converted in literals:

Code: Select all

   var query = context.Log.Where(o => o.Date = new DateTime(2010, 01, 01, 12, 00, 00));

We have optimized this aspect successfully.
As we understood, you are talking about behaviour of our provider with parameters:

Code: Select all

   DateTime value = new DateTime(2010, 01, 01, 12, 00, 00);
   var query = context.Log.Where(o => o.Date = value);

We will investigate the issue with date parameters and post here about the results as soon as possible.

laurent.christen · Post by **laurent.christen** » Wed 31 Aug 2011 12:30

You're right. It's only about variables used in the Linq statement. The values are passed to Oracle then as statement parameters and the parameter is of the type timestamp.
l

Shalex · Post by **Shalex** » Fri 02 Sep 2011 10:55

The generation of DATE/TIMESTAMP parameters in LINQ queries is improved. This is a specific optimization, and it is possible that DATE will not be determined in some cases. If you encounter such situation, please report us about this. We will post here when the next build of dotConnect for Oracle is available for download.

Shalex · Post by **Shalex** » Wed 07 Sep 2011 12:04

New version of dotConnect for Oracle 6.50 is released!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only): http://secure.devart.com/ .
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=21942 .