4 ms·

I remember 10 years ago, and I'm sure as hell not going back. Versioning of SPs was hard. Code control for SPs was hard. Automated testing of SPs was hard. Inc

I remember 10 years ago, and I'm sure as hell not going back. Versioning of SPs was hard. Code control for SPs was hard. Automated testing of SPs was hard. Incremental deployment (e.g. rolling out the new code to half of the servers in the web farm) was hard. Parallel development against a shared dev/staging server was hard. Trivial schema updates were hard. And even then, it was never guaranteed to be fast. If the underlying tables weren't indexed, or your stored procedures did stupid shit like use cursors or temporary tables, it was still slow.

eftpotrm 16y ago
Yes, bad table designs and poor practice will make your code slow regardless. Still though, while I don't dispute that SPs (frankly, databases in general) have issues with version control and deployment management, there are still options for managing that sort of issue that don't require the blunt instrument of the ORM tool generating its own set of dependencies and inappropriate SQL for the job, leading to the sort of performance issues that the article talks about....
jasonkester 16y ago
Why is any of that hard? Stored procedures and database schema changes are all written in SQL, which goes into source control nicely. Schema changes are easy in an environment like that. Parallel development on multiple dev databases rolling changes into a single staging server is easy in an environment like that. Just check in your code and let the automated build do it's thing. Nothing has changed in the last 10 years to make it harder. Granted, many shops weren't doing it well back then, thus the misconception that versioning SQL databases is hard, and that schema changes are hard. That stuff was only ever hard because people declared it to be so, and built crazy bureaucratic organizational limitations to stop developers from changing the database (because that's hard and therefore not something that developers should be doing.) Once you start thinking of it as easy and build your tools around that fact, you'll notice that it actually is easy.
- MartinCron 16y ago
  I'm not laboring under a misconception. I know how to do all of those things, I was doing them, it's just easier not to. The example that comes to mind is making a change to a stored procedure's signature. In order to change it, you have to change all of the clients that talk to it at the exact same time. As a result, incremental rollouts become impossible, so you wind up doing high-risk huge-scale deployments or doing things like creating procedures named EntityInsertV2 and EntityInsertV3 or adding optional parameters with defaults or ignoring parameters that the procedure no longer needs but older clients still want to provide. And then there's cleanup. As you really should remove EntityInsertV1 once all of the clients are using EntityInsertV2, but you can't have that in the same Update SQL, so there's really two deployments for every deployment. If there's an easy fix for this problem (other than keeping procedural logic out of the persistence layer) I never found it.
  - r00fus 16y ago
    > making a change to a stored procedure's signature. In order to change it, you have to change all of the clients that talk to it at the exact same time. Assuming all of your clients are all server-side (ie, servlets) and are also in source control, this should not be an issue. If you have thick clients accessing SQL functions directly, you have a bigger problem (client-server... ugh) that browsers have solved. If you have multiple servers talking to each other, you still have a version of the "thick client" problem, namely enterprise integration... for which there are intermediary APIs.
    - MartinCron 16y ago
      Assuming all of your clients are all server-side (ie, servlets) and are also in source control, this should not be an issue. Source control isn't the issue (of course everything is also in source control, this isn't the dark ages). I'm talking about a pretty common scenario where you have one SQL server and 4 web servers acting as clients. Let's say you want to do a zero-downtime rolling deployment and upgrade the web servers one at a time while the other 3 continue to run. This is a real issue that I encountered on multiple projects that were adhering to the "everything must be in sprocs" dogma. Moving to ORMs and parameterized queries saves a lot of headaches.

eftpotrm 16y ago

Yes, bad table designs and poor practice will make your code slow regardless. Still though, while I don't dispute that SPs (frankly, databases in general) have issues with version control and deployment management, there are still options for managing that sort of issue that don't require the blunt instrument of the ORM tool generating its own set of dependencies and inappropriate SQL for the job, leading to the sort of performance issues that the article talks about....

jasonkester 16y ago

Why is any of that hard? Stored procedures and database schema changes are all written in SQL, which goes into source control nicely. Schema changes are easy in an environment like that. Parallel development on multiple dev databases rolling changes into a single staging server is easy in an environment like that. Just check in your code and let the automated build do it's thing. Nothing has changed in the last 10 years to make it harder. Granted, many shops weren't doing it well back then, thus the misconception that versioning SQL databases is hard, and that schema changes are hard. That stuff was only ever hard because people declared it to be so, and built crazy bureaucratic organizational limitations to stop developers from changing the database (because that's hard and therefore not something that developers should be doing.) Once you start thinking of it as easy and build your tools around that fact, you'll notice that it actually is easy.

MartinCron 16y ago
I'm not laboring under a misconception. I know how to do all of those things, I was doing them, it's just easier not to. The example that comes to mind is making a change to a stored procedure's signature. In order to change it, you have to change all of the clients that talk to it at the exact same time. As a result, incremental rollouts become impossible, so you wind up doing high-risk huge-scale deployments or doing things like creating procedures named EntityInsertV2 and EntityInsertV3 or adding optional parameters with defaults or ignoring parameters that the procedure no longer needs but older clients still want to provide. And then there's cleanup. As you really should remove EntityInsertV1 once all of the clients are using EntityInsertV2, but you can't have that in the same Update SQL, so there's really two deployments for every deployment. If there's an easy fix for this problem (other than keeping procedural logic out of the persistence layer) I never found it.
- r00fus 16y ago
  > making a change to a stored procedure's signature. In order to change it, you have to change all of the clients that talk to it at the exact same time. Assuming all of your clients are all server-side (ie, servlets) and are also in source control, this should not be an issue. If you have thick clients accessing SQL functions directly, you have a bigger problem (client-server... ugh) that browsers have solved. If you have multiple servers talking to each other, you still have a version of the "thick client" problem, namely enterprise integration... for which there are intermediary APIs.
  - MartinCron 16y ago
    Assuming all of your clients are all server-side (ie, servlets) and are also in source control, this should not be an issue. Source control isn't the issue (of course everything is also in source control, this isn't the dark ages). I'm talking about a pretty common scenario where you have one SQL server and 4 web servers acting as clients. Let's say you want to do a zero-downtime rolling deployment and upgrade the web servers one at a time while the other 3 continue to run. This is a real issue that I encountered on multiple projects that were adhering to the "everything must be in sprocs" dogma. Moving to ORMs and parameterized queries saves a lot of headaches.