32

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.

12
  • 8
    Sounds like you have more problems than simply convincing the team to use ORMs. Seems to me that your team is not aware of some good development practices (i.e. design patterns, unit testing). These are more important issues you need to tackle.
    – Bernard
    May 11, 2011 at 14:53
  • 6
    Ironically, I think in about 5 years of development I've only met maybe a handful of people/teams that were aware of things like design patterns and unit testing; usually I'm the only guy in the company who knows about those things. May 11, 2011 at 14:57
  • 3
    @Wayne M: I find that a bit disturbing, but I am also not surprised by this.
    – Bernard
    May 11, 2011 at 15:14
  • 2
    I've found it very... disheartening. It's strange when you suggest something and get a "deer in headlights" look that indicates the other person hasn't the vaguest idea what you're talking about or why anyone would ever consider doing that. I've had that happen quite a few times in the past. May 11, 2011 at 15:27
  • 2
    I'm a big fan of Stored Procedure, so my comment is biased, but I completely disagree with the whole premise. You like ORM and you want to use this that's fine. However the rest of the team is fine with Stored procs. Why force them to what you like?
    – Darknight
    May 11, 2011 at 15:54

7 Answers 7

49

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.

9
  • 20
    +1 because stored procedures are a total pain to work with
    – Gary
    May 17, 2011 at 15:54
  • 5
    :'( I have no problem with stored procedures. It's just another abstraction layer to me. Mar 7, 2013 at 7:36
  • 2
    My favorite feature of most ORM's is how they make your data access code run 15 times slower. Nov 5, 2013 at 5:18
  • 4
    If we create SP then database engine store it as compiled form and it creates execution path so that it will execute as fastest as possible. But ORM send SQL every time which need to be compiled and run by database engine. I think it will be slower to use an ORM instead of Stored Procedure.
    – Arnab
    Jan 25, 2014 at 3:52
  • 5
    Funny. We switched back from an ORM to stored procedures just because of.. SPEED. Not the amount of time we need for programming, but the time an ORM needs for things like materialize objects, looking up an object, update on different clients. SP where a hell lot faster. One example: reading 30.000 objects from DB with a new modern ORM needs... oh well. timeout after 2 Minutes. Calling the stored procedure and getting the result - 2 seconds. Yes - there exist many tricks like paging to reduce callign to much from a DB - but big difference if they are just that an ORM can be used or to
    – Offler
    Apr 28, 2015 at 11:11
20

Stored procedures are good, they're fast and very efficient and are the ideal place to put your data-related code. Moving all that code to the client will make things slightly easier for you as a client developer (slightly, as you'll still have to update the schema and ORM model when commit changes them) but you will lose all that existing code, and make your app slower and probably more fragile considering the loss of all those sql skills.

I wonder if the DBAs are sitting there saying "oh, every commit, I have to pull down the client all over again, we should move all the code into DB forms instead".

In your case, you should be able to replace the existing custom ORM (ie your funny classes) with someone else's without any loss except changes to the way your code-behind code is written. You can keep the SPs too as most (all?) ORMs will happily call them. So, I'd recommend replacing those "Foo" classes with an ORM and go from there. I would not recommend replacing your SPs.

