Performance Problem with "Order by"

prof · **Joined:** Sat Jan 10, 2009 3:37 pm **Posts:** 7

Hi @All..

I'm new to NHibernate.. I searched the internet and the forum but unfortunately i didn't find any answers.

I am using MSSQL Express, in the Database painter (SQL Server Management Studio Express) the query takes less then 1sec:

select top 250 * from XXX order by Date desc;

(The Field "Date" has an index on the Database)

When i do the same with NHibernate it takes me over 17sec !!

session.CreateCriteria(typeof(XXX))
.AddOrder( Order.Desc("Date") )
.SetMaxResults(250)
.List();

Does somebody know why this takes so much longer?
Thanks for the Answers!

Thomas T. · **Joined:** Fri May 25, 2007 5:06 am **Posts:** 11

One of the reason might be your mapping if it's not optimal. Review SQL for which NHibernate generated for this case, execute it through the Management Studio Express and improve your mapping if needed.

prof · **Joined:** Sat Jan 10, 2009 3:37 pm **Posts:** 7

i tried exactly what you suggested... But i still cant find the causer!

the mapping in this case is really easy:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="TestNHibernate" assembly="TestNHibernate">
<class name="TestNHibernate.XXX, TestNHibernate" table="TEST" lazy="false" optimistic-lock="version" select-before-update="true">
<id name="ID">
<column name="T_ID" not-null="true"/>
<generator class="native" />
</id>
<version name="Modified" column="T_Modified" type="datetime" />
<property name="Date">
<column name="T_DATE"/>
</property>
<property name="Name">
<column name="T_NAME"/>
</property>
.
.
.
.
</class>
</hibernate-mapping>

When i execute the Statement without an Order by it works fine..

session.CreateCriteria(typeof(XXX))
.SetMaxResults(250)
.List();

wolli · **Posted:** Mon Jan 12, 2009 3:07 am

Set show_sql="true" in your configuration and check the generated sql. Does hibernate generate the same statement you're testing ?

Have in mind that hibernate also has to generate the according objects (which shouldn't take that long, I admit). Check if it's the statement that wastes the time or the object generation.

And I think there are some problems with NHibernate, SQL Server 2005 and paging ... search for paging in the nhibernate development mailing list and the nhusers group at google.

prof · **Joined:** Sat Jan 10, 2009 3:37 pm **Posts:** 7

yes, hibernate generates the same sql. i pastet the string from the logfile into the database painter..

i think it isnt the time to generate the according objects.. because
when i execute the statement without the order by its fast as hell.

thanks for the answer, i will search for the paging

prof · **Joined:** Sat Jan 10, 2009 3:37 pm **Posts:** 7

yes, hibernate generates the same sql. i pastet the string from the logfile into the database painter..

i think it isnt the time to generate the according objects.. because
when i execute the statement without the order by its fast as hell.

thanks for the answer, i will search for the paging

wolli · **Posted:** Mon Jan 12, 2009 2:53 pm

Quote:

i think it isnt the time to generate the according objects.. because
when i execute the statement without the order by its fast as hell.

That's exactly why I mentioned the object creation (Overall time - SQL = object generation). There's no obvious reason why the same statement would take longer when it's run by hibernate. But on the other side there's also no real explanation why the generation of a few objects should take that long.

What happens if you run the same query again ? Does it take the same time ?

prof · **Joined:** Sat Jan 10, 2009 3:37 pm **Posts:** 7

yes.. its in both way a brainteaser to me.. :-)
it really doesnt make sense at all!

if i run the query again, its a few second faster... but i think
that should be normal.

i'm gonna try the same with an other table and other objects..
maybe i will find something

thanks