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.
