5 ms·

There is lots of truth to this. Some ORMs like Django perform joins in very unsuspecting ways. A simple example is, say, foreign keys. Trying to access the for

There is lots of truth to this. Some ORMs like Django perform joins in very unsuspecting ways. A simple example is, say, foreign keys. Trying to access the foreign key of an object by doing `book.user.id` does an additional query for the user table to get the ID. It's less known that the id is immediately available by just doing `book.user_id` instead. I've spent time optimising things like text searches down from 2000+ queries to about ~4, and one of the more noticeable things to me isn't actually the number of joins, rather the SELECT's that take place. Many of these ORMs do a SELECT * unless you explicitly tell them to otherwise, and when dealing with large-ish datasets or on models that have large text fields this translates into significant time taken to serialise these attributes. So you can optimise the query and still have it take a long time until you realise that limiting the initial `SELECT` parameter is probably more efficient than limiting the number of joins.

paulmd 6y ago
> A simple example is, say, foreign keys. Trying to access the foreign key of an object by doing `book.user.id` does an additional query for the user table to get the ID. It's less known that the id is immediately available by just doing `book.user_id` instead. Hibernate (on Java) at least optimizes this specific use-case. At first, accessing a lazy-loaded property-object will give you a "proxy" and you can access the ID without incurring a database load (since it knows that anyway). And when doing a query, the object won't be joined when requesting book.user.id unless it needs to be (like you have some other WHERE clause that requires an actual join on that row).
bottled_poe 6y ago
> A simple example is, say, foreign keys. Trying to access the foreign key of an object by doing `book.user.id` does an additional query for the user table to get the ID. It's less known that the id is immediately available by just doing `book.user_id` instead. Hmm.. Sounds like a bug. Why is this not the same value for a foreign key?
- radicalism 6y ago
  It would return the same value, but the approach to obtain it would be different. `user` would be a property defined as a User object on the Book model, so accessing `book.user` will cause the framework to fetch the entire user model (even if we then only fetch the id). On the other hand, `book.user_id` is the auto-generated database column, generated to make the above property definition possible. But since this `user_id` is directly defined on the book object, there is no need to query the user table.
- rglullis 6y ago
  Because when you are acessing .user you are asking for all its properties, not just the id. Django does provide relatively easy ways to get over the N+1 issue, though. If you do Book.objects.select_related('user'), only one query is made.
  - Hamuko 6y ago
    `QuerySet.select_related()` and `QuerySet.prefetch_related()` are the bread and butter of Django query optimisation. I think most of the time that I've noticed a performance issue in our code, it's been easily fixed with one of those.
    - rglullis 6y ago
      Django's ORM gets a lot of flak, but I don't remember the last time I had complex queries that I could not do with it. You still need to understand a minimum of SQL and databases, and usually those that complain about the ORM are the ones that expect it to be a "sufficiently advanced compiler", but it has matured so much that nowadays the developers consider a *bug* every time the answer to How do I do this query X? involves something along the lines of use .extra or raw sql.
      - RussianCow 6y ago
        This is true, though to be fair to the critics, the syntax through which you express these complex queries is often clunky and unintuitive. For example, I need to re-read the documentation every time I use the annotation API because it's generally not obvious how to use it, and I've run into a few edge cases where you need extra code/syntax just to deal with its nuances and ambiguities. Even though Django has come a long way, I greatly prefer ORMs like SQLAlchemy and Ecto that map more closely to the SQL query I'm trying to write.
grw_ 6y ago
I encountered this a few times and started adding tests that assert each handler only executes the expected number of queries (and no more). If the application code is modified such that this N+1 query pattern occurs the test will immediately fail and you go optimise the query, problem solved. https://docs.djangoproject.com/en/dev/topics/testing/tools/#django.test.TransactionTestCase.assertNumQueries https://docs.djangoproject.com/en/dev/topics/testing/tools/#...
- drunkpotato 6y ago
  Or the person who changed the code then disables the test or sets N to 100,000,000 or something equally pleasurable to debug.
