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.
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.
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.
> 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.
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.
> 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.
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.
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.
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.
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)
doctor_eval|3 years ago
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|3 years ago
daneel_w|3 years ago
simonw|3 years ago
- 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.
KingOfCoders|3 years ago
maerF0x0|3 years ago
CodeWriter23|3 years ago
This is the classic "carpenter blames his tools for crappy results" argument. Implementation isn't easy.
KingOfCoders|3 years ago
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|3 years ago
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|3 years ago
jwmcq|3 years ago
officialchicken|3 years ago
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.
kingboss|3 years ago
jmull|3 years ago
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.
dagss|3 years ago
We made this tool to get the best of both worlds:
https://github.com/vippsas/sqlcode