Hi,

I am new to this. I have a simple question. Would NHibernate be recommended over SQL/ADO.NET where the number of records can exceed well over 10 million? Please provide some documentation or reference that I can look up as well.

Thank you for your time.

Kashif Pervaiz Butt

Hi Kashif,

The difference between using NHibernate vs. traditional SQL/ADO.Net isn't really a question about performance, but about different architectural decisions and for some people personal preference.

Martin Fowler's Patterns of Application Architecture describes both of these patterns:
Domain Model http://martinfowler.com/eaaCatalog/domainModel.html - supported by persistence frameworks such as NHibernate
Table Module http://martinfowler.com/eaaCatalog/tableModule.html - supported by ADO.Net in the .Net space.

Regardless which method you choose, you will have issues dealing with 10 million + records.

People who typically grew up in the Microsoft space favor the Table Module pattern since most of the the tools provided by Microsoft were more data-centric.

The majority of people who grew up in the Java space tend to favor the domain model because there are a number of tools available to them that support it.

I've used both the Domain Model and persistence frameworks (TopLink for Java and NHibernate) and ADO.Net very successfully.

My preference is to use the Domain Model with persistence frameworks as I prefer to focus on the behavior of the system instead of the data.

I have found that working with ADO.net and the table module architecture, a larger percentage of development time is spent on managing the database queries/stored procedures and/or whatever method you are using to interact with the database.

Take the example of adding a new column to a table.

SQL/ADO.Net
You would need to change the select, insert, update queries and/or stored procedures, plus the strongly typed data set and the table ddl using.

NHibernate
you would update your object that is persisted, the table ddl and your mapping file.

Adding a new persistence object is also much faster using the domain model and a persistence framework as is refactoring existing persistent objects.

Using a Domain Model with persistence frameworks also means that the developer can spend more time in the language of choice instead of having to switch between SQL and vb.Net/c#.net.

I am an ex-dba and have found that using persistence frameworks you can achieve very good database performance with very large sets of data as effectively as you can using SQL/ADO.Net without giving up too much control to the persistence framework.

I have also found that NHibernate does an excellent job of generating SQL that performs well under a large volume of data.

This is my humble opinion...

-Greg

Thanks for the reference. I will definitely go over these as I am trying to introduce NHibernate Framework. The data is used for reporting purposes and I would definitely like to have the team move away from structured querying. Yet another question arises, how LINQ and NHibernate differ? Enterprise Solution vs. Open Source? Any ideas and thoughts would be greatly appreciated.

Kashif Pervaiz Butt[/quote]

Hi,

Linq is not an persistance framework. linq it means language integrated query.

There is a another solution provided by microsoft. ADO.Net Entity Framework as a persistance framework.



[/quote]

Thanks for your reply. .NET Entity Framework might be the way I go. NHibernate has been great. Thanks all,

Kashif

Linq to Sql is the entry level persistence framework, which is what I assume kashifpbutt was referring to. Entity Framework and Ling to Entity is the more complex offering.

I have been using both NHibernate and Ling to Sql in the last few weeks and have found them both highly usable. From the work I have done NHibernate is the clear winner, but the learning curve has been too high for me to feel comfortable implementing it in the current Web based solution. I am currently using Ling to Sql.

The major benefit of using Linq to Sql right now is the speed with which you can generate your mapping and classes from an existing database. With the visual designer surface in VS2008 it takes a few seconds and your away. The major drawback is that it currently only supports SQL Server.

To see Linq to Sql in action I would point you at Rob Conways MVC Storefront screencasts.

NHibernate supports multiple databases. While I was helping with the design and testing on the Guid DataType implementation for Oracle for the next NHibernate release I built a simple test application using NHibernate and successfully ran it against all of the supported databases (except DB2 which I couldn't get hold of) just by changing two lines in the hibernate.cfg.xml file. My test code then connected to the new database, built the tables in each databases native language, loaded some sample records and performed CRUD ops against them. Again, all with changing just two lines of config in an XML. I can't stress how cool it was to see that.

I would point you to the Summer of NHibernate screencasts to get started quickly.

Both excellent solutions, both worth exploring.

Al