One simple HQL-statement generates one query per row

hugoascom · **Joined:** Mon May 05, 2008 3:17 am **Posts:** 2

Hello!

I want to fetch som data for readonly purpose from a view in my MSSQL database to an entity through NHIbernate.

The problem is: When I log all database calls (in sql profiler), I discovered that NHibernate first did one query to fetch all ID's, then one query for each row. There is a lot of rows in this view. How can I make NHibernate fetch everything in a single sql statement? I've tried to add "batch-size="100"" to the mapping file, but it has no effect.

The generated SQL:

Code:

select dataexport0_.TaktID as x0_0_ from vwDataExport_Takt dataexport0_ where ((Endx is null )or(Endx>'2007-05-06 00:00:00' ))and(Start<='2008-05-05 00:00:00' ) order by  TaktID

Code:

exec sp_executesql N'SELECT dataexport0_.TaktID as TaktID6_0_, dataexport0_.LineName as LineName6_0_, dataexport0_.Start as Start6_0_, dataexport0_.Endx as Endx6_0_, dataexport0_.WorkedTime as WorkedTime6_0_, dataexport0_.TaktTime as TaktTime6_0_ FROM vwDataExport_Takt dataexport0_ WHERE dataexport0_.TaktID=@p0', N'@p0 int', @p0 = 1

Code:

exec sp_executesql N'SELECT dataexport0_.TaktID as TaktID6_0_, dataexport0_.LineName as LineName6_0_, dataexport0_.Start as Start6_0_, dataexport0_.Endx as Endx6_0_, dataexport0_.WorkedTime as WorkedTime6_0_, dataexport0_.TaktTime as TaktTime6_0_ FROM vwDataExport_Takt dataexport0_ WHERE dataexport0_.TaktID=@p0', N'@p0 int', @p0 = 2

and so on.......

Hibernate version:
v2.0.50727

Mapping documents:

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   namespace="AdministrationBase.DataExport"
                   assembly="AdministrationBase">
  <class name="DataExport_Takt" table="vwDataExport_Takt">
    <id name="TaktID">
      <generator class="identity" />
    </id>
    <property name="LineName"/>
    <property name="Start"/>
    <property name="Endx"/>
    <property name="WorkedTime"/>
    <property name="TaktTime"/>
  </class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

Code:

ISession session = sessionFactory.OpenSession();
IQuery query = session.CreateQuery(
    "from DataExport_Takt where (Endx is null or Endx > '" + start +
    "') and Start <= '" + end + "' order by TaktID");

foreach (DataExport_Takt takt in query.Enumerable())
    takts.Add(takt);

session.Close();

Name and version of the database you are using:
mssql2000

wolli · **Posted:** Mon May 05, 2008 5:37 am

query.Enumerable() does exactly this. You can do

foreach (DataExport_Takt takt in query.List<DataExport_Takt>())
takts.Add(takt);

instead. That should result in a single sql.

hugoascom · **Joined:** Mon May 05, 2008 3:17 am **Posts:** 2

Thank you!
Just the type of answer I wanted to hear! :-)