.NET to Oracle default type mapping
-
- Posts: 13
- Joined: Fri 23 Apr 2010 21:16
.NET to Oracle default type mapping
Hi,
We have been using Devart for dotConnect 6.80 for quite some time now and we just recently discovered a issue.
We have around 200 tables in an ORACLE DB and almost all of them have atleast one DATETIME columns. When we write queries against this (using EF 4.1), like:
DateTime dt = DateTime.Today;
var tmp = (from c in TaskEntities
where c.LastUpdateDate >= dt
select c).ToList();
the SQL generated maps the variable dt to a BIND variable of type TIMESTAMP, which is in accordance with http://www.devart.com/dotconnect/oracle ... pping.html.
The issue is: Oracle tries to convert all the column values from DATE to TIMESTAMP, hence causing a spike in CPU usage and less throughput. Is there any way I can override the default mapping of System.DateTime in .Net to DATE in Oracle, instead of TIMESTAMP?
I know that I can convert "dt" to DATE explicitly but I have numerous such queries in the application, scattered all over the place. I am looking for a less intrusive way to achieve the same.
~Mayank
We have been using Devart for dotConnect 6.80 for quite some time now and we just recently discovered a issue.
We have around 200 tables in an ORACLE DB and almost all of them have atleast one DATETIME columns. When we write queries against this (using EF 4.1), like:
DateTime dt = DateTime.Today;
var tmp = (from c in TaskEntities
where c.LastUpdateDate >= dt
select c).ToList();
the SQL generated maps the variable dt to a BIND variable of type TIMESTAMP, which is in accordance with http://www.devart.com/dotconnect/oracle ... pping.html.
The issue is: Oracle tries to convert all the column values from DATE to TIMESTAMP, hence causing a spike in CPU usage and less throughput. Is there any way I can override the default mapping of System.DateTime in .Net to DATE in Oracle, instead of TIMESTAMP?
I know that I can convert "dt" to DATE explicitly but I have numerous such queries in the application, scattered all over the place. I am looking for a less intrusive way to achieve the same.
~Mayank
Re: .NET to Oracle default type mapping
Please refer to http://forums.devart.com/viewtopic.php?f=1&t=24890.
The documentation about the UseDateTimeAsDate option is available at http://www.devart.com/dotconnect/oracle ... tions.html.
The documentation about the UseDateTimeAsDate option is available at http://www.devart.com/dotconnect/oracle ... tions.html.
-
- Posts: 13
- Joined: Fri 23 Apr 2010 21:16
Re: .NET to Oracle default type mapping
Shalex,
Thanks for responding.
I dont think you got my question. Let me rephrase it.
We are not using "Code First" here. So the question of my DB being created from my conceptual model doesnt arise. We have a DB team which builds the tables for us and we base our conceptual model on it.
I did some more tests and found some interesting results.
I modified my query to:
I ran the query (LastUpdateDate and DoneDate both are of type DATE in the db) and captured the output using DBMonitor:
In the parameters section, I see 2 parameters being passed in, both of type TimeStamp.
I ran the same query after upgrading my system to dotConnect 8.30. (I did not do any changes with UseDateTimeAsDate property as you suggested) This time, I got one parameter as Date and other as Timestamp.
So it seems like you have fixed the issue partially, if I dont use OracleFunctions.Trunc, then the variable "dt" is mapped to type DATE in the ORACLE world, as it should be(in version 8.3) but if I do use
OracleFunctions.Trunc, then the variable "dt" is mapped to type TIMESTAMP in the ORACLE world.
So while the latest version fixes the issue, which helps me across 60% of my cases, I will appreciate if the OracleFunctions.Trunc issue if fixed too.
Thanks,
Mayank
Thanks for responding.
I dont think you got my question. Let me rephrase it.
We are not using "Code First" here. So the question of my DB being created from my conceptual model doesnt arise. We have a DB team which builds the tables for us and we base our conceptual model on it.
I did some more tests and found some interesting results.
I modified my query to:
Code: Select all
DateTime dt = DateTime.Today;
var tmp = (from c in TaskEntities
where c.LastUpdateDate >= dt
&& OracleFunctions.Trunc(c.DoneDate) >= dt
select c).ToList();
In the parameters section, I see 2 parameters being passed in, both of type TimeStamp.
I ran the same query after upgrading my system to dotConnect 8.30. (I did not do any changes with UseDateTimeAsDate property as you suggested) This time, I got one parameter as Date and other as Timestamp.
So it seems like you have fixed the issue partially, if I dont use OracleFunctions.Trunc, then the variable "dt" is mapped to type DATE in the ORACLE world, as it should be(in version 8.3) but if I do use
OracleFunctions.Trunc, then the variable "dt" is mapped to type TIMESTAMP in the ORACLE world.
So while the latest version fixes the issue, which helps me across 60% of my cases, I will appreciate if the OracleFunctions.Trunc issue if fixed too.
Thanks,
Mayank
Re: .NET to Oracle default type mapping
You are using an XML mapping, aren't you? If yes, please set the Type of your LastUpdateDate and DoneDate properties in the store (SSDL) part of the model to DATE. You can do this via interface of Entity Developer (for *.edml models) or with XML Editor (for *.edmx). Does this set the type of the parameter in the query to Date?thakkarmayank wrote:We are not using "Code First" here. So the question of my DB being created from my conceptual model doesnt arise. We have a DB team which builds the tables for us and we base our conceptual model on it.
-
- Posts: 13
- Joined: Fri 23 Apr 2010 21:16
Re: .NET to Oracle default type mapping
Shalex,
We are using xml mapping in emdx and yes, the Type of LastUpdateDate and DoneDate properties in the store (SSDL) part of the model is set to DATE.
The issue is, inspite of both being set to DATE, one parameter goes as DATE and other as TIMESTAMP. The only difference is we are using OracleFunctions.Trunc for one .
~Mayank
We are using xml mapping in emdx and yes, the Type of LastUpdateDate and DoneDate properties in the store (SSDL) part of the model is set to DATE.
The issue is, inspite of both being set to DATE, one parameter goes as DATE and other as TIMESTAMP. The only difference is we are using OracleFunctions.Trunc for one .
~Mayank
Re: .NET to Oracle default type mapping
Thank you for your report. We have reproduced the issue and are investigating it.thakkarmayank wrote:The issue is, inspite of both being set to DATE, one parameter goes as DATE and other as TIMESTAMP. The only difference is we are using OracleFunctions.Trunc for one .
-
- Posts: 13
- Joined: Fri 23 Apr 2010 21:16
Re: .NET to Oracle default type mapping
Thanks.
This bug sure did cause us some performance grief over the past 4 years.
Do we have a time line as when will this be fixed?
~Mayank
This bug sure did cause us some performance grief over the past 4 years.
Do we have a time line as when will this be fixed?
~Mayank
Re: .NET to Oracle default type mapping
It will take several weeks to release the build with the fix. We will notify you about the progress.
-
- Posts: 13
- Joined: Fri 23 Apr 2010 21:16
Re: .NET to Oracle default type mapping
Thanks.
We just renewed our subscription and we can not use the new version because of this issue and http://forums.devart.com/viewtopic.php?f=1&t=29564 issue.
Is there a way we can extend our subscription because of the inconvenience caused?
We just renewed our subscription and we can not use the new version because of this issue and http://forums.devart.com/viewtopic.php?f=1&t=29564 issue.
Is there a way we can extend our subscription because of the inconvenience caused?
Re: .NET to Oracle default type mapping
The bug with using OracleFunctions and EntityFunctions against class properties, which are of the DATE type in SSDL, is fixed. We will notify you when the corresponding build of dotConnect for Oracle is available for download.
Please submit your request to our Sales department (sales at devart*com).thakkarmayank wrote:Is there a way we can extend our subscription because of the inconvenience caused?
Re: .NET to Oracle default type mapping
New version of dotConnect for Oracle 8.4 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 active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=1&t=29687.
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=1&t=29687.