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

On Sep 20, 9:22 am, djn <[email protected]> wrote:
> 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

-- 
You received this message because you are subscribed to the Google Groups 
"nhusers" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to 
[email protected].
For more options, visit this group at 
http://groups.google.com/group/nhusers?hl=en.

Reply via email to