Retrieving large datasets using Fluent NHibernate

I'm building a solution where I'm retri开发者_高级运维eving large amounts of data from the database(5k to 10k records). Our existing data access layer uses Fluent NHibernate but I'm "scared" that I will incur a large amount of overhead by hydrating object models that represent the database entities.

Can I retrieve simply an ADO dataset?

---

Yes you should be concerned about the performance of this. You can look at using the IStatelessSession functionality of NHibernate. However this probably won't give you the performance you are looking for. While I haven't used NH since 2.1.2GA, I would find it unlikely that they've substantially improved the performance of NH when it comes to bulk operations. To put it bluntly, NH just sucks (and most ORMs in general for that matter) when it comes to bulk operations.

Q: Can I retrieve simply an ADO dataset?

Of course you can. Just because you're using NHibernate doesn't mean you can't new up an ADO.NET connection and hit the database in the raw.

As much as I loathe data tables and data sets, this one of the rare cases you might want to consider using them instead of adding the overhead of mapping / creating the objects associated with your 10K rows of data.

---

Depending on how much performance you need, there are a few options. Nothing will ever beat using a sqldatareader, as that's what's underneath just about every .NET ORM implementation. In addition to being the fastest, it can take a lot less memory if you don't need to save a list of all the records after the query.

Now as for your performance worries, 5k-10k records isn't that high. I've pulled upwards of a million rows out of nhibernate before, but obviously the records weren't huge and it was for a single case. If you're doing this on a high traffic website then of course you will have to be more efficient if you're hitting bottlenecks. If you're thinking about datasets, I'd suggest instead trying Massive because it should still be more efficient than DataSet/Table and more convenient.

---

You can use "Scalar queries", which is in fact native SQL query returning a list of object[] (one object[] per row):

sess.CreateSQLQuery("SELECT * FROM CATS")
 .AddScalar("ID", NHibernateUtil.Int64)
 .AddScalar("NAME", NHibernateUtil.String)
 .AddScalar("BIRTHDATE", NHibernateUtil.Date)

Example from NHibernate documentation: http://nhibernate.info/doc/nh/en/index.html#d0e10794