How to improve select performance in NHibernate

Question

I need to materialize about 20000 records from database and write it as CSV. Unfortunately materialization of that records is extremely slow in NHibernate. Is there an option to get SQL generated by Nhibernate Linq Provider or write custom materialization mechanism that instead of creating new object will write line to stream?

Wrong tool for the job. But try stateless session. – Diego Mijelshon Nov 02 '12 at 19:44 — Diego Mijelshon, Nov 02 '12 at 19:44
Diego - Did you know any tool that generate SQL from Linq? – Sławomir Rosiek Nov 04 '12 at 18:24 — Sławomir Rosiek, Nov 04 '12 at 18:24

score 3 · Answer 1 · answered Nov 02 '12 at 15:18

3

You should probably project the loaded data directly to some DTO type (or anonymous class) using LINQ's select clause. This will bypass everything NHibernate normally does to track state.

answered Nov 02 '12 at 15:18

Oskar Berggren

5,583
1
19
36

score 1 · Answer 2 · answered Nov 02 '12 at 14:20

1

You can use a stateless session to speed things up if you are really only reading from the database and writing out to CSV and not updating any of the records.

This blog post

http://ayende.com/blog/4137/nhibernate-perf-tricks

has some info on performance speedups for bulk operations.

answered Nov 02 '12 at 14:20

Rich Amos

259
2
4

1

As an aside, NHibernate (or any ORM for that matter) is not designed around bulk operations. If you need to process large amounts of data, you should probably look to other options over NHibernate. – Matthew Brubaker Nov 02 '12 at 14:45

score 1 · Answer 3 · answered Nov 08 '12 at 10:08

You should first consider using a tool like NHProf (http://www.hibernatingrhinos.com/products/NHProf) to check the SQL query generated by NHibernate Linq Provider.

Using NHibernate doesn't permit to make full abstraction of the SQL DB it relies on. If your query involves more than one table or where criterias, you need to think about the usual SQL performance issues (i.e. indexes, columns you're joining on, subqueries).

20000 records doesn't seem a lot to me, so you should first look in that direction.

How to improve select performance in NHibernate

3 Answers3