aspose file tools*
The moose likes Object Relational Mapping and the fly likes How to approach queries Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "How to approach queries" Watch "How to approach queries" New topic
Author

How to approach queries

Carlos Conti
Ranch Hand

Joined: Apr 21, 2010
Posts: 113
Hi there,

I am having the question wether I should query result as objects, or List<Object[]> (Object[] return values array per record), when calling Hibernate queries. Hibernate is supposed to have very optimization options when dealing with queries with several joins, but in my case retrieving Object[] is the best performing option. I have tried to use LEFT JOINS too, but the result doesn't improve so much. Queries still take ages to execute (more than 1 minute), whereas the Object[] ones take 2 seconds to update results on screen.

Does any one know any good tutorial where I can get some fresh ideas. Certainly retrieving objects has many advantages.

Many thanks in advance.

Carlos.
Raul Guerrero
Greenhorn

Joined: Apr 13, 2009
Posts: 7
Carlos Conti wrote:
I am having the question wether I should query result as objects, or List<Object[]> (Object[] return values array per record)


Hi Carlos, according to JPA, when you have more than one result you have to use the collections API, so you have the option of fetching results as List, Map or Set, also, you define that on your entities.
If you are worried about the amount of queries that hibernate generates, then I should recommend you do the queries yourself using something else, even though ORMs have advanced a lot, they still tend to generate unnecesary garbage queries, and you have no way of optimizing them by hand.
Carlos Conti
Ranch Hand

Joined: Apr 21, 2010
Posts: 113
Hi Raul,
thanks for your quick answer. Just to make the point clear, when I say List<Object[]> as a result, I mean with the 'Object[]' part, a record filled with primitive values, suchas as Strings, Integers or Booleans. I am not constructing an object at all, since in the query I am only SELECTing field values. I get the result as a List, but I do not specify in the query what sort of aggregate object the result is supposed to be (List/Set/...). Would that make a difference when it is specified inside the query passed to hibernate?

The main shortcut I have when retrieving results this way is that I cannot call class methods on the obtained results, which facilitate a lot some reports.

Another idea I thought about was to fetch fields individually, so I could mark as Lazy those fields that were not to appear in the report. Do you thinq that could improve performance? I ask so, because the JOIN's do not seem to be the problem, since my currently modified queries have exactly the same JOIN's in them, and the performance boosts!. So I think it is more an issue related to object creation.

Any ideas are very welcome.

Many thanks again.
Carlos.

Raul Guerrero
Greenhorn

Joined: Apr 13, 2009
Posts: 7
It would be good if you placed an actual sample code to try what you mean, as you aren't creating any entities, and an ORM is based on entities.

If you want to be able to get only primitives from a query then why use an ORM? another way would be to create an entity that works as a wrapper for your primitives.

Also, as you are stating, if this is for reporting, then definitely you should use pure SQL queries instead of ORM for getting the best out of your RDBMS and your reporting engine, and you will start bumping into issues when your users start building a lot or complex reports and your java app will have to process all of the ORM stuff instead of just getting the info from the DB. And if you aren't using a reporting engine, I really recommend that you should, there are really good opensource ones like jasper reports.
Carlos Conti
Ranch Hand

Joined: Apr 21, 2010
Posts: 113
Hi Raul,

this is not purely for reporting purposes. Everything works within an application, but sometimes a big bunch of data must be processed and have realized that when retrieving entities it takes so long!. Not using ORM is not an option. I am just trying to reduce the time it takes some of my queries to finish. For those I had to retrieve the primitive values I specifically needed in each case, instead of their container entities if I wanted the query to run fast. That's the point.

I believe it will have to do with the way I fetch the values. If I fetch eagerly only those values to be displayed in my reports it should be faster isn't it? Is there any way to see while debugging which values are lazyly or eagerly fetched?

Thanks again.
Carlos.
Raul Guerrero
Greenhorn

Joined: Apr 13, 2009
Posts: 7
Carlos Conti wrote:Hi Raul,

this is not purely for reporting purposes. Everything works within an application, but sometimes a big bunch of data must be processed and have realized that when retrieving entities it takes so long!. Not using ORM is not an option. I am just trying to reduce the time it takes some of my queries to finish.


Ok if you really want to use an ORM, why don't you use a strategy like entity per table? that way, you can do fast queries to a certain table and then only add relations where you really need it?

and Yes, using eager and lazy in certain places for the object fetching can really speed things up, also try to do a lot of caching, I don't know about Hibernate parameters, but on eclipselink you have tags for your persistence.xml like eclipselink.jdbc.cache-statements, or also, if you're using JPA2, there are tags like <shared-cache-mode> that can help you do some query statement caching to avoid overhead.

Check out the Hibernate documentation depending on the version you're using, and caching really helps out a lot.

EDIT: Also for debugging check the documentation, sorry I can't help with hibernate specifics, but for eclipselink I use tags like:
<property name="eclipselink.logging.logger" value="DefaultLogger"/>
<property name="eclipselink.logging.level" value="FINE"/>
And that writes out to the output or your App server log the queries is executing, that way you'll know if a query is executed right away, or if it is queued to be executed later.
Carlos Conti
Ranch Hand

Joined: Apr 21, 2010
Posts: 113
Hi Raul,

thanks for your insights. I'll have a check with the lazy loading for certain fields, to see if that speed things up.


Thanks again for this discussion.

Regards,

Carlos.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to approach queries