Performance with large Resultsets

a.pawi · **Posted:** Thu Oct 30, 2008 9:29 am

Hi to the Hibernate Community,

i am currently researching the NHibernate 2 in order to develop a new datalayer for our application.

A special demand is to get about 150k datarows from a simple table in an Oracle Database (10gR2) as fast as possible. The table isnt associated with other ones... just a simple table.
At current configuration it takes about 22sec to get this List of Objects. My first thought was to change the fetchsize for the session to get a faster result.

Unfortunetly i didnt find the ISession.SetFetchSize(..) as it is declared in the Hibernate 3 API for Java.

If it helps, here my hibernate.cfg.xml:

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-configuration  xmlns="urn:nhibernate-configuration-2.2" >
  <!--  -->
  <session-factory name="nhibernator">
    <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
    <property name="connection.driver_class">NHibernate.Driver.OracleClientDriver</property>    
    <property name="connection.connection_string">persist security info=True;data source=SOURCE; user id=ID; password=PWD;</property>
    <property name="show_sql">true</property>
    <property name="prepare_sql">false</property>
    <property name="dialect">NHibernate.Dialect.Oracle9Dialect</property>
    <property name="use_outer_join">false</property>
    <property name="query.substitutions">true 1, false 0, yes 'Y', no 'N'</property>
    <property name="cache.provider_class">NHibernate.Cache.HashtableCacheProvider</property>
    <mapping assembly="BOMPrins" />

  </session-factory>
</hibernate-configuration>

I´ve found several options for the batch-size to configure in the hibernate.cfg.xml or the mapping file for the entitty but nothing could gain some speed :(

Additionaly i´ve read that i could use

Code:

<property name="connection.driver_class">NHibernate.Driver.OracleClientDriver</property>    

instead of

Code:

<property name="connection.driver_class">NHibernate.Driver.OracleDataClientDriver</property>    

but NHibernate tells me that it cannot instantiate the driver class from this configuration. Ive installed the latest ODP.

You musst understand: I HAVE to speed it up, because the whole develop-team in my company worked with MS DataSets and Borland DataTables in the past and they are very critical in point of ORMs :) I dont think, that i´ll get the same speed they had in past, but i want to get i bit closer to their 12secs ;)

The NHibernate Reference and Google dont help me anymore, so i am turnig to this forum.

Can anyone give me some advice to tune up this execution?

With best regards
Adam

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

Are you referring to the batch-size? That is specified at the class or collection mapping level.
See 19.1.5. Using batch fetching for examples

Code:

<class name="Person" batch-size="10">...</class>


<class name="Person">
    <set name="cats" batch-size="3">
        ...
    </set>
</class>

a.pawi · **Posted:** Fri Oct 31, 2008 5:12 am

Hi,
thanks for your response. Yes i am referring the batch-size, but it doesnt influence the speed at all.

Does this configuration only affect collections associated to the entity and not the entity itself anyway? Then why it can be defined at entity and collection level? Additionaly i can add a the "adonet.batch_size" property to the hibernate.cfg ... but nothing makes a difference! Im confused ;)

Tell me that i´ve missed something to configure please, because im running out of ideas slowly.

Here my code if it helps ...

Code:

using (ISession session = _sessionFactory.OpenSession())
{
  return session.CreateCriteria(typeof(MyEntity))
    .Add(Expression.Eq("CanalID", (Decimal)111))
    .List<MyEntity>();
}   

wolli · **Posted:** Fri Oct 31, 2008 6:50 am

adonet.batch_size is for update/inserts only. If you specifiy a value larger than 0, statements are sent as a "batch" to the server instead of each statement on its own.

batch-size on a class/collection is used for proxy initialization only (afaik).
http://www.hibernate.org/hib_docs/nhibernate/1.2/reference/en/html/performance.html#performance-fetching-batch

Have you checked the generated sql statements ? If you query for objects without any associations, one statement should be used. And you should disable lazy-loading for that class which probably would result in a lot of sql statements when all 150K proxies are initialized.[/list]

a.pawi · **Posted:** Fri Oct 31, 2008 8:34 am

Tanks for explaining the adonet.batch_size.

Quote:

batch-size on a class/collection is used for proxy initialization only (afaik).

I assumed it too, so i dont think it could help in my situation.

the show_sql shows just one executed query but i dont know if there are posted more statements under the hood. I turned on the oracle trace but it isnt in a human-readable format ;)

does somebody know a free oracle moniter for the client-side?

wolli · **Posted:** Sun Nov 02, 2008 4:03 am

show_sql shows all SQL that NHibernate executes, so if you only see one, than that's it. What I discovered in my application, that during develoment, logging and running it in Visual Studio have an huge impact. Especially log level DEBUG.

Have you checked if the time is spent in NHibernate while hydrating the objects or in the query ? Try an index on canalid. Besides that, I can't think of another way of tuning.

The question is, do you really need a resultset with 150K ? There are a lot of discussions about using an ORM or not in http://groups.google.com/group/nhusers. Search there for ORM.

a.pawi · **Posted:** Mon Nov 03, 2008 4:54 am

Hi again,

thanks for you replies.

- The log4net is currently disabled.
- Running the binary without visual studio didnt make it better
- CanalID is indexed already

You are very right that its not the best idea to get 150k datarows ... in future it should be filtered in a better way. It was just an ugly idea of my project-leader, because it was made this way in the past ;)

Greetings
Adam

a.pawi · **Posted:** Thu Nov 13, 2008 10:54 am

For everyone who has similar performance requirements as me i will post my results for this topic.

If there is no log4net configured for the project it doesnt mean, that nhibernate is not logging anyway. So i thought, that the logging was disabled. Now i configured log4net for my project and configured the level to error. The result was a 60% faster query. I assume nhibernate logs at info or debug level by default. Now i still dont understand to which output nhibernate is logging its logs, when no log4net is configured?

Maybe it helps in future questions.

a.pawi · **Posted:** Thu Nov 13, 2008 10:55 am

- double post -