gregmac 6y ago
The most insidious part about misusing ORMs is it's often not visible for a while. Modern DBMSs on modern hardware are crazy fast, so when you have only a few tens or hundreds of thousand rows in your table, those inefficient and pointless ORM queries are just not noticeable because you still get sub-second response times. As your database grows, the site begins to gets slower and slower, but it's hard to distinguish between the real problem and "I guess we're just handling more requests per second". I personally love tools like Miniprofiler [1] for this (though maybe there's something better today, it's been a while since I've worked on that type of thing). It's a constant and accessible way to keep an eye on what goes into each request, and I've caught many of those bad queries before they were problems by using it (eg: "WTF, why did it take 9 queries and 250ms to grab what looks to be a single row from a single table?!"). [1] https://miniprofiler.com/ https://miniprofiler.com/
- zzzeek 6y ago
  To be fair this is a problem inherent to databases in general. You can have hand written queries that perform badly due to structure or query frequency as well which are not apparent until the dataset grows. The ORM should make it easier to rectify such situations (eg drop in an eager loading directive) vs having to restructure hand-written routines for similar effects.
  - magicalhippo 6y ago
    Indeed, even with query analyzer you might see say table scans instead of index scan just because the DB realizes just scanning the 100 rows you got is faster than trying to use an index. So without a large number of rows it can be hard to know what it will actually do.
- fhood 6y ago
  We have everything hooked into lightstep. Makes it extremely easy to track down problematic operations.
airstrike 6y ago
> ...in very unsuspecting ways > Trying to access the foreign key of an object by doing `book.user.id` does an additional query for the user table to get the ID. It's less known that the id is immediately available by just doing `book.user_id` instead. But that's not really unsuspecting. `book.user` is asking for the user table, `book.user_id` is not. Those two things are not identical even though they return the same value.
- cztomsik 6y ago
  IIRC spark can optimize it away, and in OO there shouldn't be user_id
  - airstrike 6y ago
    > in OO there shouldn't be user_id I don't think that's right. There is a user_id column in the book table, so why shouldn't there be book.user_id?
    - cztomsik 6y ago
      depends if you're doing active record (which I am not considering as typical ORM) or data mapper like hibernate where your entity is POJO with id and annotations but typically no foreign keys (just references to other model classes) hibernate is then "the magic "environment where it just works" I realize OO is out of fashion now but it's still true and it still works and I've been in a lots of projects where ORM was useful
bcrosby95 6y ago
If you're selecting tons of data when you SELECT * you might also have a god object. I prefer to have my model be a bit more split up by use rather than being full of random stuff. E.g. a customer_address table rather than stuffing all that data into customer, even if they only have a single address.
robertlagrant 6y ago
I'm pretty sure Django has an active record ORM, which are generally a bit rubbish in terms of performance. A unit of work ORM such as SQLAlchemy seems to generate much better queries.

paulmd 6y ago

> A simple example is, say, foreign keys. Trying to access the foreign key of an object by doing `book.user.id` does an additional query for the user table to get the ID. It's less known that the id is immediately available by just doing `book.user_id` instead. Hibernate (on Java) at least optimizes this specific use-case. At first, accessing a lazy-loaded property-object will give you a "proxy" and you can access the ID without incurring a database load (since it knows that anyway). And when doing a query, the object won't be joined when requesting book.user.id unless it needs to be (like you have some other WHERE clause that requires an actual join on that row).

bottled_poe 6y ago

radicalism 6y ago
It would return the same value, but the approach to obtain it would be different. `user` would be a property defined as a User object on the Book model, so accessing `book.user` will cause the framework to fetch the entire user model (even if we then only fetch the id). On the other hand, `book.user_id` is the auto-generated database column, generated to make the above property definition possible. But since this `user_id` is directly defined on the book object, there is no need to query the user table.
rglullis 6y ago
Because when you are acessing .user you are asking for all its properties, not just the id. Django does provide relatively easy ways to get over the N+1 issue, though. If you do Book.objects.select_related('user'), only one query is made.
- Hamuko 6y ago
  `QuerySet.select_related()` and `QuerySet.prefetch_related()` are the bread and butter of Django query optimisation. I think most of the time that I've noticed a performance issue in our code, it's been easily fixed with one of those.
  - rglullis 6y ago
    Django's ORM gets a lot of flak, but I don't remember the last time I had complex queries that I could not do with it. You still need to understand a minimum of SQL and databases, and usually those that complain about the ORM are the ones that expect it to be a "sufficiently advanced compiler", but it has matured so much that nowadays the developers consider a *bug* every time the answer to How do I do this query X? involves something along the lines of use .extra or raw sql.
    - RussianCow 6y ago
      This is true, though to be fair to the critics, the syntax through which you express these complex queries is often clunky and unintuitive. For example, I need to re-read the documentation every time I use the annotation API because it's generally not obvious how to use it, and I've run into a few edge cases where you need extra code/syntax just to deal with its nuances and ambiguities. Even though Django has come a long way, I greatly prefer ORMs like SQLAlchemy and Ecto that map more closely to the SQL query I'm trying to write.

grw_ 6y ago

I encountered this a few times and started adding tests that assert each handler only executes the expected number of queries (and no more). If the application code is modified such that this N+1 query pattern occurs the test will immediately fail and you go optimise the query, problem solved. https://docs.djangoproject.com/en/dev/topics/testing/tools/#django.test.TransactionTestCase.assertNumQueries https://docs.djangoproject.com/en/dev/topics/testing/tools/#...

drunkpotato 6y ago
Or the person who changed the code then disables the test or sets N to 100,000,000 or something equally pleasurable to debug.

gregmac 6y ago

The most insidious part about misusing ORMs is it's often not visible for a while. Modern DBMSs on modern hardware are crazy fast, so when you have only a few tens or hundreds of thousand rows in your table, those inefficient and pointless ORM queries are just not noticeable because you still get sub-second response times. As your database grows, the site begins to gets slower and slower, but it's hard to distinguish between the real problem and "I guess we're just handling more requests per second". I personally love tools like Miniprofiler [1] for this (though maybe there's something better today, it's been a while since I've worked on that type of thing). It's a constant and accessible way to keep an eye on what goes into each request, and I've caught many of those bad queries before they were problems by using it (eg: "WTF, why did it take 9 queries and 250ms to grab what looks to be a single row from a single table?!"). [1] https://miniprofiler.com/ https://miniprofiler.com/

zzzeek 6y ago
To be fair this is a problem inherent to databases in general. You can have hand written queries that perform badly due to structure or query frequency as well which are not apparent until the dataset grows. The ORM should make it easier to rectify such situations (eg drop in an eager loading directive) vs having to restructure hand-written routines for similar effects.
- magicalhippo 6y ago
  Indeed, even with query analyzer you might see say table scans instead of index scan just because the DB realizes just scanning the 100 rows you got is faster than trying to use an index. So without a large number of rows it can be hard to know what it will actually do.
fhood 6y ago
We have everything hooked into lightstep. Makes it extremely easy to track down problematic operations.

airstrike 6y ago

> ...in very unsuspecting ways > Trying to access the foreign key of an object by doing `book.user.id` does an additional query for the user table to get the ID. It's less known that the id is immediately available by just doing `book.user_id` instead. But that's not really unsuspecting. `book.user` is asking for the user table, `book.user_id` is not. Those two things are not identical even though they return the same value.

cztomsik 6y ago
IIRC spark can optimize it away, and in OO there shouldn't be user_id
- airstrike 6y ago
  > in OO there shouldn't be user_id I don't think that's right. There is a user_id column in the book table, so why shouldn't there be book.user_id?
  - cztomsik 6y ago
    depends if you're doing active record (which I am not considering as typical ORM) or data mapper like hibernate where your entity is POJO with id and annotations but typically no foreign keys (just references to other model classes) hibernate is then "the magic "environment where it just works" I realize OO is out of fashion now but it's still true and it still works and I've been in a lots of projects where ORM was useful

bcrosby95 6y ago

If you're selecting tons of data when you SELECT * you might also have a god object. I prefer to have my model be a bit more split up by use rather than being full of random stuff. E.g. a customer_address table rather than stuffing all that data into customer, even if they only have a single address.

robertlagrant 6y ago

I'm pretty sure Django has an active record ORM, which are generally a bit rubbish in terms of performance. A unit of work ORM such as SQLAlchemy seems to generate much better queries.