4 ms·

>> Any stored proc codebase is guranteed over time to become a maze of complexity and footguns. I think this is a very valuable and important point and wanted

>> Any stored proc codebase is guranteed over time to become a maze of complexity and footguns. I think this is a very valuable and important point and wanted to highlight it. >> Any stored proc codebase is guranteed over time to become a maze of complexity and footguns. There's no absolute law that states that SP using environments will always end up as a mess, but there should be such a law, because they do! I've worked across dozens to hundreds of enterprise environments, and the pattern is clear. I suppose it comes from personality and culture with the people working on db coding more often in the 'get it done' end of the spectrum vs programmers where you find a significant number interested in 'get it right'. An even stronger reason is probably that db coders are more likely to be autonomous instead of in large centrally driven teams. That central hierarchical organisation is a necessary but not sufficient condition for working to tame the entropy of large complex systems, without which they evolve into "a maze of complexity and footguns".

marcus_holmes 6y ago
yeah, I don't think this is a problem with sprocs, per se. I think this is a problem because the database code is treated differently from the application code, because so few coders understand SQL well enough to be comfortable in it. So, as you say, the DBA's tend to be autonomous, and held to different standards. And with less ability to say "we have tech debt, we need to refactor our sprocs" because of the knock-on effect on the application code. It's a management problem rather than a technical certainty, I think.
- WJW 6y ago
  If it happens consistently across "dozens to hundreds" of enterprise environments as stated by the GP and with apparently zero exceptions in which they remained simple then, while maybe not a technical certainty, it should be treated as a practical certainty. Pointing out that it's probably caused by management is not really very useful since there are so few means available for line workers to fix their managers.
  - marcus_holmes 6y ago
    This is true, and I agree, but mis-attributing the problem is still not good. After all, if (for instance) a new IDE plugin appeared that managed sprocs consistently with code and solved this problem, then we'd still face "sprocs are bad" criticism because the problem was mis-attributed.
AmericanChopper 6y ago
It's certainly a valid observation that anything that large enterprise does, will end up being more complex than it necessarily needs to be. But if I look at a lot of the SP anti-patterns I've seen over the years, some common themes start to arise. I'd say the biggest issue is that it is more complex to maintain interfaces between two discrete systems than it is to write monolithic code paths. Seeing SP_ProcedureName accompanied by SP_ProcedureName_New is very common. It's easy to get into that situation, and tedious to get out of it. Storing business logic in the DB also presents a lot of very tempting, but ultimately costly, shortcuts to developers. Using a non-temporary table when you should have used a temporary table, because you wanted debugging to be easier, but now you've got a concurrency issue, but it'll probably be OK, so you leave it there for 15 years. I have also never seen what I would consider to be a sensible use of a trigger. You also end up with SPs invoking SPs invoking SPs... and figuring out the side effects of calling something becomes a lot more complicated than it would be in a monolith. I don't think they're always bad, but I do think that reasonable use cases for them are rather uncommon. When you had a lot of services directly consuming one database, SP interfaces used to make a lot more sense. But that's not a very common architecture is not very common any more. Even in large enterprises, you'll typically see new projects being developed as web applications, even if they're only served over the corporate network.

marcus_holmes 6y ago

yeah, I don't think this is a problem with sprocs, per se. I think this is a problem because the database code is treated differently from the application code, because so few coders understand SQL well enough to be comfortable in it. So, as you say, the DBA's tend to be autonomous, and held to different standards. And with less ability to say "we have tech debt, we need to refactor our sprocs" because of the knock-on effect on the application code. It's a management problem rather than a technical certainty, I think.

WJW 6y ago
If it happens consistently across "dozens to hundreds" of enterprise environments as stated by the GP and with apparently zero exceptions in which they remained simple then, while maybe not a technical certainty, it should be treated as a practical certainty. Pointing out that it's probably caused by management is not really very useful since there are so few means available for line workers to fix their managers.
- marcus_holmes 6y ago
  This is true, and I agree, but mis-attributing the problem is still not good. After all, if (for instance) a new IDE plugin appeared that managed sprocs consistently with code and solved this problem, then we'd still face "sprocs are bad" criticism because the problem was mis-attributed.

AmericanChopper 6y ago

It's certainly a valid observation that anything that large enterprise does, will end up being more complex than it necessarily needs to be. But if I look at a lot of the SP anti-patterns I've seen over the years, some common themes start to arise. I'd say the biggest issue is that it is more complex to maintain interfaces between two discrete systems than it is to write monolithic code paths. Seeing SP_ProcedureName accompanied by SP_ProcedureName_New is very common. It's easy to get into that situation, and tedious to get out of it. Storing business logic in the DB also presents a lot of very tempting, but ultimately costly, shortcuts to developers. Using a non-temporary table when you should have used a temporary table, because you wanted debugging to be easier, but now you've got a concurrency issue, but it'll probably be OK, so you leave it there for 15 years. I have also never seen what I would consider to be a sensible use of a trigger. You also end up with SPs invoking SPs invoking SPs... and figuring out the side effects of calling something becomes a lot more complicated than it would be in a monolith. I don't think they're always bad, but I do think that reasonable use cases for them are rather uncommon. When you had a lot of services directly consuming one database, SP interfaces used to make a lot more sense. But that's not a very common architecture is not very common any more. Even in large enterprises, you'll typically see new projects being developed as web applications, even if they're only served over the corporate network.