• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Optimized SQL with ORM

 
Bartender
Posts: 4116
72
Mac TypeScript Chrome Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
author
Posts: 32
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.
 
Vijitha Kumara
Bartender
Posts: 4116
72
Mac TypeScript Chrome Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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]
 
Author
Posts: 12617
IntelliJ IDE Ruby
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
*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
Posts: 32
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic