Oracle 11g and NHibernate DateTime to Date mapping

[djn]

Hi.

I'm having trouble with a query where I select records within a given
timespan.

The column I'm selecting from is of type DATE. I have mapped this
column as a DateTime property, and the query works but is slow.

The generated query looks like: (provided by nhprofiler)

select kifkalende0_.KALENDER_MEDARBEJDER_ID as KALENDER1_119_0_,
kifkalende1_.KALENDER_EMNE_ID as KALENDER1_210_1_,
kifkalende0_.OPDATERET_TIDSPUNKT as OPDATERET2_119_0_,
kifkalende0_.AENDRET as AENDRET119_0_,
kifkalende0_.OPDATERET_AF as OPDATERET4_119_0_,
kifkalende0_.OPRETTET_AF as OPRETTET5_119_0_,
kifkalende0_.OPRETTET_TIDSPUNKT as OPRETTET6_119_0_,
kifkalende0_.SLETTET as SLETTET119_0_,
kifkalende0_.KALENDER_EMNE_ID as KALENDER8_119_0_,
kifkalende0_.MEDARBEJDER_ID as MEDARBEJ9_119_0_,
kifkalende1_.OPDATERET_TIDSPUNKT as OPDATERET2_210_1_,
kifkalende1_.BESKRIVELSE as BESKRIVE3_210_1_,
kifkalende1_.DATO as DATO210_1_,
kifkalende1_.ER_FRA_SAG as ER5_210_1_,
kifkalende1_.FRA_SAG_ID as FRA6_210_1_,
kifkalende1_.FRA_TABEL as FRA7_210_1_,
kifkalende1_.FRA_TID as FRA8_210_1_,
kifkalende1_.OPDATERET_AF as OPDATERET9_210_1_,
kifkalende1_.OPRETTET_AF as OPRETTET10_210_1_,
kifkalende1_.OPRETTET_TIDSPUNKT as OPRETTET11_210_1_,
kifkalende1_.SAG_TYPE as SAG12_210_1_,
kifkalende1_.TIL_TID as TIL13_210_1_,
kifkalende1_.YDERLIGERE_BESKRIVELSE as YDERLIGERE14_210_1_,
kifkalende1_.EMNE_ID as EMNE15_210_1_,
kifkalende1_.PERSON_ID as PERSON16_210_1_
from "KIF_KALENDER_MEDARBEJDER" kifkalende0_
left outer join "KIF_KALENDER_EMNE" kifkalende1_
on kifkalende0_.KALENDER_EMNE_ID =
kifkalende1_.KALENDER_EMNE_ID,
"KIF_KALENDER_EMNE" kifkalende2_
where kifkalende0_.KALENDER_EMNE_ID = kifkalende2_.KALENDER_EMNE_ID
and (kifkalende0_.MEDARBEJDER_ID in (7624 /* :p3 */,6226 /* :p4
*/,7382 /* :p5 */,5774 /* :p6 */,
5775 /* :p7 */,8259 /* :p8
*/,8218 /* :p9 */,9899 /* :p10 */,
6000 /* :p11 */,5779 /
* :p12 */,5780 /* :p13 */,5782 /* :p14 */,
5783 /* :p15 */,5784 /
* :p16 */,5785 /* :p17 */,5788 /* :p18 */,
5789 /* :p19 */,5790 /
* :p20 */,7341 /* :p21 */,8963 /* :p22 */,
10201 /* :p23 */,10388 /
* :p24 */))
and kifkalende2_.DATO >= TIMESTAMP '2010-11-10 00:00:00.00' /
* :p0 */
and kifkalende2_.DATO <= TIMESTAMP '2010-11-10 23:59:59.00' /
* :p1 */
and (kifkalende0_.SLETTET = TIMESTAMP '1899-12-31 00:00:00.00' /
* :p2 */
or kifkalende0_.SLETTET is null);

And in our database, it takes around a 1500 ms to execute.

If we change the query manually to:

