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.