We are currently using NHibernate and having great success in our transactional management of data (working with one or two things at a time.) However, I would like to hear from others on how they might handle the following situations...

1. Displaying Web-Based Report

So far, I am not having any trouble with this. Our approach is to use custom paging and only retrieve up to 100 records to display on a page. So, I use a Criteria query to retreve the Student objects and then bind to a DataGrid. This seems like a reasonable approach. At least we have not had too many performance issues yet.

2. Generating Excel File Report

This example is where I would like to get some other opinions. We have the same basic reporting capabilities, however, in this example, we do not want to limit the results to 100 at a time. We may need to generate an Excel document with ten thousand records and then allow the user to download that file. If I follow my same approach as above, using a Criteria query or even HQL to retrieve the Student objects, then I would have to loop through each one, generating the appropriate CSV/Excel record based on the data from the object.

I see a few possible issues with #2...

First, isn't retrieving that many objects going to put a strain on memory and also performance? Doesn't this approach load ten thousand Students into memory?

Second, doesn't looping through these ten thousand records also put a strain on the performance of the application?

Is there an efficient method to perform this operation with NHibernate? Or, should I be using ADO.Net directly to return a DataReader for example?

Finally, I have gotten used to having this wonderful NHibernate set of tools for nearly 100% of my data access layer. I have been implementing a domain driven design approach to my application. So, I am wanting to come up with a solution that can fit nicely within this approach.

I appreciate any suggestions or advice!

Kevin

First, make sure that you can't use an actual reporting tool to generate an Excel report directly from the data. E.g., MS SQL Reporting Services gives you Excel reports "for free". The problem has been solved. NH is a great tool, but there are things for which it simply isn't appropriate.

If in fact the business objects and logic therein contribute to the reported data (e.g., computed properties or decrypting encrypted data) then you have fewer options. Otherwise, the answer would seem to be: this is not what NH was built for.

I do not think we have access to any reporting tools. I will check.

However, we may have some business rules or logic that will need to contribute, as you mention. We have a number of encrypted fields (data is stored in the DB in encrypted format.) So, in that case, it sounds like the Student object will have to help out with the report generation. Is this a bad design on my part? Is there a better approach?

I guess I could create a performance oriented report generator based on using NHibernate and domain objects. Then, I could "generate" the report in a separate thread and notify the user when it is ready to download.

Is there a way in NHibernate to more efficiently load this number of objects, sort of like a DataReader does, with a read-only, one-at-a-time approach? For example,



How would I approach this with NHibernate?

Thanks!

That works, but personally I prefer to return what I call reporting objects. NHibernate lets you specify just the fields you want and then place them in to a strongly typed class. That class just needs to take the fields in the constructor in the same way the HQL uses them. Create the class with the fields you want. Add a constructor taking the fields you want. Add an import to a mapping file, and then call the constructor with NEW MyReportingObject() in the HQL.

This should help with performance.




Reference my above suggestion. Use the reporting objects. An easy (even if it is not the most efficient) way to generate excel documents from an ASP.NET page is to render the DataGrid to an excel file instead of the UI. Excel can read tables and will then show the data in a readable excel format which can be saved as a normal excel document. This is a little kludgy, but it is easy and free. There are numerous examples of how to do this on the web. This of course is just an option if you are not using a tool such as reporting services. Even so it is pretty handy to have the exact view the user sees on the page returned to them in an excel format.

If your domain objects are setup to be lazily loaded, the query will only return proxy objects, which should not be memory consuming. You can then load the objects in batch (say 16) and then evict.