• 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
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

Performance tuning to select two whole tables for one-to-one mapping

 
Greenhorn
Posts: 9
Opera Spring Flex
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi All,
I am facing performance issue while trying to fetch all the records (almost 24,000 rows) from 2 tables in oracle.

There are two tables: FinancialEntity and Company. 'FinancialEntity' table contains the foreign key for 'Company' table as 'company_id' which is referenced in id column in Company table. There are company name etc I want to read from company table and from FinancialEntity table, I need to fetch 2/3 columns.

So in my hibernate mapping, in the java bean, I have created one Company Object. Please note, for one financial entity id, there should be only one company only. So in the financialentity.hbm.xml file, I have provided one 'many-to-one' mapping for company. (This I did, based on recommendation found in some website. To me, I thought it would be one-to-one mapping. The reason is not clear to me till now )

When I am running the HQL query as 'from financialentity' it is taking an enormous amount of time. Then I specified the columns from FinancialEntity table and the company object. The performance is better, but since it needs to fetch all the columns for company, it is taking solid 2-3 min to get the whole set of records.

I tried to put the join query, but that is also taking more than 2 minutes just to populate the whole list. While in the Oracle editor, it is taking only 1/2 sec to populate the whole records.

Any suggestion would be highly appreciated.

Please let me know, if you need me to post the snippet from my xml, bean and DAO class.
 
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


When I am running the HQL query as 'from financialentity' it is taking an enormous amount of time. Then I specified the columns from FinancialEntity table and the company object. The performance is better, but since it needs to fetch all the columns for company, it is taking solid 2-3 min to get the whole set of records.

.
Sounds like you are not giving your application enough memory. 24000 entities is quite a lot of data to pull into the session, so you'll need decent heap space. Have you considered paging?

 
sankha ghosh
Greenhorn
Posts: 9
Opera Spring Flex
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
No, I haven't. Actually the requirement is to display the whole list together. The memory should not be a problem.

But since you brought the point, I can think about it. In that case, could you please provide me some idea, how to proceed, and how to use hibernate for that?
 
Paul Sturrock
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

sankha ghosh wrote:No, I haven't. Actually the requirement is to display the whole list together. The memory should not be a problem.


Looks like it may be. From the database point of view, there is no difference in performance at all returning two columns or all columns, so we can assume it is some other bottleneck. 24000 entities makes me assume this is a bulk data processing application, yes? If so its proably worth mentioning that ORMs in general are poor choices for bulk data manipulation.


But since you brought the point, I can think about it. In that case, could you please provide me some idea, how to proceed, and how to use hibernate for that?


Have a look at this article.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic