Domain Logic and SQL (2003)

Indeed.

Doing data retrieval and manipulation with application code instead of SQL is like doing web layout with JS instead of CSS or doing graphics with canvas instead of HTML/SVG.

Sure it's more flexible, but it's also far less simple, readable, and in many circumstances less performant.

"Rule of least power" is a good principle. [1]

[1] https://en.wikipedia.org/wiki/Rule_of_least_power

We recently changed one of our supplier. The previous one allowed us a full read access to their database (400 tables) but the new one insists that we use their SOAP webservices.

So now when someone ask me for some data, I need to code, manually joining objects instead of crafting a quick SQL query and using the export to CSV function.

Dictionaries (C# key value collection) have fast become a staple diet since Linq is way too slow when you try to 'join' hundred of thousands of objects together.

It feels backward (and I'm saying this as a dev, I'm certainly no DBA or SQL expert).

"But that's not going to be database agnostic!"

Haha ... I'll admit I used to say that but rarely did an internal system (what I work on most) ever actually have that requirement. Systems that are sold to customers to run on their premises are another story but don't worry about using PostgreSQL-only (e.g.) features to speed up both your processing and development times. I have however seen the horrors of 600 line stored-procedures with calls to other stored-procedures nested 15 deep. If you get to the point that you can't easily understand the business logic, you've gone too far.

I think that this is one of those spots where, if you want things to be ORM-friendly, you've got to bake it into the data model.

A good starting point is to use CR instead of CRUD. Append-only tables with soft deletes aren't necessarily the most performant, but they're naturally less susceptible to race conditions. The lack of destructive modification (under normal operation) also makes it easier to diagnose problems and keep audit trails.

> complex retry logic

READ COMMITTED and REPEATABLE READ benefit from retry logic as well, not just SERIALIZABLE.

For a long time, I sought to write deadlock free code.

But that is very hard.

For example in PostgreSQL every UPDATE must be ordered, every DELETE must be ordered. [1]

Finally, I did myself a favor and create application-level retires.

This is 100% cool so long as (1) your deadlocks aren't so frequent so as to reach a performance problem and (2) the action is "replayable" (e.g. no read-once streams). Fortunately, these are both frequently true.

[1] https://dba.stackexchange.com/questions/257587/is-select-for...

Batching is the multi-threadedness of databases.

Its also important to remember that in databases, you are more often optimising for IO usage than CPU.

Retry logic should not be complex! Model transactions as functions, and call them again with the same inputs.

I agree completely. I love SQL just as much. I built a little framework based on SQL and jinja last year.

Shameless plug: https://github.com/socialpoint-labs/sqlbucket

Just changed job though, and the data engineering team I just joined is literally banning raw SQL and enforcing scala instead... I'm a bit sad.

I don't think I've ever seen performance improvements from taking logic in a complex SQL query and re-implementing it at the application layer.

The bottleneck with databases is virtually never calculations on results (CPU), it's disk access and network latency and bandwidth.

And generally, if you do have crazy complex CPU-bound calculations you need to do on data (e.g. scientific stuff)... SQL doesn't provide the necessary functions/control anyways, so the database isn't even an option for that.

The only issue I can imagine you might have run into at some point is complex queries badly written (e.g. recursive subqueries without indices) that you could speed up with application logic -- but then the solution is to optimize the query.

> (...) but at some point you may find that your application has grown and your SQL server is running into IOPS and CPU limits that are difficult to fix.

It's not unique to SQL/databases that at some point your user base may outgrow your current architecture and you may have to invest in something else.

It's dangerous to think you can skip this and just employ whatever architecture is "good" for bigger user bases from the start. There are different trade-offs in different systems and the trade-offs of most alternatives are not as well understood as the trade-offs of databases. I have seen more than one project implode when something which was simple with a database was far more complicated with the alternative.

Most (if not all) modern RDBMSs allow for reliable horizontal and vertical scaling. SQL Server (the db you mention) is no slouch when paired with additional CPUs (for vertical linear scaling) and allows for transactionally safe replication to distributed clusters of SQL Servers (for horizontal scaling) in geographically distributed servers. PostgreSQL has this and it's absolutely free.

Where is this meme that RDBMSs do not scale coming from? (I see lots of people saying this as though it is a given. Has there been any published data on this for me to read?).

The situation goes both ways. Sometimes people put complex logic into the database and it hurts scalability. Other times people pull logic out of the database, but losing all the correlation seeking and filtering means that it now has to do a lot more IO than it did before, what hurts scalability too.

+1 Also, in my experience, SQL query optimizers can also be unpredictable on complex queries (behavior changes with data size and statistics) which can cause them to suddenly change to slow execution plan when data grows or something is added , despite having all indexes for a good plan possible (which for interactive apps is to avoid table scans always -- they might be faster in some bad corner cases, but if data doesn't fit memory, they can't be cached and they slow down everything else).

Follow up: yes, you're all correct, but sometimes things in an organisation aren't perfect and I've been in many situations where the database is the bottleneck with little power to change it. Now that I think about it, it's mostly a symptom of the database frequently under a different 'organisational area' compared to the application. This means we might have the ability to, for example, easily add more application servers, but the database falls under a different department and any changes require a lot more coordination with other parts of the business. I realise this could happen for any resource, but in my experience it's very common to have it occur with a database.

What's the limit of vertical SQL scaling and what kind of applications (I'm talking not only about the functionality, but also realistic level of popularity) can end up hitting it, in your experience?

Another alternative is that based on your workload, using an RDBMS that is column-based.

what an odd comment... I find it difficult to believe that you would run into CPU limits before data movement costs slowed you down.

Also RDBMS are not that difficult to scale, especially easier than an ad-hoc ORM/whatever on your application side.