Comment by bob1029
2 days ago
> But for processing (and validating) various data loads and incoming data streams, by looking up matching values in existing reference tables, stored procedures can increase performance/efficiency and reduce complexity
Performing operations on data directly in the SQL provider is the peak of human enlightenment. It takes a lot of leadership or wisdom to push a modern team away from using crap like EF to process everything, but 100x slower.
In exactly 0% of cases of cycling records through code will you see a higher performance result than executing a single T-SQL or PL/SQL script against the provider.
The procedural SQL languages are Turing complete. SQL itself is as of recursive common table expressions. There's not any reasonable argument for not trying this if all the information is already in the SQL store.
Moving information is way more expensive than processing information that is already in place (cache). Your SQL server process can iterate on items in L1 millions of times before a packet makes it across the data center one time.
Operational concerns trumps raw performances most of the time. Stored procedures live in a different CI/CD environment, with a different testing framework (if there’s even one), on a different deployment lifecycle, using a different language than my main code. It is also essentially an un-pinnable dependency. Too much pain for the gain.
Now, give me ephemeral, per-connection procedures (call them unstored procedures for fun) that I can write in the language I want but that run on provider side, sure I’ll happily use them.
> Stored procedures live in a different CI/CD environment
They don't have to. The procedural SQL commands can be source controlled along with the rest of the codebase. Transmitting the actual command text to the SQL server that does all the work is not the inefficient part.
Why do they live in separate CI?