forcing the use of a specific index in a query

mosi · **Joined:** Fri Sep 28, 2007 4:12 am **Posts:** 15

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

wolli · **Posted:** Wed Jan 14, 2009 11:15 am

You can specifiy a custom select statement in the mapping which includes the WITH clause:

http://nhforge.org/doc/nh/en/index.html#querysql-load

mosi · **Joined:** Fri Sep 28, 2007 4:12 am **Posts:** 15

That could indeed solve the problem. Somehow my custom query isn't used by nhibernate though.
I've tried to put a custom query into the mapping file for the Vorgang class for loading, it now looks like this
(Note: the ID = 1 in the SQL is for testing)

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>

      <many-to-one name="Versandeinheit" class="core.domain.posdata.Versandeinheit, POSData.Core" column="id_versandeinheit" />
      <bag name="Vorgang_Stufeliste" inverse="true">
         <key column="id_vorgang"/>
         <one-to-many class="core.domain.posdata.Vorgang_Stufe, POSData.Core"/>

      </bag>
      <loader query-ref="qry_vorgang"/>
   </class>
   <sql-query name="qry_vorgang">
      <return alias="vorgang" class="core.domain.posdata.Vorgang, POSData.Core"/>
      SELECT {vorgang.*}
      FROM t_vorgang WITH (INDEX = 2)
      WHERE ID = ? AND ID = 1
      FOR UPDATE
   </sql-query>
</hibernate-mapping>

My query is build via ICriteria this way:

Code:

ICriteria lcriteria = NHibernateSession.CreateCriteria(typeof(Vorgang));
            ICriteria lversandeinheitcriteria = lcriteria.CreateCriteria("Versandeinheit");            lversandeinheitcriteria.Add(NHibernate.Criterion.Expression.Eq("Nummer", Versandeinheitnummer));

Somehow this custom sql query seems to be completely ignored here as the generated SQL didn't change at all.

Am I missing something obvious here?

wolli · **Posted:** Thu Jan 15, 2009 5:47 am

The custom query will probably not be used for a criteria or query, just for Load/Get and if a collection is loaded. Sorry, I didn't think far enough. So this idea won't be working for a special query. But you can still use the named SQL query:

session.GetNameQuery("qry_vorgang").SetParameter(...).List();