

Get to know your ORM and take control of joins. - fatalmind
http://use-the-index-luke.com/l/sql/join/nested-loops-join-n1-problem

======
ecopoesis
Or better yet, ditch your ORM and learn SQL.

ORMs are an antipattern. They save you a little time and effort up front, but
when you inevitably need to do something that's not basic CRUD you end up
wasting an immense amount of time fighting your tools.

Don't fight your tools, just write SQL.

~~~
chimi
People ask me, "Which language should I learn first?" I always say, SQL. If
you know SQL you can do more than a lot of programmers who don't know SQL and
SQL makes for better programs too. Win-Win.

~~~
ufo
Come on, although SQL is declarative and relational, it is still very low
level, has terrible support for abstraction and has all sorts of
incompatibilities depending of the database implementation you use. I can't
believe we are already in the 21st century and good relational libraries are
not yet the standard solution.

------
jiggy2011
I have noticed some unexpected behaviour in the past with joins vs N+1
selects.

I was using an ORM (hibernate) for a webapp connected to mysql. When I turned
on the query log I found a lot of N+1 Select stuff going on.

I made the joins explicit, expecting a performance gain. When I tested the
performance again, using apache benchmark and also a query profiler I
discovered that there was absolutely 0 performance difference between the 2
solutions. These were with some reasonable sized datasets, ~20k entries across
2 tables.

I guess this is because of some sort of optimisation done at the mysql level.

I guess that my experiment did not take into account running the same set of
queries more "spaced out" in time with other different queries (to account for
caching etc).

Perhaps though it is possible that ORM designers are aware of these kind of
optimisations so don't really worry about these queries as much as might be
expected?

~~~
nl
_When I tested the performance again, using apache benchmark and also a query
profiler I discovered that there was absolutely 0 performance difference
between the 2 solutions._

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?

~~~
jiggy2011
The test was done with DB and HTTP servers on the same box. I tested both the
HTTP response times from another box and also the time for the SQL queries to
return inside the same box. Indexes were setup for the joins and there would
have been enough RAM to store the dataset.

I did not really bare IO in mind doing the test because if IO was the
bottleneck then I would have optimised enough.

Certainly a bad test from anything approaching a scientific view but I had
expected to see a performance difference even in this scenario (especially
when testing the turnaround between the Java app and Mysql).

I think my faulty logic at the time was that N+1 selects would be slow because
of the overhead of parsing multiple queries. What led to to that conclusion
was that I had previously optimised an old PHP app that was using
mysql_query() to use joins rather than millions of selects and more or less
got 10x performance back.

------
lucian1900
Not all ORMs give you the ability to do any joins, and even fewer give you
subqueries and proper group by.

Relational libraries like SQLAlchemy (Python) and Korma (Clojure) are much
better at letting you use your relational database conveniently.

~~~
BadassFractal
How would you compare Korma to say.. ActiveRecord?

~~~
lucian1900
I've never used active record, but from what I've read it's an ORM. Both
SQLAlchemy and Korma expose relational concepts directly.

SQLAlchemy also has an optional ORM built on top of the relational
abstraction.

