How not to structure database-backed web apps: performance bugs in the wild

On top of the data/frameworks issue there's the one of data locality. Frameworks make the tradeoff decisions for you, but not always the right ones since the right tradeoffs are application-dependent. In one context using a cluster of commodity machines that do a mix of compute for your service and store and shuffle data around is a good idea. In another context a few beefy servers with the beef divided up between the app servers and database servers depending on where most of the compute most efficiently takes place is a better idea. (Though even with poor performance design, hardware is still good enough that a lot of things run just fine by having multiple services on one modest machine. That doesn't stop lots of programmers from reaching for frameworks that demand easily scaleable multi-machine systems even when there's no need and won't ever be need.)

In any case consistency matters: SQL strings are just one form of shipping code to the data to compute remotely instead of retrieving the data and computing locally. When you bring in an ORM, inconsistency is bound to follow with lots of computation that could have been in a query or stored procedure now living in the ORM framework and the application itself.

I can at least understand many people's annoyance with stored procedures though. Most DBs procedural extension languages suck badly enough that shipping JavaScript strings over to NoSQL stores can seem like a big improvement, and various levels of SQL standard conformance is all you typically get to use from the broader design pool of declarative languages.

I've been building apps with Django and Django's ORM for the last 10 years and found essentially zero overhead in most cases. Every once in a while there's a slow page, I open up the debug toolbar which shows me every SQL query that was used to generate the page in a nice waterfall diagram, I see something a little odd and change the order of some filters or add a `select_related`, or `prefetch_related` or discover that some third party library is making a dumb call (unavoidable problem of using third party libraries on any platform) and find a workaround for that. Every once in a blue moon, it appears easier to write a raw SQL statement than figure out what needs to be done to get the ORM to generate it, so I do that. Of course, Django's ORM makes it stupid easy to use a raw SQL statement: https://docs.djangoproject.com/en/2.0/topics/db/sql/

I've worked with a few other ORMs in Python and other languages over the years as well (in Go, Erlang, Elixir, Clojure, nodejs) and never really encountered any where the ORM had a noticeable performance overhead (dominated by the network latency back and forth from the database) and I've yet to work with one that I couldn't just do a raw query when needed. The closest I've seen is when I started using GORM in Go, I found it running slowly and discovered that it automatically adds a "soft delete" functionality so every query gets an additional "and not is_deleted" clause added. Disabled that feature and it was fine. That was my own fault though for starting to build before I finished reading the documentation, as it was pretty clearly explained in a later section. (OK, also the ORMs I was using in Perl and Java back in the late 90's/early 00's were also pretty terrible, but those were prehistoric times.)

You always have to be careful of N+1 problems, but that's not just an ORM thing. You run into that as soon as you have any abstraction in your code. Once you have refactored to `get_list_of_items(some criteria)` and `get_item_details(item)` functions/methods/whatever, whether it is using an ORM underneath or raw SQL, developers working on the app have to know that they can't loop over the results of the first and call the second on each of them. Tradeoffs between reusability and performance are nothing new though and not at all specific to web applications or ORMs.