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

[sankha ghosh]

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.

[Paul Sturrock]

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]

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]

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.