It's Time To Get Over That Stored Procedure Aversion You Have



  • Why do I need to monkey with an ORM when I can let Postgres elegantly handle this for me with SQL that’s pretty damn simple to understand? Are SELECT statements and parameters really that scary?
    I want to vault every ORM into the heart of the sun or, preferably, go back in time and smash all the computers responsible for their genesis. It’s 2015, let’s wake the fuck up to the power of SQL and our relational systems.

    Taking swings at the failings of ORMs is like shooting fish in a barrel.




  • BINNED

    I think the main complaint most people will sling at stored procedures is "Potrability!"

    But, honestly, how fucking often do you change database engines, and how sure are you that your ORM can deal with it?

    The prime example is getting the ID of last inserted record. Every DB engine has (or doesn't have) it's own way of dealing with this. Does your ORM deal with them all? If not, how much pain will that cause? More or less than changing bits of specific syntax in SQL.

    Not to mention query planners and their quirks. With stored procs or without them, are you sure you won't have to rewrite a single query you / your ORM generates?

    I like the idea of ORM. And, within limited scope, it's great. But I'd rather have the DB do, you know, database stuff. If the results of my convoluted query get mapped by an ORM so be it, but not using stored procedures at all is stupid IMHO.



  • Sounds like a horrible mix up of two completely unrelated things.

    There is ORM, which simply does not work when you actually need any of the database operations. But then, objects don't support relational operations at all, so when you need relational operations hanging on to objects is just plain silly. But it has nothing to do with stored procedures; they are at most implementation detail for ORM.

    And then there are stored procedures. Stored procedures complicate writing the code, because you need to edit code in two places — the stored procedure in the database update script and the code file that uses it. They however allow providing backward compatible interface to older applications. So if I don't need to run different versions of the application, or different applications with different release schedule, against the same database, I prefer slapping the queries into the code close to where they are used. But if I needed the abstraction, that's what they are for. The other option is services, but advantage of stored procedures is that you still have transactions.



  • @Onyx said:

    I think the main complaint most people will sling at stored procedures is "Potrability!"

    Also, version controlling. Fuck CREATE OR REPLACE PACKAGE BODY with a leaky car battery.

    Also also, debugging is a pain.

    And even if it wasn't for that, I'm still averse to putting too much logic into the database. Database is for storing and presenting data, not for calculations. Unless there's a performance gain justifying moving the logic there, I'd rather see anything more advanced than a calculated/aggregated view be shifted where it belongs - to the business logic layer of your code.


  • FoxDev

    @Maciejasjmj said:

    Database is for storing and presenting data, not for calculations.

    Some calculations do benefit from being in the DB, like sums and averages. Other than that though, calculations should be in the business logic layer.



  • I often use sprocs, but mostly when I need to be efficient or wrap a single atomic operation into a transaction. For example, moving record from one table to another. This always needs to happen safely and without creating duplicates, so database feels like the right place for that code.

    His example with user management is, IMO, taking it too far. Extended SQL languages (plpgsql, tsql) are all ugly and archaic, and the code is a pain to test, debug and maintain. Also, you are limited to the kind of operations db can handle, which all revolve around shuffling data internally. The author himself pointed out he needs rails code to handle emails. And what if you suddenly need to ping an outside service or read/write stuff from a disk or a different DB? Rails code can be extended with stuff like that. His postgres system, not so much.

    ORMs are getting a lot of hate. And it's true they are pain in the ass if you need more flexibility or performance. But that's the thing. You don't have to use ORM for that stuff.

    It's been a while, but I had good experiences mixing Entity Framework with stored procedures. EF for quickly shitting out CRUD code, sprocs for performance-sensitive custom stuff. It worked nicely. Certainly better than trying to cram everything into either ORM or SQL would have.



  • ORMs aren't there to protect ‘rockstar developers’ from boring database stuff, rather, their purpose is to protect the data from rockstar developers.



  • I don't see how the problems with ORM are fixed with sprocs. You don't like ORM's then use a noSQL database which handles best your data models (documents, graphs, objects).

    And as some said, sprocs are a PITA to debug, to maintain and to evolve. Also, now and then you might find someone proficient with SQL but those are in the realm of DBA's and not on development, so there's a gap. And asking a RoR developer to also be an SQL guru is not effective unless you manage it well.

    Why you can't ask your developer to be an SQL guru? Because it's not something they are 100% of their time doing. In general, SQL stuff is something you learn enough to accomplish a certain task and then forget about it. It's also why you don't want your DBA's to be doing any frontend development. They'll probably hack something in jQuery that'll look like an SQL EXECUTE and end up here.


  • Discourse touched me in a no-no place

    @Eldelshell said:

    I don't see how the problems with ORM are fixed with sprocs.

    They're not. The real problems with ORM are that the majority of its proponents try to make the database fit the code, instead of making the code fit the database. Hilarity ensues.


  • FoxDev

    @dkf said:

    The real problems with ORM are that the majority of its proponents try to make the database fit the code

    Only made worse by Code-First ORM.


  • ♿ (Parody)

    @Groaner said:

    Taking swings at the failings of ORMs is like shooting fish in a barrel.

    From reading his ORM rant, I can only assume that Rails and ActiveRecord and Entity Framework are retarded. This guy didn't address any of my issues with ORM, and seems to be making stuff up about how they make me screw over my data model.

    I use ORM extensively. Also SQL. I have some stored procedures and functions and even some backed by Java code.

    The main thing that stored procedures do for me is that they make my life more difficult from a configuration control / administrivia / procedure perspective. So there better be some major gain to adding them.


  • ♿ (Parody)

    @cartman82 said:

    ORMs are getting a lot of hate. And it's true they are pain in the ass if you need more flexibility or performance. But that's the thing. You don't have to use ORM for that stuff.

    Exactly. And it's a lot easier to drop to SQL when you really need it and let the ORM handle the drudgery the rest of the time.


  • I survived the hour long Uno hand

    @boomzilla said:

    ActiveRecord

    I'm not sure I get the point of this ActiveRecord shit. I mean...

    $query = $this->db->select('FName, LName, chars.id')->where(array('LogID' => $log['id']))->join('chars', 'chars.id = logchars.charID')->get('logchars');

    actually took me longer to write than the SQL it (hopefully) generated....


  • ♿ (Parody)

    @Yamikuronue said:

    I'm not sure I get the point of this ActiveRecord shit. I mean...

    Well, if the stuff you posted is supposed to be part of an ORM, then I can sort of see where he's coming from.


  • I survived the hour long Uno hand

    It's from a project where I was mucking about with CodeIgniter's ActiveRecord paradigm: http://www.codeigniter.com/user_guide/database/active_record.html



  • Ouch, I think the RWTF there is definitely CodeIgniter.

    I think one of the main reasons to use an ORM is protect the DB from shitty developers who write queries full of injection vulnerabilities. Stored procedures don't protect against SQL injection, parameterised queries do, and that's something that the good ORMs can do.

    Having said that, yeah, it can take a while longer sometimes to write queries and to think about how to structure them in your ORM of choice when you know how it should be done in SQL.


  • FoxDev

    @Ashley_Sheridan said:

    Stored procedures don't protect against SQL injection, parameterised queries do

    If you aren't calling stored procedures through parameterised means (and any decent ORM/DAL will allow you to do so), you're Doing It Unbelievably Wrong You Retard™ 😜



  • Funny that a few years back I recall Rob Conery being on the ORM bandwagon, in fact he created Subsonic and Massive.

    That said though, I agree to a point. I think Stored Procedures have their place, but like anything else it's easy to abuse and you end up with hundreds or thousands by clueless people who only know SQL and nothing else.

    I'm more a fan of micro-ORMs anyways, Dapper, Massive, PetaPoco etc. that are basically a thin wrapper around SQL, not some convoluted heavy behemoth like NHibernate (which I still like) or Entity Framework that tries to abstract out everything.


  • I survived the hour long Uno hand

    At my company we mandate procs for everything... but we also have a SQL development team whose job is to manage those procs and the underlying views and tables. So we treat our database layer like a full layer of our apps, and we have a lightweight service layer above it. It's not necessarily the best plan, but it seems harmless enough, and it stops our web developers from screwing up their SQL from lack of experience.



  • In my experience, people who mandate procs for everything tend to only think in data terms. I hate that extreme, personally. I've worked at too many clueless places that were all herp derp we only use stored procedures.


  • I survived the hour long Uno hand

    The way I understand it, proc support in Coldfusion was much better than the query support capabilities that lead to inline SQL queries being mismanaged all over the place.



  • Wouldn't know, never used Coldfusion. I tend to find that sprocs are only good when you're aggregating a lot of things or doing some weird groupings in the code. But having them for general select/update clauses is IMHO pretty stupid, they don't benefit anything there.



  • @RaceProUK said:

    If you aren't calling stored procedures through parameterised means (and any decent ORM/DAL will allow you to do so), you're Doing It Unbelievably Wrong You Retard™

    If you aren't calling all database operations through parameterised means (and any decent ORM/DAL will allow you to do so), you're Doing It Unbelievably Wrong You Retard™. That's why the problem isn't solved by stored procedures, it's solved by not Doing It Unbelievably Wrong You Retard™.



  • The biggest problem with stored procedures is that they make it difficult to superimpose an object interface over them. In order to do so, all retrieval procs need to return all the attributes for an object and no return attributes that cross object types. In order to support denormalized data and server-generated data, modifications have to return the after-state of the object with the same restrictions as above.

    So, this really turns out to be an issue of what type of interface do you want. The stored procedure guys want you to treat the database as the only persistence layer; no caching and no object representation that you expect to remain valid over multiple operations outside the database. They want an RPC based interface where every operation is independent of every other operation.

    The object guys want to be able to retain an object representation and do any number of operations on it. They want to bind it to user interfaces and they want to cache it. The simplest way to make this happen is to use an ORM.


  • I survived the hour long Uno hand

    Honestly, our database is now older and more valuable than any one of our applications. So the database is the primary location for most of the information our data processes, and if we want it in a given format or assembled in a different way, the service layer interprets it and returns the requested data packet. We don't really do much object-orientation in our applications, at least not until recently, and the OO we do use is full of hacks to make it match our existing table structure because our devs have no idea what a sane application would look like >.>

    Things like a JS model with a single attribute called data, where the data is all stored as an object directly translated from the JSON the service returned, which is coupled to the proc's output. Then you call model.getData() and operate on that object.

    So yeah, I don't think procs are the hill I want to die on as far as quality is concerned XD


  • FoxDev

    @Yamikuronue said:

    Things like a JS model with a single attribute called data, where the data is all stored as an object directly translated from the JSON the service returned, which is coupled to the proc's output. Then you call model.getData() and operate on that object.

    😷


  • FoxDev

    @Yamikuronue said:

    Then you call model.getData() and operate on that object.



  • Aw god! I just remembered how sprocs are used in JDBC:

    cs = this.con.prepareCall("{call RAISE_PRICE(?,?,?)}");
    cs.setString(1, coffeeNameArg);
    cs.setFloat(2, maximumPercentageArg);
    cs.registerOutParameter(3, Types.NUMERIC);
    cs.setFloat(3, newPriceArg);
    cs.execute();
    

    This alone makes me want to go back an write some Hibernate XML.

    @boomzilla said:

    and even some backed by Java code.

    More memories!!! GO AWAY!!!


  • ♿ (Parody)

    @Eldelshell said:

    This alone makes me want to go back an write some Hibernate XML.

    I've used Hibernate for nearly a decade and never written any XML. It's annotations all the way down.


  • ♿ (Parody)

    Plus, he says stuff like this:

    If I was to use an ORM, this would be a lot of writes. If I was careless (or using Rails – take your pick) I wouldn’t put this into a transaction, which it very much should be.

    Which tells me that he's probably gotten a sign from Bill Engvall.



  • "To determine the best road to travel one must first determine where one wants to go, and/or what is important about the journey"..... Selecting an inappropriate approach and then shoehorning it is TRWTF.



  • @boomzilla said:

    and never written any XML

    That's the joke :facepalm:


  • ♿ (Parody)

    @Eldelshell said:

    That's the joke :facepalm:

    Oh. 'Cause a lot of the documentation talked about that, and you see lots of talk about it on SO and the like, so it sounded plausible.



  • No, but because annotations were introduced in Java 1.5 and Hibernate and Spring used XML files before that: persistence.xml



  • @Yamikuronue said:

    At my company we mandate procs for everything... but we also have a SQL development team whose job is to manage those procs and the underlying views and tables. So we treat our database layer like a full layer of our apps, and we have a lightweight service layer above it. It's not necessarily the best plan, but it seems harmless enough, and it stops our web developers from screwing up their SQL from lack of experience.

    Policies like this complicate my work.

    I know because it's the policy for where I work too. However, in my case, it's 1 DB developer for a team of 8 Java/.NET developers and also the contractor writing an application for modifying Oracle Spatial data.

    Needless to say, if I want anything to get done in a reasonable timeframe, I end up writing the PL/SQL code myself.

    Oh, and for extra fun, on the Oracle side we also have everything returned as Oracle types... because heaven forbid we deal with normal data.



  • @powerlord said:

    Needless to say, if I want anything to get done in a reasonable timeframe, I end up writing the PL/SQL code myself.

    I don't know what takes longer, to have the DB team write the thing or they reviewing the thing you just made before putting it on the DB so you can use it.



  • The problem is that people get rubberbanding from SQL to source and back.
    ORM isn't the first attempt to make code do relational stuff after constructing data from tons of table gets, and it won't be the last. And I don't know how many times I've looked at a solution that put 90% of its business tier logic in SQL.

    It's quite simple people.

    SQL for getting the data and putting the data, in whatever form you need.
    SQL for performing baked in operations that will occur every time you call an update, write, read, etc.

    But business logic doesn't belong in an sproc.

    Other than a read or write operation, IMO, doesn't belong in sql.
    If you need to pass your write sproc a variable other than the data you are writing, like an extra flag to choose an optional operations, then it's not an sproc.
    If I can't fit my writeTable stored procedure on my vertical oriented monitor, and read it. It's too much sql.


  • FoxDev

    @xaade said:

    Business logic doesn't belong in an sproc

    That should be painted in dayglo pink on a black background in letters 18" high, and erected in every development office worldwide.

    We're gonna need a lot of paint…



  • @xaade said:

    If I can't fit my writeTable stored procedure on my vertical oriented monitor, and read it. It's too much sql.

    If all of your sprocs are 10 lines of SELECT/INSERT/UPDATE statements, then why bother having them? ORMs are more than capable of getting simple operations correct.

    That's why the divide really is that sproc people don't agree with you (and me). They think that business logic does belong in the database.



  • @xaade said:

    an sproc

    This makes me curious how you pronounce sproc.


  • kills Dumbledore

    Use each layer to its strengths - Set based business logic -> sproc, object based business logic -> app code


  • FoxDev

    @Jaime said:

    This makes me curious how you pronounce sproc.

    I'm guessing s-prok; personally, I prefer sprok, like sprocket.



  • @Jaloopa said:

    Use each layer to its strengths - Set based business logic -> sproc, object based business logic -> app code

    I dare you to do proper cache invalidation after calling a stored procedure that modified an unknown number of entities.



  • @Jaime said:

    If all of your sprocs are 10 lines of SELECT/INSERT/UPDATE statements, then why bother having them?

    @Jaloopa said:

    Use each layer to its strengths - Set based business logic -> sproc

    That's why.

    If you want to cascade update, it's better to do that in an sproc.

    For example.

    You have two tables, one is a table of hourly values, one is a table of daily values.

    Your business logic can just push the hourly values, then the updateValues can roll up the hourly values.



  • @xaade said:

    You have two tables, one is a table of hourly values, one is a table of daily values.

    Your business logic can just push the hourly values, then the updateValues can roll up the hourly values.

    Trigger. Or... better yet... don't store denormalized data.


  • FoxDev

    @xaade said:

    You have two tables, one is a table of hourly values, one is a table of daily values.

    Your business logic can just push the hourly values, then the updateValues can roll up the hourly values.


    OK, that's business logic, but only just; personally, I'd say it's more denormalisation or pre-calculation. For performance reasons, of course.


  • FoxDev

    @Jaime said:

    don't store denormalized data

    Sometimes you need to to get the performance. It's shit, I know, but sometimes you have no other choice.



  • @RaceProUK said:

    Sometimes you need to to get the performance. It's shit, I know, but sometimes you have no other choice.

    You still don't need to change how that data is interacted with. The reason a trigger is a better answer than a sproc is that a trigger makes it so you can rely on that denormalized data. If you use a sproc to keep the data in sync, then you have to concern yourself with validating every code path that interacts with the hourly data.



  • That would be a great idea, but we have TONS of historical data.... that's all scheduled for archiving.

    Trigger or in the sproc itself doesn't matter. The fact here is that SQL performs this task better.

    Rolling up values, gives us a long-term view of our data that isn't archived.


Log in to reply