NHibernate Performance

mummu · **Joined:** Thu Aug 27, 2009 6:28 am **Posts:** 19

Hi Friends,

I am using the HQL query to retieve the Data from database and fill it in the objects/Collections. the table has related with more than 5 tables from that 3 would be an large table(like more than 50 lakhs record), i need to get some selected values from all the tables. i cannot able to select the particular field and store in the entity. so i selected top entity. so it was too slow.. for fetching 2000 records it took 3mins... so how can i improve the performance.
Could you please give me the example.

wolli · **Posted:** Fri Apr 02, 2010 4:47 am

Sounds more like a database problem. How is the perfomrance of the generated SQL when you run it in SQL management studio.

mummu · **Joined:** Thu Aug 27, 2009 6:28 am **Posts:** 19

that is fetching the record within 45 seconds... i dont know whether is the problem in writing the hql query. can we get the performance of fetching the record like sql ?

can we use N Hibernate in the large scale application like i.e., insurance , banking projects where performance is main concern.

wolli · **Posted:** Fri Apr 02, 2010 5:53 am

Can you post the HQL and the mappings ?

mummu · **Joined:** Thu Aug 27, 2009 6:28 am **Posts:** 19

Hi wolli,
The below is my hql Query

Code:

Select distinct c from BenefitDtlsDto bd join bd.Claims c join c.ClaimsProc cp join c.AdjClaimDtls ad where 1=1 and c.MemberBenefitId=bd.PrimaryKey and cp.DateService  between '01/01/2009' and '01/30/2009' and c.ProgramId =1 and ad.OfficeId =10510

I have an entity with the name of BenefitDtlsDto and it has mapping with the 3 tables. all the table has more than 25 Lakh record.

My question is

Quote:

Like banking or large scale application, can we get the very good performance while fetching the record when compare to SQL Procedures/Command(Compiled Statement)

.

wolli · **Posted:** Mon Apr 05, 2010 7:53 am

Try

Select distinct c from BenefitDtlsDto bd join fetch bd.Claims c join c.ClaimsProc cp join fetch c.AdjClaimDtls ad where 1=1 and c.MemberBenefitId=bd.PrimaryKey and cp.DateService between '01/01/2009' and '01/30/2009' and c.ProgramId =1 and ad.OfficeId =10510

and have a look here:

http://nhforge.org/doc/nh/en/index.html#queryhql-joins
http://nhforge.org/doc/nh/en/index.html#performance-fetching