Get to know your ORM and take control of joins.

A big issue nobody is addressing here is that using "pure SQL" requires you to use crappy data structures (say, a dictionary, "associative array" or, most likely, a simple array in languages like C/C++) to access and push the data. So, instead of having something simple like:

    user = User.objects.get(username='ecopoesis')
    ...
    # 100 lines of business logic that uses the 'user' object.
    # Methods like 'set_password' that calculates a hash of a
    # password and stores it in the object make my life much easier
    # by keeping the logic close to the data.
    ...
    user.save()

Namely, what you can't avoid, if building an object-oriented system, is that you ultimately need to map those DB values to your live objects. Whether you end up using an off-the-shelf ORM or you hand-code the SQL yourself is less relevant.

Odds are if you eschew an ORM in favor of hand-coded SQL layer, you'll end up writing one anyways - only you're likely to do a shittier job of it than people who understand the problem area far more intimately (e.g. Hibernate committers).

I always get strange looks from my fellow programmers when I suggest that we just ditch the ORM and use straight SQL. With some complex queries, like generating reports, I had a much easier time using iBATIS many years ago than hacking through ActiveRecord's oddities. AR is good for simple select/insert/update, but once you get into UNIONs and nested subqueries, you're fighting AR more than you should be.

I have hand coded and maintained a lot of SQL. And you know what's not fun? Adding or removing a column and going through all the selects, updates, inserts and manually changing them all and still getting bit by the 'Table X has no corresponding column of type Y' for a piece of the system used once in a blue moon.

And every time you want to add a new class creating the table, creating all the boilerplate select, insert, update statements that are exactly the same as the thousands of times I've done this before.

All that pointless busy worked done away with in a second. In a couple of keystrokes.

You, in my opinion, do not know what you're missing.

That's what an ORM is for. It's not a 'little time'. It's only a 'little time' if you don't actually do serious business coding where there are often 100s of tables to effectively solve a problem domain.

An ORM will dissipate that entirely. And when you want to drop down to hand coded SQL, you can! Best of both worlds!

The only problem with ORMs is when novices don't understand what they're requesting the DB to do.

And guess what, SQL has exactly the same problem!

What I concluded was that we debug most languages line by line, like moving through a linked list. SQL is much more efficient. A query, if it is well structures (no inline views or UNIONs) is a small set of sections, each of which is broken down further. So instead of reading through a query line by line, we figure out which section to look at, which relations are involved and debug that way. It's like searching a btree instead.

I'll admit, though, that I'm a big fan of having a separate table that has the "front end view" (read: denormalized) of the data for the ORM to read from, especially in cases where the raw data tables are fairly complex.

When you have multiple joins that aren't one-to-one mappings you get all of the different combinations in the result set.

Let's say that you have 5 people, each with 5 addresses and 5 phone numbers. The result set wouldn't have 55 rows (5 people + 25 phones + 25 addresses), but 125 rows (a row for each combination). The difference gets larger as the numbers get larger (for 100 addresses and phones per person, it's 1,005 vs. 50,000). Your ORM also needs to figure out that semi-mess and when you get to large numbers of rows, that's a lot of rows to process.

  John | Address 1 | Phone 1
  John | Address 1 | Phone 2
  John | Address 1 | Phone 3
  John | Address 1 | Phone 4
  John | Address 1 | Phone 5
  John | Address 2 | Phone 1. . .

Now, Hibernate has a way around this. It's been a while, but it's something along the lines of setting it to fetch related entities in batches (EDIT: doing slight Googling, but not checking it in code, it's the @BatchSize annotation and @Fetch annotation that you'll want to look into). In that case, Hibernate basically does a "SELECT * FROM people" and then a "SELECT * FROM addresses WHERE person_id IN (list_of_people_ids_from_last_query)" and a "SELECT * FROM phones WHERE person_id IN(list)". That way, Hibernate gets 5 people records plus 25 address records plus 25 phone records (55 rows in total) and there aren't any duplicates that need to be weeded out, etc. It's three queries which is more than 1, but a lot fewer than the looped queries without the cartesian product.

This is the behavior that Rails' Model.includes(:relation) and Django's Model.objects.prefetch_related('related_set') use. There was a discussion (possibly years ago) on the django-developers list about why select_related('related_set') didn't work on reverse foreign keys and this is the reason why.

Benchmarks are hard.

What were you testing exactly? The behaviour you are seeing is surprising, but if the database server is on the same physical server as the app server and you are testing for response time without loading the server I could see how it could happen.

Did you have indexes setup to support the joins?

Did you have sufficient RAM for the join to be done in memory?

Was the database server hitting IO limits?

I don't think this explains your case, though, where N=20k. I'm not sure what could have been going on there.