When to use Stored Procedures instead of using any ORM with programming logic?

Question

Hi all I wanted to know when I should prefer writing stored procedures over writing programming logic and pulling data using a ORM or something else.

Answer 1

Stored procedures are executed on server side.

This means that processing large amounts of data does not require passing these data over the network connection.

Also, with stored procedures, you can build consistent complicated business logic.

Say, you need to update the account balance each time you insert a transaction, and you need to insert many transactions at once.

Instead of doing this with triggers (which are implemented using inefficient record-by-record approach in many systems), you can pass a table variable or temporary table with the inputs and issue a set-based SQL statement inside the procedure. This will be much more efficient.

Answer 2

I prefer SPs over programming logic mainly for two reasons

• Performance, anything what will reduce result set or can be more effectively done on the server, e.g.:
  • paging
  • filtering
  • ordering (on indexed columns)
• Security -- if someone have got application's access to the database and wants to wipe out your all your records, having to execute Row_Delete for single each of them instead of DELETE FROM Rows already sounds good.

Answer 3

Never unless you identify a performance issue. (largely opinion)

(a Jeff blog post!) http://www.codinghorror.com/blog/2004/10/who-needs-stored-procedures-anyways.html

If you see stored procs as optimizations: http://en.wikipedia.org/wiki/Program_optimization#When_to_optimize

Answer 4

As ever, much of your decision as to which to use will depend on your application and its environment.

There are a couple of schools of thought here, and this debate always arouses strong sentiments on both sides.

The advantanges of Stored Procedures (as well as the large data moving that Quassnoi has mentioned) are that the logic is tied down in the database, and therefore potentially more secure. It is also only ever in one place.

However, there will be others who believe that the place for application logic should be in the application, especially if you are planning to access other types of datebases (for which you will have to write often different SPs).

Another consideration may be the skills of the resources you have to implement your application.

Answer 5

When appropriate.

• complex data validation/checking logic
• avoid several round trips to do one action in the DB
• several clients
• anything that should be set based

You can't say "never" or "always".

There is also the case where the database engine will outlive your client code. I bet there's more DAL or ORM upgrades/refactoring that DB engine upgrades/refactoring going on.

Finally, why can't I encapsulate code in a stored proc? Isn't that a good thing?

Answer 6

The point at which stored procedures become preferable to an ORM is that point at which you have multiple applications talking to the same database. At this point, you want your query logic embedded in one place, rather than once per application. And even here, you might want to prefer a service layer (which can scale horizontally) instead of the database (which only scales vertically).