Page 1 of 1
Peformance Issue: Entity Provider uses TIMESTAMP not DATE
Posted: Thu 25 Nov 2010 14:15
by laurent.christen
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
Posted: Thu 25 Nov 2010 16:39
by AndreyR
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.
Posted: Mon 14 Mar 2011 19:06
by motuzko
AndreyR wrote:I'll notify you about the results of our investigation.
So, what were the results of the investigation?
Posted: Tue 15 Mar 2011 09:36
by AndreyR
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.
Posted: Mon 29 Aug 2011 13:59
by laurent.christen
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.
Posted: Wed 31 Aug 2011 10:01
by Shalex
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.
Posted: Wed 31 Aug 2011 12:30
by laurent.christen
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
Posted: Fri 02 Sep 2011 10:55
by Shalex
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.
Posted: Wed 07 Sep 2011 12:04
by Shalex
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 .