我在使用一个查询时遇到了问题,需要选择一定时间范围内的记录。
我正在从一个类型为DATE的列中进行选择。我已经将这个列映射为DateTime属性,虽然查询可以工作但速度很慢。
生成的查询看起来像这样:(由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);
在我们的数据库中,执行需要大约1500毫秒。
如果我们手动更改查询为:
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);
这段程序执行时间约为50毫秒。
有没有办法使NHibernate生成to_date而不是timestamp进行日期比较?
我有点困惑RegisterDateTimeTypeMappings在Oracle10gDialect中的工作原理,但我尝试扩展它并更改该方法为:
protected override void RegisterDateTimeTypeMappings()
{
RegisterColumnType(DbType.Date, "DATE");
//RegisterColumnType(DbType.DateTime, "TIMESTAMP(4)");
RegisterColumnType(DbType.DateTime, "DATE");
RegisterColumnType(DbType.Time, "TIMESTAMP(4)");
}
但这并没有帮助。
我们的环境是:
- .net (C#) 4.0
- NHibernate 3.1.0,主要使用Linq
- ODP.Net 11.2.2.0,连接Oracle 11g
有人有什么建议吗?
谢谢, ./Daniel