Peformance Issue: Entity Provider uses TIMESTAMP not DATE

Peformance Issue: Entity Provider uses TIMESTAMP not DATE

Postby 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
laurent.christen
 
Posts: 3
Joined: Thu 25 Nov 2010 13:52
Location: Zurich, Switzerland

Postby 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.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby 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?
motuzko
 
Posts: 47
Joined: Tue 08 Sep 2009 18:02

Postby 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.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby 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.
laurent.christen
 
Posts: 3
Joined: Thu 25 Nov 2010 13:52
Location: Zurich, Switzerland

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

Postby 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
laurent.christen
 
Posts: 3
Joined: Thu 25 Nov 2010 13:52
Location: Zurich, Switzerland

Postby 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
Devart Team
 
Posts: 7616
Joined: Thu 14 Aug 2008 12:44

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


Return to dotConnect for Oracle