Is is possible to tell NHibernate which Index to use for a query?
The SQL Server in the production environment here somehow decided not to use an index and now timeouts happen frequently.
I figured out which index should be used and already verified that it would work by running a modified query that included a
Code:
WITH (INDEX=<id_of_index>)
through the query analizer.
Now I'm trying to find a way to tell nhibernate that it should use that index too.
The only thing I've stumbled upon so far is the index="" attribute in the many-to-one element but apparently that one doesn't quite do the trick as the generated SQL does not change at all.
Maybe someone can point me in the right direction as I couldn't find anything in this board and on the net.
If you need, here's some info about the mapping etc.
The <many-to-one> element from the first mapping document is the important relation here.
One Versandeinheit can contain multiple Vorgang objects, imagine something like a box (Versandeinheit) full of letters (Vorgang) in it.
Hibernate version: 2.0.1 GA
Mapping documents:the two relevant ones here:
Code:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" >
<class name="core.domain.posdata.Vorgang, POSData.Core" table="t_vorgang">
<id name="Id" type="Int32" column="id">
<generator class="identity"></generator>
</id>
<!-- some more fields -->
<many-to-one name="Versandeinheit" class="core.domain.posdata.Versandeinheit, POSData.Core" column="id_versandeinheit" index="IX_t_vorgang_id_versandeinheit" />
<!-- apparently the index="..." attribute does nothing -->
</class>
</hibernate-mapping>
Code:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" >
<class name="core.domain.posdata.Versandeinheit, POSData.Core" table="t_versandeinheit">
<id name="Id" type="Int32" column="id">
<generator class="identity"></generator>
</id>
<property name="Nummer" column="nummer" type="String" />
<property name="Kostentraeger" column="kostentraeger" type="Int32" />
<bag name="Vorgangliste" inverse="true">
<key column="id_versandeinheit"/>
<one-to-many class="core.domain.posdata.Vorgang, POSData.Core" />
</bag>
</class>
</hibernate-mapping>
Name and version of the database you are using:MS Sql 2000 Standard Version 8.00.2050
The generated SQL (show_sql=true):Code:
SELECT top 50 this_.id as id5_1_, this_.id_versandeinheit as id6_5_1_, versandein1_.id as id4_0_, versandein1_.nummer as nummer4_0_, versandein1_.kostentraeger as kostentr3_4_0_
FROM t_vorgang this_ inner join t_versandeinheit versandein1_ on this_.id_versandeinheit=versandein1_.id
WHERE versandein1_.nummer = @p0