7 ms·

Stored procedures are a nightmare that shepherd your application into an illegible, unmanageable monstrosity. Stored procedures are the slipperiest slope I've

Stored procedures are a nightmare that shepherd your application into an illegible, unmanageable monstrosity. Stored procedures are the slipperiest slope I've seen as a developer.

empthought 5y ago
Sounds like you’re pretty bad at writing stored procedures.
- freedomben 5y ago
  Sounds like you've never worked on a team.
BoxOfRain 5y ago
That's a bit of an absolutist stance I think, a good programmer can use stored procedures just fine and in some cases it can even improve performance. Yes it makes it easier for bad programmers to write bad code, but bad programmers will write bad code no matter what tools they have in their toolbox.
taffer 5y ago
In my experience, such problems only occur when people believe that common software engineering practices do not apply when writing stored procedures. Just have your stored procedures version controlled, tested, and deployed like any other code.
- kbenson 5y ago
  The real problem is that you're almost always shifting work from a language that is well known and understood by you and/or your team to one that is less, or even poorly, understood and known, and you end up incurring the cost of novice programmers, which can be a real problem for both security and performance. If you have good knowledge and experience in the language your preferred version of SQL implements, that's good. If you just have people that understand how to optimize schemas and queries, you might find that you encounter some of the same problems as if you shelled out to somewhat large and complex bash scripts. The value of doing so over using your core app language is debatable.
  - taffer 5y ago
    If your front-end is written in React and your business logic is written in SQL, is it really fair to call what's left in the middle tier a "core app language"? If you're writing a SaaS application today, you're more likely to want to rewrite your Java middle tier in Go than replacing your DBMS.
    - kbenson 5y ago
      Not everyone is making a web app, or even something amenable to using React native, and even if they are, there's no guarantee that their middle tier isn't also in JavaScript. That said, I wasn't making a case about replacing your DBMS. I specifically avoided that because yes, most people stay with what they know and used, and even if they switch, they switch for a different project, not within the same project. There are some cases where multiple DBMS back-end support is useful, but I think that's a fairly small subset (software aimed towards enterprises which wants to ease into your existing system and note add new requirements, and open source software meant to use one of the many DBMS back-ends you might have). My actual point is more along the lines of: - Most DBMS hosted languages I've seen are pretty shitty in comparison to what you're already using. - The tooling for it is likely much worse or possibly non-existent. - You are probably less familiar with it and likely to fall into the pitfalls of the language. All languages have them, shitty languages have more. See first point. - If you accept those points and the degree to which you accept them should definitely play a role in deciding to use stored procedure you've written in the language your DBMS provides. - I think trade offs are actually similar to what you would see writing chunks of your program in bash and calling out to that bash script. People can write well designed and safe bash programs. It's not easy, and there are a lot of pitfalls, and you can do it in the main language you're writing probably. Thus the reasons against calling out to bash for chunks of core are likely similar to the reasons against calling a stored procedure.
      - taffer 5y ago
        You make some good points. I personally have experience with business web applications, i.e. large complex data models with rather simple updates and report-like queries. These types of queries combined with a well normalized data model map well to set-based SQL. Of course, it's a different story if you're writing technical applications or games that are more about crunching numbers than querying and updating data. To me, the shitty procedural languages you mention are just for gluing queries together. The important stuff happens in SQL and the simplicity of keeping it all in the database is worth it.
moksly 5y ago
This isn’t my experience at all, but I suppose it depends on how you build your applications. I used to be of your opinion, before we moved more and more of our code based from .Net to Python an I was a linq junkie, but these days I think you’re crippling your pipeline if you don’t utilise SQL when and how it’s reasonable to do so. There are a lot of data retrieval where a stores procedure will will save you incredibly amounts of resources because it gives you the exact dataset you need exactly when you need it.

Stored procedures are a nightmare that shepherd your application into an illegible, unmanageable monstrosity. Stored procedures are the slipperiest slope I've seen as a developer.

empthought 5y ago

Sounds like you’re pretty bad at writing stored procedures.

freedomben 5y ago
Sounds like you've never worked on a team.

BoxOfRain 5y ago

That's a bit of an absolutist stance I think, a good programmer can use stored procedures just fine and in some cases it can even improve performance. Yes it makes it easier for bad programmers to write bad code, but bad programmers will write bad code no matter what tools they have in their toolbox.

taffer 5y ago

In my experience, such problems only occur when people believe that common software engineering practices do not apply when writing stored procedures. Just have your stored procedures version controlled, tested, and deployed like any other code.

kbenson 5y ago
The real problem is that you're almost always shifting work from a language that is well known and understood by you and/or your team to one that is less, or even poorly, understood and known, and you end up incurring the cost of novice programmers, which can be a real problem for both security and performance. If you have good knowledge and experience in the language your preferred version of SQL implements, that's good. If you just have people that understand how to optimize schemas and queries, you might find that you encounter some of the same problems as if you shelled out to somewhat large and complex bash scripts. The value of doing so over using your core app language is debatable.
- taffer 5y ago
  If your front-end is written in React and your business logic is written in SQL, is it really fair to call what's left in the middle tier a "core app language"? If you're writing a SaaS application today, you're more likely to want to rewrite your Java middle tier in Go than replacing your DBMS.
  - kbenson 5y ago
    Not everyone is making a web app, or even something amenable to using React native, and even if they are, there's no guarantee that their middle tier isn't also in JavaScript. That said, I wasn't making a case about replacing your DBMS. I specifically avoided that because yes, most people stay with what they know and used, and even if they switch, they switch for a different project, not within the same project. There are some cases where multiple DBMS back-end support is useful, but I think that's a fairly small subset (software aimed towards enterprises which wants to ease into your existing system and note add new requirements, and open source software meant to use one of the many DBMS back-ends you might have). My actual point is more along the lines of: - Most DBMS hosted languages I've seen are pretty shitty in comparison to what you're already using. - The tooling for it is likely much worse or possibly non-existent. - You are probably less familiar with it and likely to fall into the pitfalls of the language. All languages have them, shitty languages have more. See first point. - If you accept those points and the degree to which you accept them should definitely play a role in deciding to use stored procedure you've written in the language your DBMS provides. - I think trade offs are actually similar to what you would see writing chunks of your program in bash and calling out to that bash script. People can write well designed and safe bash programs. It's not easy, and there are a lot of pitfalls, and you can do it in the main language you're writing probably. Thus the reasons against calling out to bash for chunks of core are likely similar to the reasons against calling a stored procedure.
    - taffer 5y ago
      You make some good points. I personally have experience with business web applications, i.e. large complex data models with rather simple updates and report-like queries. These types of queries combined with a well normalized data model map well to set-based SQL. Of course, it's a different story if you're writing technical applications or games that are more about crunching numbers than querying and updating data. To me, the shitty procedural languages you mention are just for gluing queries together. The important stuff happens in SQL and the simplicity of keeping it all in the database is worth it.

moksly 5y ago

This isn’t my experience at all, but I suppose it depends on how you build your applications. I used to be of your opinion, before we moved more and more of our code based from .Net to Python an I was a linq junkie, but these days I think you’re crippling your pipeline if you don’t utilise SQL when and how it’s reasonable to do so. There are a lot of data retrieval where a stores procedure will will save you incredibly amounts of resources because it gives you the exact dataset you need exactly when you need it.