What is better in terms of performance?

Using a tool like NHibernate?

Execute in-line SQL queries from inside a program and execute them thru ADO.NET?

Have Store Procedures and Views in the DB side and execute them thru ADO.NET from the program?


Thanks

Hi,

Well the performance in this case depends heavily on what your program is going to perform against the database.

Here is simple consideration that you have to keep in mind when you are selection one of the 3 choices that you outlined in your post.

1. With Hibernate (NHibernate or Hibernate for Java) you can achive a high performance when you have to deal with same data many times and those data is not very often updated - e.g. using Hibernate caching so you will not even go to the database, using lazy loading, and etc.

2. Using a direct SQL is also a solution but then the caching, paging (effective data retrival), lazy loading and etc. are all in your plate. Again it depends on what are you planing to execute.

3. Using stored procedures and view are very effective only in case where you are performing many sql queries (operations) and you care only for the final result (not any intermediate results) in you program. This will increase the performance since it will not involve too many traffic between you database and your application.

In my opinion you have to consider using also a combination of these - e.g. you can combine Hibernate with stored procedures or even with navite query when the Hibernate HQL doens't support some very advanced features on you database - like recursive queries and etc.

There are so much that we can write about the performance but the bottom line is that it is really up to the application database logic that needs to be performed, as well as the database schema creation and etc.

Not sure if you've seen these already re performance.

http://www.hibernate.org/157.html
http://www.hibernate.org/15.html

Regarding stored procs vs SQL. Search the Web and you'll get quite a few opinions from Frans Bouma (author of llblgen pro) on this topic. Here's one:
http://weblogs.asp.net/fbouma/archive/2 ... /7050.aspx