aspose file tools*
The moose likes JDBC and the fly likes Optimized SQL with ORM Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Optimized SQL with ORM" Watch "Optimized SQL with ORM" New topic
Author

Optimized SQL with ORM

Vijitha Kumara
Bartender

Joined: Mar 24, 2008
Posts: 3860

Hi Bill,

There are few topics already talking about ORM... I have mine What are the key areas (if any identified) an ORM would fail in generating an optimized query?


SCJP 5 | SCWCD 5
[How to ask questions] [Twitter]
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

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.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Bill Karwin
author
Ranch Hand

Joined: Aug 02, 2010
Posts: 32
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.


Bill Karwin is the author of SQL Antipatterns: Avoiding the Pitfalls of Database Programming
Vijitha Kumara
Bartender

Joined: Mar 24, 2008
Posts: 3860

Paul Sturrock wrote: It depends on the ORM tool you are using........

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]
David Newton
Author
Rancher

Joined: Sep 29, 2008
Posts: 12617

*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

Joined: Aug 02, 2010
Posts: 32
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Optimized SQL with ORM