5 ms·

I'll probably get down-voted for saying this (again), but a key way to squeeze unimaginable amounts of performance is to lean into stored procedures. Look, I g

I'll probably get down-voted for saying this (again), but a key way to squeeze unimaginable amounts of performance is to lean into stored procedures. Look, I get it, the devx sucks. And it feels proprietary, icky, COBOL-like experience. It means you have to dwell in the database. What are you, a db admin?! But I'm telling you, the payoff is worth it. (and also, if you ship it you own it so yes you're a db admin). My company ran for many years on 3 machines, despite it's extremely heavy page weight because the original author wrote it stored procs from the beginning. (He also liberally threw away data, which was great, but that's another post.) Part of my job was to migrate away from .NET and to Java and JavaScript - and another engineer wrote an ingenious tool that would generate Java bindings to SQL Server stored procs that made it really nice to work with them. And the performance really was outrageous - 100x better than any system I've worked with before or since. Those 3 boxes handled 300k very data intensive monthly actives, and that was like 10 years ago. Don't worry - even if you lean into SPs there is still plenty of engineering to do! It's just that your data layer will simplify, and your troubleshooting actually gets easier, not harder. I liked the custom bindings - a bit like ActiveRecord, and no ORM. But really, truly: if you want to squeeze, move some queries into SPs and prepare to be amazed.

giantrobot 3y ago
I can't disagree with the results, SPs can change your life. HOWEVER, they require significant discipline and regular audits. All the code for them needs to be in source control with a Process for deployment to the DB. You also need a test suite as part of the Process which runs against a staging server with a comparable configuration to prod. The SPs need to be regularly dumped and compared against what's in source control and marked as what's supposed to be released in prod.
- deleted 3y ago
  [deleted]
- javajosh 3y ago
  True enough; the binding tool handled this. The system of record was in the app server. The db was considered slaved to the app, and more-or-less tightly coupled to it (within reason). This provides a really solid mental model. Side-note: the GP comment was actually doing pretty well (like +7) now it's (+2) so a cadre of anti-stored proc folks did a drive by down-voting. A bit sad, IMO.
throwdbaaway 3y ago
What do you think about evolving the stored procedures into a stateless GRPC service that fronts the database? For the price of 1 (or 2) additional network hop, you get much better devx, while keeping most of the benefits provided by stored procedures.
- javajosh 3y ago
  I don't think this adds much. The important thing is that your logic runs in the database--how the logic is ultimately exposed is up to you. We did a java app server calling SPs over JDBC with generated, typed bindings and this worked great. You'd have to write a similar tool to generate a gRPC server, but the logic would still be SPs in the database. That's the part that sucks for devs; the bindings are ultimately a detail.

giantrobot 3y ago

I can't disagree with the results, SPs can change your life. HOWEVER, they require significant discipline and regular audits. All the code for them needs to be in source control with a Process for deployment to the DB. You also need a test suite as part of the Process which runs against a staging server with a comparable configuration to prod. The SPs need to be regularly dumped and compared against what's in source control and marked as what's supposed to be released in prod.

deleted 3y ago
[deleted]
javajosh 3y ago
True enough; the binding tool handled this. The system of record was in the app server. The db was considered slaved to the app, and more-or-less tightly coupled to it (within reason). This provides a really solid mental model. Side-note: the GP comment was actually doing pretty well (like +7) now it's (+2) so a cadre of anti-stored proc folks did a drive by down-voting. A bit sad, IMO.

throwdbaaway 3y ago

What do you think about evolving the stored procedures into a stateless GRPC service that fronts the database? For the price of 1 (or 2) additional network hop, you get much better devx, while keeping most of the benefits provided by stored procedures.

javajosh 3y ago
I don't think this adds much. The important thing is that your logic runs in the database--how the logic is ultimately exposed is up to you. We did a java app server calling SPs over JDBC with generated, typed bindings and this worked great. You'd have to write a similar tool to generate a gRPC server, but the logic would still be SPs in the database. That's the part that sucks for devs; the bindings are ultimately a detail.