File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Object Relational Mapping and the fly likes Performance tuning to select two whole tables for one-to-one mapping Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Performance tuning to select two whole tables for one-to-one mapping" Watch "Performance tuning to select two whole tables for one-to-one mapping" New topic
Author

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

sankha ghosh
Greenhorn

Joined: Jun 12, 2007
Posts: 9

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 :confused

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.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


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?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
sankha ghosh
Greenhorn

Joined: Jun 12, 2007
Posts: 9

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

Joined: Apr 14, 2004
Posts: 10336

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Performance tuning to select two whole tables for one-to-one mapping