aspose file tools*
The moose likes Object Relational Mapping and the fly likes Very slow fetching Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Very slow fetching" Watch "Very slow fetching" New topic
Author

Very slow fetching

Maciej Opala
Ranch Hand

Joined: Jul 18, 2011
Posts: 38
Hi,
I have 4 tables in DB:
- A1 one-to-one with A2 with mapping fetched with the following query
and
- C1 one-to-one with C2 with mapping fetched with the following query
From both results I construct a result object. There's also no relation (in hibernate) between A1 and C1.

The problem is that it takes about 12 seconds to fetch 500 (A1 with A2) and 2500 (C1+C2) records from the db. Hibernate constructs single query for each 'get' operation instead of (?) fetch all rows and process them on java side. How to tune this?
I also have the following properties set in my configuration:


Any help would be greatly appreciated..


Bill Gorder
Bartender

Joined: Mar 07, 2010
Posts: 1632
    
    7

Why is your pool size set to 1? I hope you are not using hibernates pooling algorithm. This is not meant for production systems. See the documentation here:
http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/session-configuration.html#configuration-hibernatejdbc

I would also make sure that the columns you are querying are on are properly indexed. I would run the execution plan and make sure you don't have full table scans happening. Probably speak to a DBA if you have one available.


[How To Ask Questions][Read before you PM me]
Maciej Opala
Ranch Hand

Joined: Jul 18, 2011
Posts: 38
Bill Gorder wrote:Why is your pool size set to 1? I hope you are not using hibernates pooling algorithm. This is not meant for production systems. See the documentation here:
http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/session-configuration.html#configuration-hibernatejdbc


To be honest I don't know what pooling algorithm is in use. Default for Hibernate 4.1. What should be the pool size? Thanks for the link, will check.

I would also make sure that the columns you are querying are on are properly indexed. I would run the execution plan and make sure you don't have full table scans happening. Probably speak to a DBA if you have one available.


Actually, the columns are not indexed at all right now. Will also check.
Thanks for being helpful.
Maciej Opala
Ranch Hand

Joined: Jul 18, 2011
Posts: 38
Have set the following properties:

Hasn't helped even a bit but I guess it should be left as is right now. Will try indexing later.
rohit chavan
Ranch Hand

Joined: Oct 08, 2010
Posts: 131



Any specific reason to use here?.
Bill Gorder
Bartender

Joined: Mar 07, 2010
Posts: 1632
    
    7

the connection pool is not going to help for improving the performance of a single query, but that does not mean you do not need to address the issue. A pool size of 10 is probably good to start with. I am assuming that your database is application managed here, if its not (i.e container managed) your application should not be doing anything with pooling and you should remove that pool size property altogether.

I would refrain from making too many setting changes with the other properties until you run an explain plan and properly index your columns. This is most likely going to have the biggest pay off for you. As for the Eager vs Lazy, eager is fine if you are going to need those objects. Fetching them eagerly is more efficient that executing multiple selects later to load the data. However if the size of your result set is to large you will need to play around with fetch size at that point, but for now I would reduce your configuration to bare minimum and just use the defaults and start with indexing your columns. Once that is done if there is still a problem then start tinkering. There is a performance tuning section in the hibernate documentation as well.

Maciej Opala
Ranch Hand

Joined: Jul 18, 2011
Posts: 38
Thanks for all the valuable advice. Will check in the nearest future (now I'm fighting with another part of my hibernate application).

I use "EAGER" because I build a result object (consisting of A1 and A2) at once in the place I fetch the data.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: Very slow fetching
 
Similar Threads
populating @OneToOne from query
Persistence in the Enterprise - efficiency
@PrimaryKeyJoinColumns does not work
OneToOne composite key
JPA2.0 CascadeType.ALL doesn't cascade persist