6 ms·

> Use stored procedures For God's sake, please don't

by viach 4y ago

daneel_w 4y ago
I second that. SPs/funcs have this weird tendency to always stay hidden in the fringes and out of sight, easily forgotten when adding new functionality, easily overlooked when making changes elsewhere.
- KingOfCoders 4y ago
  From my experience only if there are dedicated DBAs and you have too many systems running - then you forget one. If you only have server code and the stored procedures in the same repository, with migrations, this problem goes away.
- maerF0x0 4y ago
  I believe that stems from people frequently not including them in version control, or not doing tests.
  - daneel_w 4y ago
    We version-control our SPs/funcs. We have unit tests, we have integration tests.
- simonw 4y ago
  I think stored procedures can be perfectly safe provides you follow these rules: - they live in source control - they are covered by automated tests - they are applied using some form of automatic database migration system (not by someone manually executing SQL against a database somewhere) If you don't have the discipline to do these things then they are likely best avoided.
  - lukeramsden 4y ago
    > If you don't have the discipline to do these things then they are likely best avoided. I'd go further and say you should avoid databases and maybe even persistence entirely if you don't have the discipline to do the above. Sprocs will be the least of your problems otherwise.
  - abledon 4y ago
    > they live in source control so that probably excludes 95% of legacy codebases out there from the 90s,00s
  - tshaddox 4y ago
    Aren’t those also the absolute bare minimum bar for any code in a production system?
    - simonw 4y ago
      Yeah, I think so. But my hunch is that the majority of people who tell you never to use stored procedures have been burned by these techniques not being used for them.
      - daneel_w 4y ago
        Your hunch is off. We version-control DDL/DML/DQL/etc. like any other software.
        simonw 4y ago
        "But my hunch is that the majority of people" - I'm not saying no-one does this, I'm saying I expect a lot of people don't do it.
    - ThePadawan 4y ago
      The realization that database procedures are code, not data, even though they reside on the database (where the data lives) is the difficult part.
      - tshaddox 4y ago
        That’s baffling to me. Who doesn’t realize that that thing which looks and behaves exactly like all other code isn't code?
        simonw 4y ago
        Before the development of decent migration systems it was incredibly common for database structure - including stored procedures - to be treated independently of source code in a repository.
  - daneel_w 4y ago
    We tick all the boxes. Please contain your arrogant presumptions.
    - simonw 4y ago
      I was confused as to why you seemed to be taking offense here, then I realized that you posted the comment I was replying to. For "you" in my comment, please read "one" instead: > I think stored procedures can be perfectly safe provides one follows these rules: > ... > If one doesn't have the discipline to do these things then they are likely best avoided.
- CodeWriter23 4y ago
  > SPs/funcs have this weird tendency to always stay hidden in the fringes and out of sight, easily forgotten when adding new functionality, easily overlooked when making changes elsewhere. This is the classic "carpenter blames his tools for crappy results" argument. Implementation isn't easy.
  - jb3689 4y ago
    Such is the life of picking complex tools
    - CodeWriter23 4y ago
      For some people I suppose
  - daneel_w 4y ago
    It's not. You're just making guesses.
    - CodeWriter23 4y ago
      If the developer doesn't know / doesn't document the project has code embedded in the database, that's on the developer, not the tools. Because the use of any developer tools requires a certain level of competence in order to use them successfully.
      - daneel_w 4y ago
        We version-control all of it. Your assumptions don't matter for reality.
KingOfCoders 4y ago
I thought so for 30 years but changed my opinion recently. I even argued with the author of Redis for some time to add some functionality so we didn't have to write Lua and have another deployment target. Now I do think there is a benefit in stored procedures and triggers (E.g. for audits) if they don't contain too much logic or complexity.
- tstrimple 4y ago
  > if they don't contain too much logic or complexity. I think this is the catch. Most folks who are arguing against SP have been burned by huge complex stored procedures with nested dependencies with deeply intertwined business logic and rules. I completely agree that you shouldn't use a SP in that way. But to help perform maintenance, or to audit, or perform data correction all make sense when kept small and simple.
