A very important task in just about any web application that you will build is data access. There is alot of opinions on how this is done the best way.  You can use inline sql statements in your code, you can use stored procedures or you can generate your sql using a OR Mapper. From the developers that use sprocs you will ofter hear arguments about preformance and security, while you will here arguments about managable code from the OR/M fans. I think that, depending on your architecture, every bit of code has it´s own place and that different concerns shouldn´t be mixed up. That is why I choose to use a OR Mapper when I start a new web application.

The thing with stored procedures is that they can often grow pretty big and handle alot of things. You might end up with a few "if" statements and alot of parameters. And why on earth would you force your database to handle your business logic? That is what the application does best. Why write business rules in  sql when you might just as well do it in a strongly typed fashion, using c# (or the language you prefere). A database, in my opinion, should be there to store your data, and nothing else.

So if we remove all the business logic from our sprocs and let them be responsible for simple CRUD operations only, then we are good. Right? Well, in my opinion, we are not there quite yet. We do now have a bunch of sprocs with simple CRUD statements that we need to manage. This can, as many of you might know, be one hell of a task. When you have a big database with lots of sprocs that contain about 2 rows of raw sql each and you need to add a field to a table that you forgott when you started the development. Imagine the headache you´d get when you realize that you need to change all sprocs accessing that table and add the appropriate parameters to the command that executes the sprocs. And all this just to add a fild to a excisting table. Simple CRUD operations is where a OR Mapper realy shines. That is the primary reason why developers choose to use them.

So what about the security and preformance then? Well, the chance of beeing a victime for a sql injection attack is the same for a sproc and a parameterized query (wich is what any good OR/M will use) is the same. You do not suffer a bigger risk for such a attack just because you choose to go with NHibernate (or any other OR/M) instead of sprocs. The only security gain that I can see using sprocs over generated sql is that you can control the database access so that you can allow the application to execute sprocs but not access the tables themselves. But in most cases you will trust your application to access your tables. There is, of course, times when you dont trust the application 100% and you would rather expose a api for the application(s). In those cases I would rather build a service thatprovides this API. Then you will have a strongly typed API that your applications can access in a secure way.

It is true that the preformance of a generated sql query can´t be better then in a sproc. But it can be equaly good. I hear many arguments like: "sprocs are pre-compiled sql queries that will run faster". That is false arguments as it is acctually not pre-compiled (in mssql server) at all. Sql server will, however, precalculate a execution plan for the query that will make it run a bit faster, but it will do the same for a query sent to the database. This means that you can get equal preformance with sprocs and generated queries for simple CRUD operations. When a query hits the database, sproc or string based sql, the database will check its cache and see if this query has been run before. If it has, the database will reuse the same execution plan as before and the query will run faster.

I feel that many developers, including myself, often focus on the wrong thing when trying to optimice a application. Instead of finding the real bottle neck we are working hours on optimicing the wrong part of our application. If you have a webforms application with a ton of viewstate. Then you should try to minimize that before going to your DAL and optimicing your queries. There is, of course, times when you need to work on that as well. But most of the time that is only one or two realy complex queries. In scenarious like that, I would probably write a sproc for that specific situation and use my OR Mapper to call it. But as long as we are talking simple CRUD operations I feel that the extra time it takes to write all those sprocs would be alot better spent on optimizing other parts of the application as the preformance of the modern OR Mappers is very high.