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.