flowerbreeze 4y ago
I've only given up trying to understand a system once. It was when I was handed over an application that used stored procedures for everything. Including recursive stored procedures... The rest could be figured out, but they were just too much.
- jwmcq 4y ago
  I feel this. Once had something locking up a production SQL Server instance, and it turned out to be a dreadful partially-recursive web of sprocs, views, and TVFs that worked fine until apparently one day the query optimiser decided otherwise. Spent hours tracing what the heck was going on.
officialchicken 4y ago
Why? What is your specific reasoning? Using EXPLAIN and SP's to help fix cache misses, slow queries, poor index performance, etc. is generally considered a good thing. As a side note, I did not realize $diety was concerned about DDL/DML, so thanks for pointing it out. I never really thought about it.
doctor_eval 4y ago
Hard disagree with this attitude but I see it all the time. Stored procedures are much faster than writing logic in some remote server (just by virtue of getting rid of all the round trips), require far less code (no DAOs, entities and all that crap which simply serves to duplicate existing definitions), and have built-in strong consistency checking primitives - which can even be safely delayed until the end of the transaction. And what people do is, they throw all these advantages away because they can’t be bothered working out how to integrate the stored procedure code ergonomically into their workflow. I mean - I even use an IDE (JetBrains) to write pl/pgsql. It’s just another file in my repo. Get to this point and stored procedures are a game changer.
- viach 4y ago
  Like you said "they can’t be bothered working out how to..." That's a fact which is not going to change.
dagss 4y ago
Stored procedures has some advantages (fast to debug/try out a query from your service without copy+paste all the time, etc), but also disadvantages (unreadable git diffs, big bang rollouts on changes) We made this tool to get the best of both worlds: https://github.com/vippsas/sqlcode https://github.com/vippsas/sqlcode
kingboss 4y ago
Yes. Please use stored procedures. Don't listen to this guy.
jmull 4y ago
Your app logic concerned with data executes somewhere, right? So… why exactly would you exclude compute running close to the storage? You can use that minimal latency. Of course, people can create an uncontrolled mess of spaghetti code out of sps/funcs… like they can with any kind of code.

> Use stored procedures For God's sake, please don't

by viach 4y ago

> Use stored procedures For God's sake, please don't

I second that. SPs/funcs have this weird tendency to always stay hidden in the fringes and out of sight, easily forgotten when adding new functionality, easily overlooked when making changes elsewhere.

KingOfCoders 4y ago
From my experience only if there are dedicated DBAs and you have too many systems running - then you forget one. If you only have server code and the stored procedures in the same repository, with migrations, this problem goes away.
maerF0x0 4y ago
I believe that stems from people frequently not including them in version control, or not doing tests.
- daneel_w 4y ago
  We version-control our SPs/funcs. We have unit tests, we have integration tests.
simonw 4y ago
I think stored procedures can be perfectly safe provides you follow these rules: - they live in source control - they are covered by automated tests - they are applied using some form of automatic database migration system (not by someone manually executing SQL against a database somewhere) If you don't have the discipline to do these things then they are likely best avoided.
- lukeramsden 4y ago
  > If you don't have the discipline to do these things then they are likely best avoided. I'd go further and say you should avoid databases and maybe even persistence entirely if you don't have the discipline to do the above. Sprocs will be the least of your problems otherwise.
- abledon 4y ago
  > they live in source control so that probably excludes 95% of legacy codebases out there from the 90s,00s
- tshaddox 4y ago
  Aren’t those also the absolute bare minimum bar for any code in a production system?
  - simonw 4y ago
    Yeah, I think so. But my hunch is that the majority of people who tell you never to use stored procedures have been burned by these techniques not being used for them.
    - daneel_w 4y ago
      Your hunch is off. We version-control DDL/DML/DQL/etc. like any other software.
      - simonw 4y ago
        "But my hunch is that the majority of people" - I'm not saying no-one does this, I'm saying I expect a lot of people don't do it.
  - ThePadawan 4y ago
    The realization that database procedures are code, not data, even though they reside on the database (where the data lives) is the difficult part.
    - tshaddox 4y ago
      That’s baffling to me. Who doesn’t realize that that thing which looks and behaves exactly like all other code isn't code?
      - simonw 4y ago
        Before the development of decent migration systems it was incredibly common for database structure - including stored procedures - to be treated independently of source code in a repository.
