Wrong ways to use the databases, when the pendulum swung too far

I am not going to dismiss your experience here. Stored Procedures can turn into wild monsters. Pair it with Triggers and you are in for chasing a noodle.

But it's also a reality, that relational databases often become the main integration point in companies. In those environments it’s hard (next to impossible) and dangerous to use something like ORMs.

Often enough I don't "own the tables" and I don't "own the columns" for lack of a better word. The DBA only gives me SELECT and EXECUTE permissions on the database. How am I going to work here without Stored Procedures?

And while this sounds weird, these limited permissions are to protect me from accidentally writing to wrong columns. Wrong columns that could impact systems, I didn't even know about. Is it possible to write to the same columns with a Stored Procedure? Of course! But it's not as dangerous as giving an application fat UPDATE permissions.

By using SQL Views I can build a data model upon these tables for reading the data, and build a more consumable data model. And you mentioned C#: I can use EF Core to query these Views with LINQ. Stored Procedures are used to store data to multiple tables in a "legacy database" within a transaction.

This could also be done with EF Core, but I need to explicitly log all changes to the data. How should this be done without a Stored Procedure or Triggers? CDC doesn't help a lot here.

I'm convinced that the solution to stored procedures that people are afraid to update is automated tests. Write unit tests for them just like you would any other complex logic - the tests can evolve over time into a detailed spec for how they should work, and give you the ability to refactor or improve them with confidence later on.

For some reason stored procedures and automated testing seem to often not go together, maybe because they're often accompanied by an environment where DBAs and software developers are separate teams with separate sets of skills?

> you probably want to have the bulk of your business logic written in C#

Perhaps, if the C# applications are the only ones accessing the database. But suppose multiple applications written in different languages (and for different purposes) need the database access? You can do this via stored procs or (better in my experience) by adding an intermediate server process which the applications use, via a publicly documented API, and which then talks to the the database which may provide stored proc access to the intermediate server.

Equally anecdotally:

