Why should I use SPROCs?


  • ♿ (Parody)

    @accalia said in Can't use a view--should I just directly join tables in code or use a stored procedure?:

    it's more annoyance in the short term but a fucking lifesaver in the long term.

    It seems like mostly an annoyance to me. What's the lifesaving that's going on? I have some stored procedures for raisins and they're much more of a PITA than having my queries in the code. Especially from a configuration management perspective.

    And, yes, before someone pipes in, everything defining that sort of thing is in version control.



  • @boomzilla You should use them when a database has multiple clients and you need more fine-grained permissions control. Clients should have access to only the sprocs required to do their jobs.

    Other than that, I used to be a big rah rah SPROC fan and I've kind of cooled on that in the last 5 years.


  • ♿ (Parody)

    @blakeyrat Ah, the permissions thing makes sense. I don't work on a system that needs anything like that, so it hadn't occurred to me.



  • @boomzilla
    I like the "black box" effect of the SPROC in terms of being able to separate application development from (most elements of) performance tuning. Your application developer can hack together whatever SQL query gives him the result columns and filter criteria he wants, drop it into an SPROC, and leave it. And then when it gets to production and "real world" data means SQL doesn't perform efficiently enough for the original query, a performance tuner can come through and adjust the SPROC without having to make any application changes - as long as you keep the same input and output schema, and inputs A continue to produce results B, it doesn't matter what you do inside the SPROC to get there.

    Obviously, if you're in a situation where your application developers are generating properly tuned SQL to begin with, and/or your real world data never gets so much bigger than the development data set the procedure was written for, there's not much benefit there. Or if the application developer is the one that has to go back and fix his crappy SQL, he can probably make changes all the way through the stack (though there's still some additional risk in making both SQL and application changes, if you have to do that). But if you're separating job responsibilities enough that junior developers are writing the initial code and then a SQL specialist SQL-golfs the resulting monstrosities that need to be tuned, then the block box of the sproc gives the SQL specialist room to work in.



  • @boomzilla Well I believe at this point it's safe to say any decent RDBMS will have table-level permissions, so even that reason might be obsolete. That said, you could still have permissions that table-level isn't fine-grained enough to implement-- for example, you can get the results of that SELECT if your WHERE clause includes clientX or clientY but not if it includes clientZ. Implementing permissions like that without using SPROC would be somewhere between "insanely difficult" and "impossible".



  • In some cases, you'll get better performance doing calculations or processing inside the database. In particular, if a calculation takes a large amount of data as input but produces only a small amount of data as output, doing it in a stored procedure reduces the bottleneck of transferring data across the network.


  • ♿ (Parody)

    @izzion said in Why should I use SPROCs?:

    a performance tuner can come through and adjust the SPROC

    Yeah...in my world, I am the performance tuner. Separating the code and the SQL just obscures things and adds extra complexity to configuration management for me.


  • ♿ (Parody)

    @dragnslcr said in Why should I use SPROCs?:

    In some cases, you'll get better performance doing calculations or processing inside the database. In particular, if a calculation takes a large amount of data as input but produces only a small amount of data as output, doing it in a stored procedure reduces the bottleneck of transferring data across the network.

    Yes, and in some cases like that I do have SPROCs. I'm more interested in the people (and I've seen several around here) who seem to be saying that basically all of their data access is via SPROC, which frankly blows me away.


  • Fake News

    At least with a sane DBMS (MySQL need not apply), a sproc gives you (among other things):

    • A one-stop shop for DB access needs. Yeah, you may only have a desktop app that does SELECT FROM x at a low level, but what if you're now creating a mobile app that needs to do the same thing? Will you be porting all those queries to the mobile app, or creating a REST API that needs copies of those queries, or refactoring it into some DLL/assembly for shared DAL between the desktop and mobile apps? Or you could just write a sproc and be done with it.
    • ORM tools don't always generate the most optimized SQL. Either you need to hammer on the ORM to generate a more-optimized form, or you can just write a sproc and move on.
    • The DBMS will automatically generate a persistent query plan for a sproc, thus giving you an edge on that sproc's performance. You don't get that with an ad-hoc query unless you're running it a lot, and maybe not even then.

  • Java Dev

    @blakeyrat said in Why should I use SPROCs?:

    @boomzilla Well I believe at this point it's safe to say any decent RDBMS will have table-level permissions, so even that reason might be obsolete. That said, you could still have permissions that table-level isn't fine-grained enough to implement-- for example, you can get the results of that SELECT if your WHERE clause includes clientX or clientY but not if it includes clientZ. Implementing permissions like that without using SPROC would be somewhere between "insanely difficult" and "impossible".

    One of our internal applications allows direct DB access; querying directly is allowed but any updates need to be done via sprocs (or via the web interface which I believe is mostly implemented in sprocs).


  • Notification Spam Recipient

    @boomzilla said in Why should I use SPROCs?:

    @dragnslcr said in Why should I use SPROCs?:

    In some cases, you'll get better performance doing calculations or processing inside the database. In particular, if a calculation takes a large amount of data as input but produces only a small amount of data as output, doing it in a stored procedure reduces the bottleneck of transferring data across the network.

    Yes, and in some cases like that I do have SPROCs. I'm more interested in the people (and I've seen several around here) who seem to be saying that basically all of their data access is via SPROC, which frankly blows me away.

    I mix both, depending on the scenario.

    For instance, where I'm getting the same resultset columns but just performing different actions, we have this:

    0_1500660594145_69e977e5-67f4-4be8-8d43-871ab167a84b-image.png

    But, in other places where stuff is quite one-off or otherwise non-common there are raw queries.

    One thing I like about stored procs is that the execution plan is much less likely to get regenerated for each query just because the inputs change (because, you know, different SQL after all), but that's not a big issue if your queries aren't uber complicated.


  • ♿ (Parody)

    @lolwhat said in Why should I use SPROCs?:

    The DBMS will automatically generate a persistent query plan for a sproc, thus giving you an edge on that sproc's performance. You don't get that with an ad-hoc query unless you're running it a lot, and maybe not even then.

    I know that Oracle often does, and I've been bitten by it several times. Most recently last week, from a query that hasn't changed in years and suddenly was timing out due to Oracle deciding to do full table scans on a particular table.


  • Fake News

    @boomzilla ObThatsOracleForYou



  • @boomzilla said in Why should I use SPROCs?:

    @blakeyrat Ah, the permissions thing makes sense. I don't work on a system that needs anything like that, so it hadn't occurred to me.

    I would still do that with a proxy in front of database, or maybe even different replicas.


  • ♿ (Parody)

    @cartman82 said in Why should I use SPROCs?:

    I would still do that with a proxy in front of database, or maybe even different replicas.

    Could you elaborate? In my setup, I have a schema with all of my application's data plus a separate schema for some COTS that we integrate with. The COTS can only do stuff in its schema, my app can do pretty much whatever in both. Additionally, my reporting server logs in under a different user with read only permissions.

    What would a proxy or replicas do for me here?



  • @izzion said in Why should I use SPROCs?:

    @boomzilla
    I like the "black box" effect of the SPROC in terms of being able to separate application development from (most elements of) performance tuning. Your application developer can hack together whatever SQL query gives him the result columns and filter criteria he wants, drop it into an SPROC, and leave it. And then when it gets to production and "real world" data means SQL doesn't perform efficiently enough for the original query, a performance tuner can come through and adjust the SPROC without having to make any application changes - as long as you keep the same input and output schema, and inputs A continue to produce results B, it doesn't matter what you do inside the SPROC to get there.

    Obviously, if you're in a situation where your application developers are generating properly tuned SQL to begin with, and/or your real world data never gets so much bigger than the development data set the procedure was written for, there's not much benefit there. Or if the application developer is the one that has to go back and fix his crappy SQL, he can probably make changes all the way through the stack (though there's still some additional risk in making both SQL and application changes, if you have to do that). But if you're separating job responsibilities enough that junior developers are writing the initial code and then a SQL specialist SQL-golfs the resulting monstrosities that need to be tuned, then the block box of the sproc gives the SQL specialist room to work in.

    These days, if you had performance concerns (which buying hardware can't solve, that's always the first option), your top choices would be to scale horizontally and/or add caching, both of which would involve some app development anyway.

    What you're describing feels like the world of yesteryear, TBH.



  • @cartman82
    I can say with experience that there's a world of difference between "good enough" SQL and "good" SQL. On a recent project, I reduced the CPU load of a running production system by half (from 40% of 8 cores to 20%) simply by tuning a handful of stored procedures. In the case of one database, I reduced the database's daytime load from 1500-2000 seconds of CPU time per hour to 50-150 seconds per hour with those changes.

    A few of the changes were missing indexes, but the bulk of the savings was in re-writing inefficient queries to use proper search terms, or eliminating a UDF and switching it to an inline TVF. None of the changes required any application changes, because everything was boxed in a stored procedure.


  • ♿ (Parody)

    @cartman82 said in Why should I use SPROCs?:

    What you're describing feels like the world of yesteryear, TBH.

    Uh...no. Sometimes you just write queries that don't scale with data. I deal with some pretty complicated queries that sometimes turn out to be real dogs.


  • ♿ (Parody)

    @izzion said in Why should I use SPROCs?:

    eliminating a UDF and switching it to an inline TVF

    Uh, what?

    "Weird Al" Yankovic - UHF (Official HD Video) – 04:04
    — alyankovicVEVO



  • @boomzilla
    (Scalar) User Defined Function. Also known as the David that has slain many a gargantuan SQL Server.



  • @boomzilla said in Why should I use SPROCs?:

    Could you elaborate? In my setup, I have a schema with all of my application's data plus a separate schema for some COTS that we integrate with. The COTS can only do stuff in its schema, my app can do pretty much whatever in both. Additionally, my reporting server logs in under a different user with read only permissions.
    What would a proxy or replicas do for me here?

    I don't know what "COTS" are. But if you had clients that needed a filtered view into some data (which was a suggested use case for sprocs), there are two potential alternatives.

    1. Replicate the vertical slice of data into a separate table / database and give the gimped clients access to that instead your master data.

    2. Have all clients talk to a service instead of a database. The service talks with the database and dishes out vertical slices of data according to authorization of each client (can also do caching etc).

    Both of these have downsides (stale data, speed), and a view would probably fit better for this use case than any of these options. But it's something to consider.


  • ♿ (Parody)

    @izzion said in Why should I use SPROCs?:

    @boomzilla
    (Scalar) User Defined Function. Also known as the David that has slain many a gargantuan SQL Server.

    OK...then what's a TVF?


  • ♿ (Parody)

    @cartman82 said in Why should I use SPROCs?:

    I don't know what "COTS" are.

    @cartman82 said in Why should I use SPROCs?:

    But if you had clients that needed a filtered view into some data (which was a suggested use case for sprocs), there are two potential alternatives.

    Oh, OK, I thought you were saying you'd do those things even if you didn't have that need.



  • @izzion said in Why should I use SPROCs?:

    can say with experience that there's a world of difference between "good enough" SQL and "good" SQL. On a recent project, I reduced the CPU load of a running production system by half (from 40% of 8 cores to 20%) simply by tuning a handful of stored procedures. In the case of one database, I reduced the database's daytime load from 1500-2000 seconds of CPU time per hour to 50-150 seconds per hour with those changes.

    A few of the changes were missing indexes, but the bulk of the savings was in re-writing inefficient queries to use proper search terms, or eliminating a UDF and switching it to an inline TVF. None of the changes required any application changes, because everything was boxed in a stored procedure.

    Uh...no. Sometimes you just write queries that don't scale with data. I deal with some pretty complicated queries that sometimes turn out to be real dogs.

    I get that.

    But have you done the cost/benefit analysis of how much time you spent x your hourly rate VS extending that server with another core for a year (or however long that code will be in production)?

    I myself haven't, but hearing/reading from people who are doing the actual numbers, it seems the conclusion is increasingly "just send it to AWS and let them deal with it, you programmers get back to churning more code".



  • @boomzilla
    Table Valued Function. There's two flavors of that - the Inline TVF, which gets treated like a view that can accept parameters, and the Multi-Statement TVF, which is basically a User Defined Function that returns a table instead of a scalar value.

    The primary difference is that the ONLY thing the inline TVF can do is return a table - you can use CASE statements or whatever within the function to do the calculations you need to do with your parameters, but you cannot declare extra variables to do chain calculation logic.

    From a performance perspective, an inline TVF will behave very similarly to a view (though it has more problems with self-joining -- if you have tables A and B in the TVF, and try to join the TVF to tables A and B in the outer query to select additional columns, things will blow up on you), whereas the multi-statement TVF still has to execute row-by-row like the scalar UDF does and is a parallelism inhibitor and severe performance killer.


  • ♿ (Parody)

    @cartman82 said in Why should I use SPROCs?:

    But have you done the cost/benefit analysis of how much time you spent x your hourly rate VS extending that server with another core for a year (or however long that code will be in production)?

    My biggest query problem is usually I/O. (I know @izzion was talking about CPU, though.)

    @cartman82 said in Why should I use SPROCs?:

    I myself haven't, but hearing/reading from people who are doing the actual numbers, it seems the conclusion is increasingly "just send it to AWS and let them deal with it, you programmers get back to churning more code".

    While we have some flexibility on horizontal scaling, not that much, and our problem isn't usually anything that horizontal scaling will deal with.



  • @boomzilla said in Why should I use SPROCs?:

    Yes, and in some cases like that I do have SPROCs. I'm more interested in the people (and I've seen several around here) who seem to be saying that basically all of their data access is via SPROC, which frankly blows me away.

    If you do need to have SPROC-s, then I can almost see that making sense, just for consistency's sake.

    No one set of special rules for code, the other for SPROC-s. Single interface, single pattern for all DB related stuff.


  • ♿ (Parody)

    @izzion said in Why should I use SPROCs?:

    Table Valued Function

    Ah, yes, OK. Just didn't grok the acronym.



  • @cartman82
    In our case, the core licensing was $7,000, plus annual maintenance of $2,500. Not to count the hardware cost of the cores (which was basically a sunk cost - the VM hosts aren't close to capacity) or the Windows licensing cost (which is less than 10% of the SQL licensing cost).

    So that offsets about 2.5 weeks of my time in the first year, and 1 week every year thereafter.

    Also, though, the performance benefit was real time as well - an order process that was taking nearly 20 seconds for our automated monitoring script to step through is now down to 10-12; reports are running faster, and one CSV export process that had to be artifically limited to 10,000 rows to return within 30 seconds (most of the time) can now do over 50,000 rows in 6 seconds flat.

    "Throwing hardware" at the problem is still, in my experience, a very sub-optimal solution, and only going to kick the can for a few months, compared to actually fixing the queries to handle the size of the data you're working with. Even more so in SQL "big data" land than on the application tier side.



  • @lolwhat said in Why should I use SPROCs?:

    The DBMS will automatically generate a persistent query plan for a sproc, thus giving you an edge on that sproc's performance. You don't get that with an ad-hoc query unless you're running it a lot, and maybe not even then.

    (Not really true in MS SQL.)



  • @boomzilla
    Yeah, we bulldozed at least as much I/O as we did CPU time as well (the CPU time was because SQL was reading shit-tons of data it didn't need to find the data it wanted). But it's harder to turn that into $$$ for the PHBs.



  • @izzion said in Why should I use SPROCs?:

    @cartman82
    In our case, the core licensing was $7,000, plus annual maintenance of $2,500. Not to count the hardware cost of the cores (which was basically a sunk cost - the VM hosts aren't close to capacity) or the Windows licensing cost (which is less than 10% of the SQL licensing cost).
    So that offsets about 2.5 weeks of my time in the first year, and 1 week every year thereafter.

    Also, though, the performance benefit was real time as well - an order process that was taking nearly 20 seconds for our automated monitoring script to step through is now down to 10-12; reports are running faster, and one CSV export process that had to be artifically limited to 10,000 rows to return within 30 seconds (most of the time) can now do over 50,000 rows in 6 seconds flat.

    Ok, that makes sense, with the licensing costs and all.


  • Fake News

    @boomzilla said in Why should I use SPROCs?:

    My biggest query problem is usually I/O.

    I hear ya. One thing that my current employer does is require DB-level compression of tables and indexes, to help some on that front.


Log in to reply