更新:
我错误地粘贴了来自错误重载的查询代码。底部是固定代码
嗨,这是我的域名。
我在追踪一些运动项目,比如说赛车。
司机有名字之类的东西。。。典型的个人资料。
让我所有的比赛(让我们离开寻呼)在这个事件中,显示他们的参赛者信息,包括从司机的数据。
我的问题是,我不认为我的映射(或者条件查询,或者两者都是)对于这个场景是最优的,所以我想问你,如果你在这里看到任何优化的机会。
我特别不喜欢这样一个事实,即目前需要两次往返数据库,而我认为一个子查询可以使它在一次工作。
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="Foo"
namespace="Foo">
<class name="Event" table="Events">
<id name="Id">
<generator class="guid"/>
</id>
<property name="EventId" not-null="true" unique="true" index="IDX_EventId" />
</class>
</hibernate-mapping>
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="Foo"
namespace="Foo">
<class name="Race" table="Races">
<id name="Id">
<generator class="guid"/>
</id>
<property name="RaceId" not-null="true" unique="true" index="IDX_RaceId"/>
<property name="Year" not-null="true" />
<property name="IsValid" not-null="true" />
<property name="Time" not-null="true" />
<many-to-one name="Event" cascade="all" not-null="true" />
<bag name="Contestants" cascade="save-update" inverse="true" lazy="false" batch-size="20" >
<key column="Race"/>
<one-to-many class="Racer"/>
</bag>
</class>
</hibernate-mapping>
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="Foo"
namespace="Foo">
<class name="Racer" table="Racers">
<id name="Id">
<generator class="guid"/>
</id>
<many-to-one name="Race" foreign-key="FK_Racer_has_race" not-null="true" cascade="save-update" />
<property name="Lane" not-null="true" />
<many-to-one name="Driver" foreign-key="FK_Racer_has_driver" cascade="save-update" lazy="false" fetch="join" />
<property name="FinishPosition" />
<property name="Finished" not-null="true" />
</class>
</hibernate-mapping>
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="Foo"
namespace="Foo">
<class name="Driver" table="Drivers">
<id name="Id">
<generator class="hilo"/>
</id>
<property name="Name" not-null="true" length="32" unique="true" index="IDX_Driver_Name" />
</class>
</hibernate-mapping>
public IList<Race> GetMostRecentRacesForEvent( int eventId, int firstRaceToFetch, int count ) {
DetachedCriteria criteria = DetachedCriteria.For( typeof( Race ) ).
CreateAlias( "Event", "event" ).
Add( Restrictions.Eq( "event.EventId", eventId ) ).
AddOrder<Race>( r => r.Time, Order.Desc ).
SetResultTransformer( new DistinctRootEntityResultTransformer() ).
SetFirstResult( firstRaceToFetch ).
SetMaxResults( count );
return this.ExecuteListQuery<Race>(criteria); }
当分页设置为3 races/page时,它生成的SQL如下:
SELECT TOP 3 Id2_1_, RaceId2_1_, Year2_1_, IsValid2_1_, Time2_1_, Event2_1_, Id1_0_, EventId1_0_ FROM (SELECT ROW_NUMBER() OVER(ORDER BY __hibernate_sort_expr_0__ DESC) as row, query.Id2_1_, query.RaceId2_1_, query.Year2_1_, query.IsValid2_1_, query.Time2_1_, query.Event2_1_, query.Id1_0_, query.EventId1_0_, query.__hibernate_sort_expr_0__ FROM (SELECT this_.Id as Id2_1_, this_.RaceId as RaceId2_1_, this_.Year as Year2_1_, this_.IsValid as IsValid2_1_, this_.Time as Time2_1_, this_.Event as Event2_1_, event1_.Id as Id1_0_, event1_.EventId as EventId1_0_, this_.Time as __hibernate_sort_expr_0__ FROM Races this_ inner join Events event1_ on this_.Event=event1_.Id WHERE event1_.EventId = @p0) query ) page WHERE page.row > 0 ORDER BY __hibernate_sort_expr_0__ DESC
第二个问题:
SELECT contestant0_.Race as Race2_,
contestant0_.Id as Id2_,
contestant0_.Id as Id0_1_,
contestant0_.Race as Race0_1_,
contestant0_.Lane as Lane0_1_,
contestant0_.Driver as Driver0_1_,
contestant0_.FinishPosition as FinishPo5_0_1_,
contestant0_.Finished as Finished0_1_,
driver1_.Id as Id3_0_,
driver1_.Name as Name3_0_
FROM Racers contestant0_
left outer join Drivers driver1_
on contestant0_.Driver = driver1_.Id
WHERE contestant0_.Race in ('4157280d-be8d-44be-8077-a770ef7cd394' /* @p0 */,'74e1bfaa-9926-43c7-8b17-e242634dc32f' /* @p1 */,'e1e86b67-2c37-4fbe-8793-21e84a6e4be4' /* @p2 */)