PS. It appears you have a lot of common code in the code-behind classes, that makes them a design pattern in themselves. what do you think a design pattern is in the first place! (ok, it might not be the best, or even a good one, but it's still a DP)

Edit: and now with Dapper, any reason to avoid sprocs over a heavyweight ORM is gone.

7
  • 3
    +1, the obvious first step is to move to an ORM and use that to map the existing stored procedures' results to objects. Get rid of all of that ds.Tables[0].Rows[0]["FooName"].ToString() crap. Manager loves stored procedures? He gets to keep them. But it would be physically impossible to argue that moving all that repetitious boilerplate code into something generated by, say, LINQ to SQL, was a bad thing. May 11, 2011 at 22:56
  • 12
    Well I can't believe how much "wrong" there's in your post. Your comparison with a DBA whining about having to pull the code is inappropriate and completely senseless. First of all, the database is a SERVICE it's meant to store and retrieve data, end of the story. LOGIC as the name implies goes into the Business Logic Layer, which is a part of the API code. App more fragile moving away from sprocs? Are you serious or just trolling? TDD an app with logic in sprocs and tell me how much fun that is. Also ORMs are not meant to be switched. Databases are, since they are just a SERVICE. Jan 25, 2012 at 23:02
  • 6
    I can't really understand why some people think that the database is some kind of holy land where everything is sacred. Think about this. When a third party company exposes you a service, do they give you direct access to their db, or do they mask it behind an API? To use a popular example, take any Google service. You, developer, connect to their public API, you don't even know what database is under it, or if there's a database at all. That's how you design a solid and decoupled software. Databases aren't meant to be accessed directly by applications. Middle tiers are there for that. Jan 25, 2012 at 23:06
  • 5
    @Matteo Mosca: sure, but how does the middleware access the DB... it is a client to the DB. "client" doesn't mean "GUI" or "desktop app". There's plenty of grey areas in application tiers - do you put validation in the GUI or in the server/business logic? It should go in the server to be a clean design, but that will be really poor for performance and user responsiveness. Similarly, you often put some logic in the DB when it makes performance and (in this case) data correctness better.
    – gbjbaanb
    Jan 26, 2012 at 17:28
  • 5
    I'm sorry but I still disagree. The day you have to deploy your application in a different environment, where you don't have the same DB technology you used, you find yourself in a situation where your app cannot run because you don't have all those sprocs on the new database.. and even if you recreate them (which is a total pain) they could be different, because of the differences of SQL dialects, etc. A centralized API with logic and validation, exposed through a standard (like SOAP or REST) solves this problem, and adds testability, versioning and consistency. Jan 26, 2012 at 21:07
13

You seem to be trying to lead your team from one extreme (stored procedures and datasets) to another (a full-blown ORM). I think there are other, more incremental changes you can set about implementing to improve the code quality of your data access layer, which your team might be more willing to accept.

The half-baked active-record implementation code you've posted isn't particularly elegent - I'd recommend researching the Repository Pattern which is easy to understand and implement, and is very popular with .NET developers. This pattern is often associated with ORM's but it's just as easy to create repositories using plain ADO.NET.

As for DataSet's - yuck! Your class libraries will be much easier to work with if you return statically (or even dynamic) typed objects. I believe this illustration explains my opinion of DataSet's better than I could.

Also, you can ditch the stored proc's without jumping to an ORM - there's nothing wrong with using parameterzed SQL. In fact I'd definately favour it over using stored proc's unless you have complex procedures that save on multiple round trips to the server. I too hate it when I open up a legacy database and see an endless list of CRUD procedures.

I'm not discouraging the use of ORM's - I generally use them on most projects I work on. However I can see why there might be plenty of friction in trying to introduce one into this project and your team which to put it kindly, sound like they stopped learning new things about 8 years ago. Having said that I would definately take a look at the new breed of "Micro ORM's" such as Dapper (used to power this site no less) and Massive, both of which are incredibly easy to use and keep you closer to the SQL than a typical ORM does, and which your team might be more willing to accept.

2
  • 2
    I think the issue is just when the app was written there were no real ORMs of note for .NET so it was done the other way (the ASP.NET 1.1 way), and nobody ever thought to do it (or anything else) differently until I joined a few months ago. May 12, 2011 at 11:19
  • 1
    +1 for Dapper. I just started using it on a project and it is extremely easy to implement and use. I'm already a big fan.
    – SLoret
    Jul 18, 2011 at 17:56
4

I'm in a similar situation, actually our hardware, power and politics lean to the database side, so everything goes through a stored procedure. Unfortunately, they are a pain for a coder, especially when it comes to metadata and code generation, as there isn't as rich meta data in stored procedures as tables.

Regardless, you can still write elegant, and clean code using stored procs. I'm currently implementing the Repository pattern myself with all stored procedures. I'd suggest looking at FluentAdo.net for his brilliant idea when mapping from datareader back to your business objects. I took a piece of that idea, and repurposed it for my homegrown solution.

3

JFTR - I'm a lowly PHP developer, but this sounds like a predominantly political issue.

Given the scale of the "rot" propping up the app, then - best practices aside - there would be a significant overhead to root it out. This sounds like it's bordering on rewrite territory.

Can you guarantee that the alternative you suggest would yield benefits to justify the cost to the business? I suspect that the ROI of this venture may be hard to sell to the business. Unless the app is unstable, or you can prove the merit of the overhaul in financial terms - this might prove to be difficult.

Is ORM the only alternative to SPROCS? There are a couple of design patterns between a full blown ORM and vanilla SQL. Perhaps you could start the process by bringing these SPROCS gradually out of the DB into a DBAL. There is the danger of course that this would grow into a homebrew ORM over time - but you'd have got a step closer to the objective.

2

We switched from SP to ORM a few years ago.

In one case we had to update 80 tables. The old estimation model would have estimated 80 hours for this with entlib and SP. We did it in 10 :)

It has given us an 80% reduction in the amount of time we use developing the data access layer.

2
  • 1
    And you couldn't script the table update why?
    – Darknight
    May 12, 2011 at 9:13
  • This was taking a complex object in memory and saving it it a relational database for reporting. We did write a program that did a reflection on the object to create the table structure. May 12, 2011 at 9:19
-1

It sounds more to me that the underlying issue is that your deployments are not working properly and automatic.

It might be easier to set up a continuous build engine which knows how to manipulate the databases as needed after each commit.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.