alt Hacker NewsThis is what you get when you let your ORM loose on the database without understanding JOINs. Especially, the bit where something like 'book.author.name' that looks like a simple field dereference actually is a method call on an ORM proxy object (book), via python's __getattr__ or similar, that fires off a new query if the data you want is not loaded yet.
Some ORMs let you specify the extent of the data that you want, like Hibernate has its own Hibernate Query Language.
At some point you are better off just writing SQL yourself, though. Even without join problems, if you ask an ORM to get the person with user id 123 and all you want is their name, the ORM cannot know that unless to tell it, and so you end up with a 'SELECT *' type query.
Replies
People always say this and my experience in C# has been the opposite. I've never wanted to reach for raw SQL and hardly ever do, only if its because there's no extension to do SKIP..LOCKED or something
var name = dbContext.Users .Where(u => u.Id == 123) .Select(u => u.Name) .First();
> all you want is their name
I have a WIP PR that addresses exactly this:
ORMs are great. They make the easy queries remain easy and the harder queries impossible.
This is true but not super true in case of linq and related providers like efcore. Even nhibernate linq would do this.
You should write a raw SQL query to grab just a user's name only when there's a need for that.
Or set lazy loadin to "raise" in the relationships and get exceptions if you dont explicitly join.
Dealing with Django, they give quite a few ways to query data. For the small internal web site we ran, I've yet to encounter an N+1 situation that I didn't find an alternative Django API that I should have been using.
Not saying you never need bare SQL on Django sites, but the Django ORM does have some sophisticated APIs to prevent this problem.
> Even without join problems, if you ask an ORM to get the person with user id 123 and all you want is their name, the ORM cannot know that unless to tell it
So ... tell it! You can specify in a query to fetch only certain data, and not the whole object.