select kifkalende0_.KALENDER_MEDARBEJDER_ID as KALENDER1_119_0_,
kifkalende1_.KALENDER_EMNE_ID as KALENDER1_210_1_,
kifkalende0_.OPDATERET_TIDSPUNKT as OPDATERET2_119_0_,
kifkalende0_.AENDRET as AENDRET119_0_,
kifkalende0_.OPDATERET_AF as OPDATERET4_119_0_,
kifkalende0_.OPRETTET_AF as OPRETTET5_119_0_,
kifkalende0_.OPRETTET_TIDSPUNKT as OPRETTET6_119_0_,
kifkalende0_.SLETTET as SLETTET119_0_,
kifkalende0_.KALENDER_EMNE_ID as KALENDER8_119_0_,
kifkalende0_.MEDARBEJDER_ID as MEDARBEJ9_119_0_,
kifkalende1_.OPDATERET_TIDSPUNKT as OPDATERET2_210_1_,
kifkalende1_.BESKRIVELSE as BESKRIVE3_210_1_,
kifkalende1_.DATO as DATO210_1_,
kifkalende1_.ER_FRA_SAG as ER5_210_1_,
kifkalende1_.FRA_SAG_ID as FRA6_210_1_,
kifkalende1_.FRA_TABEL as FRA7_210_1_,
kifkalende1_.FRA_TID as FRA8_210_1_,
kifkalende1_.OPDATERET_AF as OPDATERET9_210_1_,
kifkalende1_.OPRETTET_AF as OPRETTET10_210_1_,
kifkalende1_.OPRETTET_TIDSPUNKT as OPRETTET11_210_1_,
kifkalende1_.SAG_TYPE as SAG12_210_1_,
kifkalende1_.TIL_TID as TIL13_210_1_,
kifkalende1_.YDERLIGERE_BESKRIVELSE as YDERLIGERE14_210_1_,
kifkalende1_.EMNE_ID as EMNE15_210_1_,
kifkalende1_.PERSON_ID as PERSON16_210_1_
from "KIF_KALENDER_MEDARBEJDER" kifkalende0_
left outer join "KIF_KALENDER_EMNE" kifkalende1_
on kifkalende0_.KALENDER_EMNE_ID =
kifkalende1_.KALENDER_EMNE_ID,
"KIF_KALENDER_EMNE" kifkalende2_
where kifkalende0_.KALENDER_EMNE_ID = kifkalende2_.KALENDER_EMNE_ID
and (kifkalende0_.MEDARBEJDER_ID in (7624 /* :p3 */,6226 /* :p4
*/,7382 /* :p5 */,5774 /* :p6 */,
5775 /* :p7 */,8259 /* :p8
*/,8218 /* :p9 */,9899 /* :p10 */,
6000 /* :p11 */,5779 /
* :p12 */,5780 /* :p13 */,5782 /* :p14 */,
5783 /* :p15 */,5784 /
* :p16 */,5785 /* :p17 */,5788 /* :p18 */,
5789 /* :p19 */,5790 /
* :p20 */,7341 /* :p21 */,8963 /* :p22 */,
10201 /* :p23 */,10388 /
* :p24 */))
and kifkalende2_.DATO>=to_date('10-11-2010 00:00:00', 'DD-MM-
YYYY HH24:MI:SS')
and kifkalende2_.DATO<=to_date('10-11-2010 23:59:59', 'DD-MM-
YYYY HH24:MI:SS')
and (kifkalende0_.SLETTET=to_date('31-12-1899 00:00:00', 'DD-MM-
YYYY HH24:MI:SS')
or kifkalende0_.SLETTET is null);

it executes in about 50ms.

Is there any way to make NHibernate generate to_date instead of
timestamp for date comparisons??

I am bit confused as to how the RegisterDateTimeTypeMappings works in
the Oracle10gDialect, but I tried extending it, changing the method to

protected override void RegisterDateTimeTypeMappings()
{
RegisterColumnType(DbType.Date, "DATE");
//RegisterColumnType(DbType.DateTime, "TIMESTAMP(4)");
RegisterColumnType(DbType.DateTime, "DATE");
RegisterColumnType(DbType.Time, "TIMESTAMP(4)");
}
but that did not help.


Do anyone have any suggestions?


Regards,
./Daniel

[djn]

Oh!

I forgot to mention our environment:
.net (C#) 4.0
NHibernate 3.1.0, used primarily via Linq
ODP.Net 11.2.2.0


Regards,
./Daniel

[Stig Christensen]

Also you can fix it by using a custom type

See

https://groups.google.com/group/nhusers/browse_thread/thread/1e68220bcafcf28c


On 30 Aug., 14:45, Dries Verbeke <dries.verb...@gmail.com> wrote:
> Check out this post<http://stackoverflow.com/questions/7684163/problems-with-nhibernate-a...>for the answer
>
>
>
>
>
>
>
> On Friday, August 17, 2012 5:15:54 AM UTC+2, Milind wrote:
>
> > did you found the solution as I have similar issue.
>
> > please let me know

[Stig Christensen]

The fix on stackoverflow removes the default behavior, which is not
recommended. This is the from the NHibernate source.

/// <remarks>
/// This adds logic to ensure that a DbType.Boolean parameter is not
created since
/// ODP.NET doesn't support it.
/// </remarks>
protected override void InitializeParameter(IDbDataParameter
dbParam, string name, SqlType sqlType)
{
// if the parameter coming in contains a boolean then we need to
convert it
// to another type since ODP.NET doesn't support DbType.Boolean
switch (sqlType.DbType)
{
case DbType.Boolean:
base.InitializeParameter(dbParam, name, SqlTypeFactory.Int16);
break;
case DbType.Guid:
base.InitializeParameter(dbParam, name, GuidSqlType);
break;
default:
base.InitializeParameter(dbParam, name, sqlType);
break;

[cremor]

Why should the fix from stackoverflow remove any behaviour? It overrides OracleDataClientDriver, so the NHibernate code you posted is still executed.
As far as I can say this is a perfect solution. At least if you only use DATE columns, I don't know how it would affect queries on TIMESTAMP columns.