Hacker News Reader

Comment by red_admiral | Hacker News Reader

This 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.

View on HN

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.

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();

I mean, you're definitely still writing a query in this case, just using a lazy-loading C# interface.

An ORM in the traditional sense abstracts the details of the query itself fully away in favor of an object-oriented interface.

EF Core does not lazy load by default. You have to explicitly include relations if you want them on an entity. You can enable lazy loading, but in the default configuration you usually don't have the N+1 problem.

Isn't Linq a lazy-loading interface by default? The example they gave was a Linq query.

I'm not a C# user in my professional life so I'm happy to be corrected, BTW, just citing the source of my misinterpretation

Yes, but in this context this just means that until you call ToList()/ToListAsync() you have an IQueryable. That represents a query, but isn't executed yet. Only at the point where you call a method like ToList (in this case First() is the relevant one) is the actual DB query performed.

The LINQ query in the comment above would only execute a single query like "SELECT name FROM users WHERE id = 123 LIMIT 1;" and would not even fetch the full entity, only the name.

Right, which was my point. They used Linq to get around the limitations of an ORM more-so than using an ORM itself (by fetching an entity and accessing its 'field'). They're evading the N+1 problem by writing a query by hand still, just not in SQL itself.

The bit about lazy loading was a bit of a side-note more than my main point.

But using LINQ to query stuff is a core part of this ORM. And even if you access the full entity, it won't do an N+1 in the default configuration. You have to explicitly call Include() on any relation you want to fetch and it'll either fetch all of them with one query or do one query per relation type. There's a different footgun here with AsSingleQuery() and AsSplitQuery(), but that's a separate topic.

Remember kids, writing queries in an ORM is a betrayal to the ORM gods.

You are supposed to manually count the question marks in your raw SQL like the JDBC gods intended.

    String sql = "INSERT INTO users (name, email, age, status, country) VALUES (?, ?, ?, ?, ?)";
    PreparedStatement pstmt = conn.prepareStatement(sql);
    pstmt.setString(1, "Alice");
    pstmt.setString(2, "alice@example.com");
    pstmt.setInt(3, 30);
    pstmt.setString(4, "ACTIVE");
    pstmt.setString(5, "US");

There is absolutely zero chance an ORM can ever get close to the performance of this query, so write it by hand!

After all, the primary promise of ORMs has been that you do not have to write queries, it's right in the name! Object Relational Mapper!

> You are supposed to manually count the question marks in your raw SQL like the JDBC gods intended.

    String sql = "INSERT INTO users (name, email, age, status, country) " +
                 "VALUES            (?,    ?,     ?,   ?,      ?      )";

But I get your point.

How does the (u => u.Id == 123) lambda parse/execute? The bad way would be to load all users from the DB and filter on the server, the good way is to send a WHERE clause to the DB in the first place.

I ask because `Where(u => ComplicatedBooleanFunction(u))` can't in general be transformed into something you can do in SQL, especially if it's stateful, but maybe there's a trick if the function is simple enough?

Some ORMs get around this problem with methods like .WhereEqual(User::id, 123) so you don't need to parse anything to know the semantics.

PS. I have in the past got praised at work for massively speeding up database access, when I fixed something like `List<User> userList = UserDAO.fetchAll(); return userList.length();`. You need a minimal understanding of databases to use an ORM correctly. The DAO even had a `.count()` method, but you need to know a little about databases to know to look for that in the first place.

In EF Core this will almost certainly generate a query like "SELECT name FROM users WHERE id = 123 LIMIT 1;". The LINQ expression is transformed into SQL, and any expression that can't be transformed will cause an error to be thrown.

Certain methods like First() in this case materialize expressions. Only those trigger a DB query.

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.

Or set lazy loadin to "raise" in the relationships and get exceptions if you dont explicitly join.

Is usually the right answer!

You should write a raw SQL query to grab just a user's name only when there's a need for that.

> all you want is their name

I have a WIP PR that addresses exactly this:

https://github.com/joist-orm/joist-orm/pull/1967

Reply on news.ycombinator.com