FROM Category c JOIN Product p on C.Id = P.CategoryId
This will run fairly fast. An ORM, however, will likely do something like this:
SELECT * FROM Category
SELECT * FROM Product WHERE CategoryId = ?
That's very inefficient. The problem that has been showcased here is commonly referred to as the N+1 Select problem. The problem is that, for every record in the initial select, we are running an additional select to retrieve data that we need that was not retrieved with the initial select.
Most ORMs will give you a way to handle this gracefully. However, since the ORM is abstracting the SQL call, you may not discover the issue until you have a full-sized db that is experiencing performance problems. The larger the N, the worse the performance. Some people call this ripple loading. Others call it the N+1 SELECTs Problem. Hibernate specifically warns developers about the N + 1 problem. But what is their solution?
Using lazy initialization can help prevent unnecessary selects queries being executed when retrieving an object graph using HQL or Query by Criteria (QBC). However, to avoid the N+1 selects proble, the application developer needs to be careful to tune queries so that they match the way returned data is used in the application.
(Emphasis in original). So again, using an ORM still means understanding your data model, understanding query tuning, understanding SQL, etc. So, what did the ORM buy us?
Dave Wentzel CONTENT
data architecture orm