An example is a server me and a couple of colleagues worked on at a big investment bank. The underlying databases were mostly proprietary from suppliers or written by other teams in the bank. And versioned (badly, eek!). We wrote the SQL queries/procs to access the data that the VB (now C# I guess) would not have been capable of (without enormous waste of effort), and then provided COM interfaces to our server that ran our queries. Mercifully, we didn't have to provide updates, but the same idea would have applied. This gives you more or less complete isolation between app and database.

Anyway, SPs can certainly fit into such an architecture, but might not be first (or second) choice.

Oh man. I've seen an org where the stored procedures were written by a different team, and friction was high.

Then one java dev found a glaring sql injection hole. The whole app quickly reorganized around using it as an API for everything. Management was pleased speed went up and bugs went down, DBAs were pleased for less work, and Java devs were pleased they could understand what was going on in the app. Everybody happy I guess?

I’d rewrite the complete team instead.

I work for a company that's still obsessed with micro services. "Religious" is a pretty good way to describe it. Drives me absolutely nuts, especially when no one seems to understand what micro services are. I've seen at least half a dozen teams that have just created a distributed monolith and called it a micro service because they're using Docker and Kubernetes.

ORMs are basically the enemy of rdbms, by encouraging antipatterns and undermining language features they have easily done more harm than good. of course reasonable people can be trusted to use them reasonably but if you're arguing about it on the internet..

Haha. I’ve actually had an ORM attempt to load the whole database just to do a count. That was a fun bug to dig into (nobody noticed until we had a big enough customer) and an easy fix. Literally, all I had to do was mark some fields as lazy loaded.

Yeah, I've had almost the same experience with people and ORMs. Often they'll make a good point about some weakness of some specific ORM, but it is almost always some use case that represents 1% (or less) of their application. Worse, there's nothing stopping them from just using sql for those couple of times that they need to bypass the ORM. After all, there's no reason to be religious about it and only use one tool.

I'm not hugely experimented, and SQL has always been enough for me, perhaps due to my simple requirements. But so far I hold the opinion that ORM is always a mistake.

I find a lot of programmers don't know how to write an array of floats to disk, if you forced me to choose between an ORM or No DB at all, I would choose no DB all day.

ORM feels like an abstraction on top of an abstraction. I don't trust that those who chose ORMs have studied and exhausted the possibilities of the two underlying layers, I feel more often than not they recourse to higher layers of technology without understanding the lower level.

But I may be wrong, maybe those who use ORMs know all about File systems and Posix and SQL itself.

It also reminds me of the people who ask chatgpt to write a prompt for them, it's as if you had a dishwasher and you were too lazy to load the dishes onto the dishwasher, so you buy a product that loads the dishes to the dishwasher.

> 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.

I've got the opposite experience. Most rewrites I was on had substantial uplift in the product value.

The trick is to engage with the customer early, directly and often. If the customer isn't willing to pay more money for something, you should probably not do it.

It really depends on the origin of the system to be replaced. If it was never designed to be replaceable you are usually in for a bad time. Similarly if the drive to replace it comes from new folk that simply don't understand the original system you are probably completely doomed.

I often write code with full intention of replacing it, generally by writing it in such a way it's replacement is forced. That latter bit is pretty important because systems generate inertia, you need to have some hard forcing function baked into prevent it ossifying.

i.e my current project is to replace a pretty awful pile of Bash I wrote last year and replace it with a much more sophisticated and automated system in Golang. The new system is already in production and the old tools are being phased out.

Writing the original in Bash was how I basically ensured it would get rewritten and not continually upgraded/morphed into something grotesque. There was a ton of pushback, lots of people saying "why not Go! why not Python! This is an awful decision, 3k LOC of Bash is an unmaintainable nightmare!". Literally all of those people missed the point.

Building simple systems that you 100% will replace (not intend, that really isn't the same thing) is a really good pattern to find a real understanding of the solution space before building something gold plated/spaceship-like.

It generally means once you implement the follow up it very rarely becomes "legacy" software without horrible mismanagement, i.e bringing in new management that wants to rewrite things for the sake of rewriting them, usually in some FoTM nonsense.

1. Beyond just querying, the stored proc spent a lot of time processing data. As in, looping through cursors, making business logic decisions, calculating things, etc.

2. Having the business logic (not just loading the data) inside the stored procs meant that a change in business logic that would normally only need to update application code, now invalidates the stored procedure's cached execution plan.

I've seen customers be driven away by poorly performing interfaces. I've seen downtime caused by exponentially growing queries. I've seen poorly written queries return such large datasets that they cause servers to run out of memory processing the request.

Unless you're doing stock trades or black Friday sales, though, it can be pretty hard to pin down a specific inefficiency to a concrete measure of list income. Instead, people move on from products for general "we don't like it" vibes.

The most concrete measure, as someone else pointed out, is heavily inflated PAAS spend caused by poorly written code that requires beefier than necessary servers. In theory, moving faster means you're spending less money on developer salaries (the Ruby/rails mantra of old) but there's a distinct tipping point where you have to pony up and invest in performance improvements.

My previous job designed their data lake and operations on it with horrific incompetence, and their solution was just to use AWS Lambdas scaling into the thousands and tens of thousands to do stuff over it.

They made so much money but would then squander it on hopelessly inefficient designs that required an AWS spend that basically prevented them from ever financially scaling.

IME, the suffering of bad performing code is mostly secondary. It increases compute costs. Mostly because requiring more beefy VMs than strictly required which is still benign and possibly more cost-efficient than spending more engineering effort. Sometimes because of the lack of performance now more scaling and orchestration is required which comes additional complexity and therefore compute and staffing cost. This is rare to get noticed and fixed due to organisational momentum.

The worst is when the performance is so bad it starts to prevent onboarding new features or customers.

Only if the bad code affects customer experience significantly. That only happens to a big enough amount of you really let things grow out of contol. At some point you'll get bugs that take forever to solve and angry customers as a result.

I bet Atlassian could make even more money with Jira if it wasn't this slow. They are not struggling as it is, but it's bad enough that it is costing them customers

But generally I would agree

As a general rule it is hard to measure lost opportunity costs. That doesn't mean they don't exist or shouldn't be considered. I mean.. why do humans even acknowledge efficiency at all.. let alone almost always as a virtue?

The problem I've seen, very few people from Java / C# / other languages actually know plsql or ever bothered to learn it. Its not that hard, but I guess not a cool enough tech to have on resume? When it could massively help with speed of certain use cases, in ways no external code could ever dream of achieving.

The KV store had etag support for conditional writes. Etags are only useful to make sure the data didn't change underneath between your read and your write.

Storing the checkpoints along with the mutation was for idempotency. If the checkpoint was in the document, that meant the mutation had succeeded and a retry should be no-op

The author was implying that in between writing the business logic, they were smoking a lot of weed.

Yeah, I wondered what was going on there. Maybe just a joke or error, but definitely makes one wonder how much experience the author has with sql to compare/contrast the approaches to a more standard approach.

Never try to smoke an inner joint.

Seems to be this[1] guy, which you can find out by skulking around an older snapshot of his website from the Wayback Machine, where he displayed his real name within the footer on every page, and went by a slightly different username up to seemingly as late as April this year. Maybe a rather poor attempt at dissociating his real identity from the website somehow? (on second thought, he still links to the webpage from his GitHub profile, so that wouldn't make much sense, I think. Hmm.)

[1]: https://github.com/luuhq

Not Dan Luu. And to the other comment, not intentionally hiding my identity. Just that for most of my writing, who I am is irrelevant.