- daneel_w 4y ago
  We tick all the boxes. Please contain your arrogant presumptions.
  - simonw 4y ago
    I was confused as to why you seemed to be taking offense here, then I realized that you posted the comment I was replying to. For "you" in my comment, please read "one" instead: > I think stored procedures can be perfectly safe provides one follows these rules: > ... > If one doesn't have the discipline to do these things then they are likely best avoided.
CodeWriter23 4y ago
> SPs/funcs have this weird tendency to always stay hidden in the fringes and out of sight, easily forgotten when adding new functionality, easily overlooked when making changes elsewhere. This is the classic "carpenter blames his tools for crappy results" argument. Implementation isn't easy.
- jb3689 4y ago
  Such is the life of picking complex tools
  - CodeWriter23 4y ago
    For some people I suppose
- daneel_w 4y ago
  It's not. You're just making guesses.
  - CodeWriter23 4y ago
    If the developer doesn't know / doesn't document the project has code embedded in the database, that's on the developer, not the tools. Because the use of any developer tools requires a certain level of competence in order to use them successfully.
    - daneel_w 4y ago
      We version-control all of it. Your assumptions don't matter for reality.

KingOfCoders 4y ago

I thought so for 30 years but changed my opinion recently. I even argued with the author of Redis for some time to add some functionality so we didn't have to write Lua and have another deployment target. Now I do think there is a benefit in stored procedures and triggers (E.g. for audits) if they don't contain too much logic or complexity.

tstrimple 4y ago
> if they don't contain too much logic or complexity. I think this is the catch. Most folks who are arguing against SP have been burned by huge complex stored procedures with nested dependencies with deeply intertwined business logic and rules. I completely agree that you shouldn't use a SP in that way. But to help perform maintenance, or to audit, or perform data correction all make sense when kept small and simple.

flowerbreeze 4y ago

I've only given up trying to understand a system once. It was when I was handed over an application that used stored procedures for everything. Including recursive stored procedures... The rest could be figured out, but they were just too much.

jwmcq 4y ago
I feel this. Once had something locking up a production SQL Server instance, and it turned out to be a dreadful partially-recursive web of sprocs, views, and TVFs that worked fine until apparently one day the query optimiser decided otherwise. Spent hours tracing what the heck was going on.

officialchicken 4y ago

Why? What is your specific reasoning? Using EXPLAIN and SP's to help fix cache misses, slow queries, poor index performance, etc. is generally considered a good thing. As a side note, I did not realize $diety was concerned about DDL/DML, so thanks for pointing it out. I never really thought about it.

doctor_eval 4y ago

Hard disagree with this attitude but I see it all the time. Stored procedures are much faster than writing logic in some remote server (just by virtue of getting rid of all the round trips), require far less code (no DAOs, entities and all that crap which simply serves to duplicate existing definitions), and have built-in strong consistency checking primitives - which can even be safely delayed until the end of the transaction. And what people do is, they throw all these advantages away because they can’t be bothered working out how to integrate the stored procedure code ergonomically into their workflow. I mean - I even use an IDE (JetBrains) to write pl/pgsql. It’s just another file in my repo. Get to this point and stored procedures are a game changer.

viach 4y ago
Like you said "they can’t be bothered working out how to..." That's a fact which is not going to change.

dagss 4y ago

Stored procedures has some advantages (fast to debug/try out a query from your service without copy+paste all the time, etc), but also disadvantages (unreadable git diffs, big bang rollouts on changes) We made this tool to get the best of both worlds: https://github.com/vippsas/sqlcode https://github.com/vippsas/sqlcode

kingboss 4y ago

Yes. Please use stored procedures. Don't listen to this guy.

jmull 4y ago

Your app logic concerned with data executes somewhere, right? So… why exactly would you exclude compute running close to the storage? You can use that minimal latency. Of course, people can create an uncontrolled mess of spaghetti code out of sps/funcs… like they can with any kind of code.