It depends on the ORM tool you are using. The main thing to remember is to make sure you configure mappings (however that is done in your particular ORM) and queries to fetch only the data you need and cache data judiciously.

Vijitha Kumara wrote:What are the key areas (if any identified) an ORM would fail in generating an optimized query?

Here's an example. Glenn Paulley, Director of Engineering for Sybase iAnywhere gave a presentation for Sybase Techwave Symposium last year titled "Object Relational Mappers: Friend or Foe?" He showed an example of a simple SQL query that you might write if you were writing it by hand:

But after being managed by an abstraction layer (LINQ in this case) the SQL query generated was:

You could probably stare at that SQL query above and factor out superfluous subqueries that do nothing. Eventually you would factor out all the useless code and restore it to the simple form of the query. But the ORM isn't smart enough to do that analysis. The problem is that as ORMs are enhanced to handle complex data requests, they add boilerplate code to every SQL query they generate. This tends to make every data request into a complex SQL query.

Yes, it depends on the tool, but most of the tools (from different vendors) must have followed common patterns most of the time in their implementations I guess. I was thinking whether there are any common scenarios where a tool may not be able to produce optimized queries.

[EDIT]Thanks Bill. You had given an answer already while I'm replying. [/EDIT]

*shudder* at the LINQ code. I'm curious what code lead to that SQL, because I rarely see Hibernate generate such a monstrosity.

Bill Karwin

author
Ranch Hand

Posts: 32

posted 6 years ago

Hi David,

In his presentation, he didn't show the LINQ code that produced that blob of SQL. But it's an extreme example anyway. The point is that abstraction layers have to be general-purpose, so in any situation besides the utterly trivial SELECT * FROM Table, ORM's can't optimize for a given situation as well as a human can.

That's why I say that ORMs are generally for developer productivity, not code efficiency. It could still be a win if 80% of your code uses the SELECT * FROM Table type of query, but you should assume that you'll need to bypass the ORM for your remaining queries. If we were to accept that, the ORM could be smaller and simpler, and it wouldn't be as likely to make a mess out of the simple queries, as in the LINQ example above.