5 ms·
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 o
by 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 agoWhen 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 agoBesides 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 agoThe 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 agoI 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.
- mdasen 14y agoI have one possibility of what might be going on. 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. . . So, you're potentially pulling back a ton of excess data. Phone 1 will appear 5 times in the result set and then the ORM has to de-duplicate that. Likewise, Address 1 shows up 5 fimes in the result set and John shows up 5 times in the result set and the ORM needs to deal with it. Likewise, it's a lot of excess data over the wire and a lot of excess data to even process into a hash. 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.