batching update/delete statements

[Davy Brion]

Can somebody confirm that batching update/delete statements by setting the adonet.batch_size property to a value higher than 0 actually works with nhibernate 2.0 alpha 1 or 2?

i set the property to a positive value in my hibernate.cfg.xml file, but when i run the following test code, sql server profiler shows that each delete statement is executed seperately:

            var category1 = new ProductCategory { Name = "Test1", Description = "Description" };
            var category2 = new ProductCategory { Name = "Test2", Description = "Description" };
            var category3 = new ProductCategory { Name = "Test3", Description = "Description" };
            Session.SaveOrUpdate(category1);
            Session.SaveOrUpdate(category2);
            Session.SaveOrUpdate(category3);
            Session.Evict(category1);
            Session.Evict(category2);
            Session.Evict(category3);

            var criteria = Session.CreateCriteria(typeof(ProductCategory))
                .Add(Expression.Like("Name", "Test%"));

            foreach (var entity in criteria.List())
            {
                Session.Delete(entity);
            }

            Session.Flush();

hibernate config file looks like this:

<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
    <session-factory>
        <property name="dialect">NHibernate.Dialect.MsSql2005Dialect</property>
        <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
        <property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property>
        <property name="connection.connection_string">Server=(local);initial catalog=Northwind;Integrated Security=SSPI</property>
        <property name="show_sql">true</property>
        <property name="adonet.batch_size">20</property>
    </session-factory>
</hibernate-configuration>
 

[Ayende Rahien]

You need to look at the TCP layer. NH batching work at the BCP (SQL Server protocol) layer, not the command layer.

You can verify that it works using TCP trace or using NH's logging.

[Davy Brion]

ic... i was expecting to see the SQL:BatchStarting and SQL:BatchCompleted lines in the sql server profiler output but since they weren't there i assumed it wasn't batching the statements.  Do you happen to know why those lines do show up when you use MultiCriteria?

[Ayende Rahien]

There is a difference between the way both work.

For batching CUD, we use SqlCommandSet, which will batch statement in the TCP layer. From SQL Server perspective, you are getting just sending lots of statements.

For batching reads, we send several commands in the same statement, and SQL Server split that to a batch.

[Davy Brion]

thanks for clearing that up :)