9 ms·

Get to know your ORM and take control of joins.

40 points by fatalmind 14y ago

lucian1900 14y ago
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 14y ago
  How would you compare Korma to say.. ActiveRecord?
  - lucian1900 14y ago
    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.
jiggy2011 14y ago
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 14y ago
  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?
  - fatalmind 14y ago
    Besides your points, we must also consider that MySQL does neither support hash joins nor sort/merge join. MySQL it just not very good at joining. Nevertheless, a MySQL join should never be slower than the N+1 select approach.
  - jiggy2011 14y ago
    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.
- jsight 14y ago
  I do find it surprising that the results were identical. Having said that, my experience is that over low-latency connections N+1 selects are not as bad as they are often represented to be. I've occasionally chosen to accept an N+1 for a 5 row list of search results vs. "optimizing" to a JOIN for exactly this reason. Depending on the query plan, search limits, sort parameters, etc, the performance differences can be fairly non-intuitive. I don't think this explains your case, though, where N=20k. I'm not sure what could have been going on there.
ecopoesis 14y ago
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 14y ago
  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 14y ago
    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.
- einhverfr 14y ago
  Yeah I was writing a blog on long SQL queries and why a 200 line SQL query is a breeze to maintain compared to a 200 line Perl or C function. And not to mention your 200 line SQL query does a lot more than you can do in 200 lines in any other language. 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.
- typicalrunt 14y ago
  There must be a way to upvote this comment more than once... :) 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.

40 points by fatalmind 14y ago

lucian1900 14y ago

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 14y ago
How would you compare Korma to say.. ActiveRecord?
- lucian1900 14y ago
  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.

jiggy2011 14y ago

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 14y ago
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?
- fatalmind 14y ago
  Besides your points, we must also consider that MySQL does neither support hash joins nor sort/merge join. MySQL it just not very good at joining. Nevertheless, a MySQL join should never be slower than the N+1 select approach.
- jiggy2011 14y ago
  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.
jsight 14y ago
I do find it surprising that the results were identical. Having said that, my experience is that over low-latency connections N+1 selects are not as bad as they are often represented to be. I've occasionally chosen to accept an N+1 for a 5 row list of search results vs. "optimizing" to a JOIN for exactly this reason. Depending on the query plan, search limits, sort parameters, etc, the performance differences can be fairly non-intuitive. I don't think this explains your case, though, where N=20k. I'm not sure what could have been going on there.

ecopoesis 14y ago

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 14y ago
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 14y ago
  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.
einhverfr 14y ago
Yeah I was writing a blog on long SQL queries and why a 200 line SQL query is a breeze to maintain compared to a 200 line Perl or C function. And not to mention your 200 line SQL query does a lot more than you can do in 200 lines in any other language. 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.
typicalrunt 14y ago
There must be a way to upvote this comment more than once... :) 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.