Stored procedures are bad, they're often slow and approximately as efficient as ordinary client side code.
[The speedup is usually due to the way the client and stored procedure interface is designed and the way transactions are written as short, focused bursts of SQL.]
Stored procedures are one of the worst places to put code. It breaks your application into two languages and platforms according to rules that are often random.
[This question will be downvoted to have a score of about -30 because many, many people feel that stored procedures have magical powers and must be used in spite of the problems they cause.]
Moving all the stored procedure code to the client will make things much easier for everyone.
You'll still have to update the schema and ORM model from time to time. However, schema changes are isolated from ORM changes, allowing some independence between applications and database schema.
You will be able to test, fix, maintain, understand and adapt all those stored procedures as you rewrite them. Your app will run about the same and become much less fragile because you're no longer breaking into two different technologies.
ORM's are not magic, and good database design skills are absolutely essential to making it work.
Also, programs with a lot of client SQL can become slow because of poor thinking about transaction boundaries. One of the reasons stored procedures appear to be fast is that stored procedures force very, very careful design of transactions.
ORM's don't magically force careful transaction design. Transaction design still has to be done just as carefully as it was when writing stored procedures.
Benefits:
- Similar performance to a raw SqlCommand with DataReader and parsing.
- No need to roll your own conversion layer for the DataReader.
That's pretty much it, to be honest. You've got a very lightweight wrapper to your sql connections that will do the object conversion for you. You can, obviously, fine-tune the queries without having to deal with any autogenerated SQL.
Cons:
- Not even slightly typesafe. If you make a typo in the SQL your CI server is not going to catch it, you'll have to hope it's caught during automated UI or functional testing.
- A pain to maintain. You've got a bunch of inline SQL statements that do various queries that have no strong ties to the DB architecture. This can quite easily lead to queries that get "left behind" when the underlying DB structure changes, which, again, you will not see at build time.
They have their place, and they're a very effective tool that can take away some of the "donkey work" from developers when interacting with the DB, but in reality they simply cannot take the place of a full ORM in any large-scale system for queries that are not performance-critical, simply due to the increased maintenance cost.
If you are struggling with performance on DB queries I'd suggest that it would be better to use these mapping frameworks with Stored Procedures only, in order to get a compile-time indication of whether your SQL is valid (plus the additional performance benefits).
Best Answer
ORMs(Object-relational mapping) are not mutually exclusive with Stored Procedures. Most ORMs can utilize stored procedures. Most ORMs generate Stored Procedures if you so choose. So it the issue is not either or.
ORMs may generate unacceptable SQL (in terms of performance) and you may sometimes want to override that SQL with hand-crafted SQL. One of the ways to accomplish this is by using SPs(stored procedures).
In DotNet, Don't use stored procedures if:
If you are not familiar with stored procedures (not your case, but included for completeness).
If you don't want to introduce a layer of complexity and versifying to your project.
You are creating an application that should work with different databases or that would have to be replicated across several database servers (this last restriction may apply for some databases only).
Note that triggers are not to be compared with ORMs. Triggers do functions that are better not be in your application code (such as logging or synchronizing data across databases).
Some people prefer the use of Stored Procedures over SQL in code for different reasons such as security (for example to prevent SQL injection) and for their claimed speed. However, this is somewhat debatable and needs detailed discussion.
If your ORM can't generate Stored Procedures, and you have to write a large system, then you need to weight of the extra hand coding based on your case.