Stored Procedures vs ORM

Please compose all posts in Emacs.

Moderators: phlip, Moderators General, Prelates

User avatar
Thesh
Made to Fuck Dinosaurs
Posts: 6568
Joined: Tue Jan 12, 2010 1:55 am UTC
Location: Colorado

Stored Procedures vs ORM

Postby Thesh » Wed Aug 15, 2012 10:59 pm UTC

As far as I can tell, this is a debate that's never occurred on the internet ever.

Stored Procedures are dead, man, let them go. The amount of work it takes to manually write all the stored procedures is not worth the benefit. Either your stored procedures are pure CRUD, and you get absolutely no benefit, or you have a shit ton of logic in them and end up with what is essentially an extra layer of abstraction that often not only makes things slower, but also much more annoying for the application developer who doesn't actually know what the SPs are doing. Sure, you can make large changes to the database, and keep that "API" essentially the same from the application standpoint by only modifying stored procedures (although more often schema changes are dictated by the application in the first place, meaning you have to change both anyway), but isn't that the point of the data layer in the application in the first place?
Summum ius, summa iniuria.

KnightExemplar
Posts: 5494
Joined: Sun Dec 26, 2010 1:58 pm UTC

Re: Stored Procedures vs ORM

Postby KnightExemplar » Thu Aug 16, 2012 2:22 am UTC

If you have multiple applications, potentially written in different languages, then Stored Procedures looks like a better option. Although, some sort of RESTful service built out of ORMs can be an option as well.

If you've got a single application, then you can build a single data-access layer.
First Strike +1/+1 and Indestructible.

webzter_again
Posts: 119
Joined: Sun May 27, 2012 4:37 am UTC

Re: Stored Procedures vs ORM

Postby webzter_again » Thu Aug 16, 2012 1:48 pm UTC

Well there are stored procedures and there are ORMs and then there are ORMs. Purely thinking in the .net space, you've got your traditional ORMs like NHibernate and Entity Framework and then a breed of very lightweight "ORMs" like Massive, Dapper and Simple.Data. (and there are data access things like SisoDB and YesSQL that certainly aren't ORMs but are great greenfield options)

For data access on all of our CRUD screens, we're using Castle Active Record on top of NHibernate. Then, for some slightly more advanced querying, we're using QueryOver or HQL ( SQL may have been used in one case). However, the most critical part of our system is a stored procedure. Frankly, it was just easier to write a few indexed views and a couple hundred line stored proc. In our case, domain events being run through a projection and stored in a denormalized fashion somewhere would have been an even better option but we just didn't have the time to pull it off.

On our newest work here, we're just saving event streams. Those streams are going to a database and then being published out where they're then being projected to various read models and stored to a document database. So, screw stored procs and orms.

webzter_again
Posts: 119
Joined: Sun May 27, 2012 4:37 am UTC

Re: Stored Procedures vs ORM

Postby webzter_again » Wed Aug 22, 2012 9:39 pm UTC

interviewed three people this week... three people use stored procedures because of one or more of the following "its more secure", "it lets me protect against sql injection attacks", or "I don't like raw sql in my code"

User avatar
Thesh
Made to Fuck Dinosaurs
Posts: 6568
Joined: Tue Jan 12, 2010 1:55 am UTC
Location: Colorado

Re: Stored Procedures vs ORM

Postby Thesh » Wed Aug 22, 2012 11:06 pm UTC

At least they didn't say "Stored procedures are precompiled".
Summum ius, summa iniuria.

User avatar
taralluccio
Posts: 1
Joined: Tue Oct 02, 2012 7:24 pm UTC

Re: Stored Procedures vs ORM

Postby taralluccio » Tue Oct 02, 2012 7:50 pm UTC

Stored procedures are great for bulk loading or for long-running, non-interactive (mostly batch) operations. They are not that hard to maintain, and they give you a performance advantage when you need it. I don't mean premature optimization, I mean lowering execution time from a few hours to several minutes.


Return to “Religious Wars”

Who is online

Users browsing this forum: No registered users and 7 guests