I work at a company that only uses stored procedures for all data access, which makes it very annoying to keep our local databases in sync as every commit we have to run new procs. I have used some basic ORMs in the past and I find the experience much better and cleaner. I'd like to suggest to the development manager and rest of the team that we look into using an ORM Of some kind for future development (the rest of the team are only familiar with stored procedures and have never used anything else). The current architecture is .NET 3.5 written like .NET 1.1, with "god classes" that use a strange implementation of ActiveRecord and return untyped DataSets which are looped over in code-behind files – the classes work something like this:
class Foo {
public bool LoadFoo() {
bool blnResult = false;
if (this.FooID == 0) {
throw new Exception("FooID must be set before calling this method.");
}
DataSet ds = // ... call to Sproc
if (ds.Tables[0].Rows.Count > 0) {
foo.FooName = ds.Tables[0].Rows[0]["FooName"].ToString();
// other properties set
blnResult = true;
}
return blnResult;
}
}
// Consumer
Foo foo = new Foo();
foo.FooID = 1234;
foo.LoadFoo();
// do stuff with foo...
There is pretty much no application of any design patterns. There are no tests whatsoever (nobody else knows how to write unit tests, and testing is done through manually loading up the website and poking around). Looking through our database we have: 199 tables, 13 views, a whopping 926 stored procedures and 93 functions. About 30 or so tables are used for batch jobs or external things, the remainder are used in our core application.
Is it even worth pursuing a different approach in this scenario? I'm talking about moving forward only since we aren't allowed to refactor the existing code since "it works" so we cannot change the existing classes to use an ORM, but I don't know how often we add brand new modules instead of adding to/fixing current modules so I'm not sure if an ORM is the right approach (too much invested in stored procedures and DataSets). If it is the right choice, how should I present the case for using one? Off the top of my head the only benefits I can think of is having cleaner code (although it might not be, since the current architecture isn't built with ORMs in mind so we would basically be jury-rigging ORMs on to future modules but the old ones would still be using the DataSets) and less hassle to have to remember what procedure scripts have been run and which need to be run, etc. but that's it, and I don't know how compelling an argument that would be. Maintainability is another concern but one that nobody except me seems to be concerned about.
Best Answer
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.