Improving fetching speed

getmerohit · **Joined:** Mon Nov 10, 2008 3:46 am **Posts:** 12

I have a large database having 1 million records and I want to fetch each record(so my query is: select * from SampleCase) do some calculation and save it at some other place. I am sure that I will need the complete tree structure..
What is happening now is that the hibernate executes select query for each row of data. I tried to use pagination but there also select query is getting executed for each row and not in a batch.
I want that a single query give me a small number of rows say 100 in one go so that for each row select need not be executed. Is it possible to do so?

<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class entity-name="SampleCase" table="SAMPLECASE">
<id name="IRRELEVANT_NAME0" column="ID" type="string">
<generator class="native"/>
</id>
<property name="sample" type="double" ></property>
<one-to-one name="policy" cascade="all" fetch="join" class="AutoPolicy"/>
</class>
<class entity-name="AutoPolicy" >
<id name="id" column="ID" type="string">
<generator class="native"/>
</id>
<one-to-one name="Address" cascade="all" fetch="join" class="Address"/>
<one-to-one name="Customer" cascade="all" fetch="join" class="Customer"/>
<property name="EffectiveDt" type="date" ></property>
<list name="GeneralQuestions" cascade="all" fetch="join" >
<key>
<column name="ID"/>
</key>
<list-index column="INDEX1"/>
<one-to-many class="GeneralQuestions"/>
</list>
<list name="VehicleInfo" cascade="all" fetch="join" >
<key>
<column name="ID"/>
</key>
<list-index column="INDEX1"/>
<one-to-many class="VehicleInfo"/>
</list>
</class>
...
...

Also it would be helpful to know if there are other configurations which can help improve fetching speed.

pkleindl · **Joined:** Thu Jan 08, 2009 3:48 pm **Posts:** 168

- Paging
You can use .setFirstResult() and .setMaxResults() with the Query.

Code:

session.createQuery("from SampleCase").setFirstResult(1).setMaxResults(100)

=> Fetches 100 records from main table, just increase FirstResult by 100 each iteration

- Enable Batch Fetching

Code:

hibernate.default_batch_fetch_size = x

where x could be 8/16/etc
=> Fetches more than one row at a time

- Object instantiation
If you do not need the complete SampleCase object but only some values you can return them with

Code:

session.createQuery("select sc.id, sc.prop1, sc.prop2 from SampleCase sc")

as a List<Object[]> where each [] can be cast to the object type
This can save a lot of time and memory if you have complex objects, although you loose the option of object references (but it can be combined with joines to load values from other tables too)

Hope that helps, rating appreciated

mmerder · **Posted:** Tue Feb 03, 2009 8:08 am

try batch-fetching.
To enable batch-fetching you have to enable it via hibernate-configuration (set hibernate.jdbc.batch_size and hibernate.default_batch_fetch_size).

You can do that in persistence.xml (if you are using JPA) or in hibernate.cfg.xml.

mmerder · **Posted:** Tue Feb 03, 2009 8:10 am

double post.

getmerohit · **Joined:** Mon Nov 10, 2008 3:46 am **Posts:** 12

Thank you pkleindl and mmerder for your quick replies, but I already tried paging but it still used different select query to fetch the data.
I tried increasing hibernate.default_batch_fetch_size from 1 to 8 but it slowed down fetching much more. hibernate.jdbc.batch_size is set at 15. Is there some connection between these two values? I mean is there some convention that both should be equal or any such rule?
I am just executing the query and then using iterate to move across the data with lazy fetching on.

pkleindl · **Joined:** Thu Jan 08, 2009 3:48 pm **Posts:** 168

getmerohit wrote:

I tried increasing hibernate.default_batch_fetch_size from 1 to 8 but it slowed down fetching much more.

Are you sure batch fetch slowed down processing?
Can you enable SQL Log and post the output with and without batch fetching?

You can combine paging with joining other tables

Code:

session.createQuery("from SampleCase sc inner join fetch sc.details d inner join fetch sc.moreDetails md left join fetch md.oneMoreTable omt").setFirstResult(1).setMaxResults(100)

the fetch keyword makes the other tables/object eager loaded in one go

You just have to be careful to only join FETCH to only use to-one associations, otherwise (to-many) Hibernate will do the paging in memory, which will of course be rather bad for you app

Rating appreciated

getmerohit · **Joined:** Mon Nov 10, 2008 3:46 am **Posts:** 12

I only changed hibernate.default_batch_fetch_size value in the config file and the time taken by my application increased from 2 mins to ~8 mins.

When using default_batch_fetch size = 8, query used

Code:

select simsamplec0_.AUTOPOLICYID as AUTOPOLI1_34_11_, simsamplec0_.sampleWeight as sampleWe2_34_11_, autopolicy1_.ID as ID35_0_, autopolicy1_.EffectiveDt as Effectiv2_35_0_, autopolicy1_.ExpirationDt as Expirati3_35_0_, autopolicy1_.PolicyNumber as PolicyNu4_35_0_, address2_.AUTOPOLICYID as AUTOPOLI1_42_1_, address2_.AddrType as AddrType42_1_, address2_.City as City42_1_, address2_.Country as Country42_1_, address2_.County as County42_1_, address2_.State as State42_1_, address2_.Street as Street42_1_, address2_.Zip as Zip42_1_, coverage3_.AUTOPOLICYID as AUTOPOLI1_43_2_, coverage3_.CoverageCd as CoverageCd43_2_, coverage3_.Deductible as Deductible43_2_, coverage3_.Limit as Limit43_2_, customer4_.AUTOPOLICYID as AUTOPOLI1_36_3_, customer4_.CreditScore as CreditSc2_36_3_, customer4_.FirstName as FirstName36_3_, customer4_.LastName as LastName36_3_, customer4_.TaxId as TaxId36_3_, customer4_.TaxIdTypeCd as TaxIdTyp6_36_3_, communicat5_.AUTOPOLICYID as AUTOPOLI1_37_4_, communicat5_.Email as Email37_4_, communicat5_.Phone as Phone37_4_, drivingrec6_.AUTOPOLICYID as AUTOPOLI1_39_5_, drivingrec6_.AnyAccidentInLast3Years as AnyAccid2_39_5_, drivingrec6_.AnyMovingViolationsInLast3Years as AnyMovin3_39_5_, drivingrec6_.LicensedSince as Licensed4_39_5_, drivingrec6_.LicNumber as LicNumber39_5_, drivingrec6_.LicState as LicState39_5_, drivingrec6_.NumYearsLicensed as NumYears7_39_5_, accidentin7_.AUTOPOLICYID as AUTOPOLI2_13_, accidentin7_.INDEX1 as INDEX1_13_, accidentin7_.INDEX1 as INDEX1_40_6_, accidentin7_.AUTOPOLICYID as AUTOPOLI2_40_6_, accidentin7_.AccidentDescription as Accident3_40_6_, accidentin7_.AccidentTypeCd as Accident4_40_6_, accidentin7_.AtFaultInd as AtFaultInd40_6_, accidentin7_.BodilyInjuriesInd as BodilyIn6_40_6_, accidentin7_.DateofAccident as DateofAc7_40_6_, movingviol8_.AUTOPOLICYID as AUTOPOLI2_14_, movingviol8_.INDEX1 as INDEX1_14_, movingviol8_.INDEX1 as INDEX1_41_7_, movingviol8_.AUTOPOLICYID as AUTOPOLI2_41_7_, movingviol8_.DateofViolation as DateofVi3_41_7_, movingviol8_.Incarceration as Incarcer4_41_7_, movingviol8_.MovingViolationsTypeCd as MovingVi5_41_7_, movingviol8_.ViolationDescription as Violatio6_41_7_, persinfo9_.AUTOPOLICYID as AUTOPOLI1_38_8_, persinfo9_.Age as Age38_8_, persinfo9_.DOB as DOB38_8_, persinfo9_.Employer as Employer38_8_, persinfo9_.Gender as Gender38_8_, persinfo9_.HomeOwner as HomeOwner38_8_, persinfo9_.LengthTimeEmployed as LengthTi7_38_8_, persinfo9_.LengthTimewithPrevEmployer as LengthTi8_38_8_, persinfo9_.MaritalStatusCd as MaritalS9_38_8_, persinfo9_.NumDependents as NumDepe10_38_8_, persinfo9_.NumJobChanges as NumJobC11_38_8_, persinfo9_.Occupation as Occupation38_8_, persinfo9_.YrsAtCurrentResidence as YrsAtCu13_38_8_, generalque10_.AUTOPOLICYID as AUTOPOLI2_15_, generalque10_.INDEX1 as INDEX1_15_, generalque10_.INDEX1 as INDEX1_45_9_, generalque10_.AUTOPOLICYID as AUTOPOLI2_45_9_, vehicleinf11_.AUTOPOLICYID as AUTOPOLI2_16_, vehicleinf11_.INDEX1 as INDEX1_16_, vehicleinf11_.INDEX1 as INDEX1_44_10_, vehicleinf11_.AUTOPOLICYID as AUTOPOLI2_44_10_, vehicleinf11_.AnnualMileage as AnnualMi3_44_10_, vehicleinf11_.Color as Color44_10_, vehicleinf11_.LicensePlateNum as LicenseP5_44_10_, vehicleinf11_.LienHolder as LienHolder44_10_, vehicleinf11_.Make as Make44_10_, vehicleinf11_.Model as Model44_10_, vehicleinf11_.ModelYr as ModelYr44_10_, vehicleinf11_.OwnOrLease as OwnOrLease44_10_, vehicleinf11_.PurchaseAmt as Purchas11_44_10_, vehicleinf11_.PurchaseDt as PurchaseDt44_10_, vehicleinf11_.VIN as VIN44_10_ from SAMPLECASE simsamplec0_ left outer join AutoPolicy autopolicy1_ on simsamplec0_.AUTOPOLICYID=autopolicy1_.ID left outer join Address address2_ on autopolicy1_.ID=address2_.AUTOPOLICYID left outer join Coverage coverage3_ on autopolicy1_.ID=coverage3_.AUTOPOLICYID left outer join Customer customer4_ on autopolicy1_.ID=customer4_.AUTOPOLICYID left outer join Communication communicat5_ on customer4_.AUTOPOLICYID=communicat5_.AUTOPOLICYID left outer join DrivingRecord drivingrec6_ on customer4_.AUTOPOLICYID=drivingrec6_.AUTOPOLICYID left outer join AccidentInfo accidentin7_ on drivingrec6_.AUTOPOLICYID=accidentin7_.AUTOPOLICYID left outer join MovingViolations movingviol8_ on drivingrec6_.AUTOPOLICYID=movingviol8_.AUTOPOLICYID left outer join PersInfo persinfo9_ on customer4_.AUTOPOLICYID=persinfo9_.AUTOPOLICYID left outer join GeneralQuestions generalque10_ on autopolicy1_.ID=generalque10_.AUTOPOLICYID left outer join VehicleInfo vehicleinf11_ on autopolicy1_.ID=vehicleinf11_.AUTOPOLICYID where simsamplec0_.AUTOPOLICYID in (?, ?)

When I am using the default value of 1 for fetch size, the query used is

Code:

Hibernate: select simsamplec0_.AUTOPOLICYID as AUTOPOLI1_92_11_, simsamplec0_.sampleWeight as sampleWe2_92_11_, autopolicy1_.ID as ID93_0_, autopolicy1_.EffectiveDt as Effectiv2_93_0_, autopolicy1_.ExpirationDt as Expirati3_93_0_, autopolicy1_.PolicyNumber as PolicyNu4_93_0_, address2_.AUTOPOLICYID as AUTOPOLI1_100_1_, address2_.AddrType as AddrType100_1_, address2_.City as City100_1_, address2_.Country as Country100_1_, address2_.County as County100_1_, address2_.State as State100_1_, address2_.Street as Street100_1_, address2_.Zip as Zip100_1_, coverage3_.AUTOPOLICYID as AUTOPOLI1_101_2_, coverage3_.CoverageCd as CoverageCd101_2_, coverage3_.Deductible as Deductible101_2_, coverage3_.Limit as Limit101_2_, customer4_.AUTOPOLICYID as AUTOPOLI1_94_3_, customer4_.CreditScore as CreditSc2_94_3_, customer4_.FirstName as FirstName94_3_, customer4_.LastName as LastName94_3_, customer4_.TaxId as TaxId94_3_, customer4_.TaxIdTypeCd as TaxIdTyp6_94_3_, communicat5_.AUTOPOLICYID as AUTOPOLI1_95_4_, communicat5_.Email as Email95_4_, communicat5_.Phone as Phone95_4_, drivingrec6_.AUTOPOLICYID as AUTOPOLI1_97_5_, drivingrec6_.AnyAccidentInLast3Years as AnyAccid2_97_5_, drivingrec6_.AnyMovingViolationsInLast3Years as AnyMovin3_97_5_, drivingrec6_.LicensedSince as Licensed4_97_5_, drivingrec6_.LicNumber as LicNumber97_5_, drivingrec6_.LicState as LicState97_5_, drivingrec6_.NumYearsLicensed as NumYears7_97_5_, accidentin7_.AUTOPOLICYID as AUTOPOLI2_13_, accidentin7_.INDEX1 as INDEX1_13_, accidentin7_.INDEX1 as INDEX1_98_6_, accidentin7_.AUTOPOLICYID as AUTOPOLI2_98_6_, accidentin7_.AccidentDescription as Accident3_98_6_, accidentin7_.AccidentTypeCd as Accident4_98_6_, accidentin7_.AtFaultInd as AtFaultInd98_6_, accidentin7_.BodilyInjuriesInd as BodilyIn6_98_6_, accidentin7_.DateofAccident as DateofAc7_98_6_, movingviol8_.AUTOPOLICYID as AUTOPOLI2_14_, movingviol8_.INDEX1 as INDEX1_14_, movingviol8_.INDEX1 as INDEX1_99_7_, movingviol8_.AUTOPOLICYID as AUTOPOLI2_99_7_, movingviol8_.DateofViolation as DateofVi3_99_7_, movingviol8_.Incarceration as Incarcer4_99_7_, movingviol8_.MovingViolationsTypeCd as MovingVi5_99_7_, movingviol8_.ViolationDescription as Violatio6_99_7_, persinfo9_.AUTOPOLICYID as AUTOPOLI1_96_8_, persinfo9_.Age as Age96_8_, persinfo9_.DOB as DOB96_8_, persinfo9_.Employer as Employer96_8_, persinfo9_.Gender as Gender96_8_, persinfo9_.HomeOwner as HomeOwner96_8_, persinfo9_.LengthTimeEmployed as LengthTi7_96_8_, persinfo9_.LengthTimewithPrevEmployer as LengthTi8_96_8_, persinfo9_.MaritalStatusCd as MaritalS9_96_8_, persinfo9_.NumDependents as NumDepe10_96_8_, persinfo9_.NumJobChanges as NumJobC11_96_8_, persinfo9_.Occupation as Occupation96_8_, persinfo9_.YrsAtCurrentResidence as YrsAtCu13_96_8_, generalque10_.AUTOPOLICYID as AUTOPOLI2_15_, generalque10_.INDEX1 as INDEX1_15_, generalque10_.INDEX1 as INDEX1_103_9_, generalque10_.AUTOPOLICYID as AUTOPOLI2_103_9_, vehicleinf11_.AUTOPOLICYID as AUTOPOLI2_16_, vehicleinf11_.INDEX1 as INDEX1_16_, vehicleinf11_.INDEX1 as INDEX1_102_10_, vehicleinf11_.AUTOPOLICYID as AUTOPOLI2_102_10_, vehicleinf11_.AnnualMileage as AnnualMi3_102_10_, vehicleinf11_.Color as Color102_10_, vehicleinf11_.LicensePlateNum as LicenseP5_102_10_, vehicleinf11_.LienHolder as LienHolder102_10_, vehicleinf11_.Make as Make102_10_, vehicleinf11_.Model as Model102_10_, vehicleinf11_.ModelYr as ModelYr102_10_, vehicleinf11_.OwnOrLease as OwnOrLease102_10_, vehicleinf11_.PurchaseAmt as Purchas11_102_10_, vehicleinf11_.PurchaseDt as PurchaseDt102_10_, vehicleinf11_.VIN as VIN102_10_ from SAMPLECASE simsamplec0_ left outer join AutoPolicy autopolicy1_ on simsamplec0_.AUTOPOLICYID=autopolicy1_.ID left outer join Address address2_ on autopolicy1_.ID=address2_.AUTOPOLICYID left outer join Coverage coverage3_ on autopolicy1_.ID=coverage3_.AUTOPOLICYID left outer join Customer customer4_ on autopolicy1_.ID=customer4_.AUTOPOLICYID left outer join Communication communicat5_ on customer4_.AUTOPOLICYID=communicat5_.AUTOPOLICYID left outer join DrivingRecord drivingrec6_ on customer4_.AUTOPOLICYID=drivingrec6_.AUTOPOLICYID left outer join AccidentInfo accidentin7_ on drivingrec6_.AUTOPOLICYID=accidentin7_.AUTOPOLICYID left outer join MovingViolations movingviol8_ on drivingrec6_.AUTOPOLICYID=movingviol8_.AUTOPOLICYID left outer join PersInfo persinfo9_ on customer4_.AUTOPOLICYID=persinfo9_.AUTOPOLICYID left outer join GeneralQuestions generalque10_ on autopolicy1_.ID=generalque10_.AUTOPOLICYID left outer join VehicleInfo vehicleinf11_ on autopolicy1_.ID=vehicleinf11_.AUTOPOLICYID where simsamplec0_.AUTOPOLICYID=?

And regarding the changing of query, I can not change query string. That will be provided as argument. But i tried using pagination , setfirstresult and setmaxresult but it also did not gave much difference in performance.
and again thanks for posting reply :)

pkleindl · **Joined:** Thu Jan 08, 2009 3:48 pm **Posts:** 168

[quote="getmerohit"] where simsamplec0_.AUTOPOLICYID in (?, ?)[/code]

That is quite strange, how many distinct values are there in AUTOPOLICYID ?
because even with batch size 8 there are only 2 entries loaded.

Can you post how exactly you are currently loading the base list and how it is accessed in the table?

How are your tables mapped?

getmerohit · **Joined:** Mon Nov 10, 2008 3:46 am **Posts:** 12

pkleindl wrote:

where simsamplec0_.AUTOPOLICYID in (?, ?)
That is quite strange, how many distinct values are there in AUTOPOLICYID ?
because even with batch size 8 there are only 2 entries loaded.

Can you post how exactly you are currently loading the base list and how it is accessed in the table?

I am not getting what you are asking...AUTOPOLICYID is the primary key and is different for each row.

Are you asking about mapping? I have already given a small piece in the previous post.

pkleindl · **Joined:** Thu Jan 08, 2009 3:48 pm **Posts:** 168

You kinda ommited that information in

Code:

<class entity-name="SampleCase" table="SAMPLECASE">
<id name="IRRELEVANT_NAME0" column="ID" type="string">
<generator class="native"/>
</id>
<property name="sample" type="double" ></property>
<one-to-one name="policy" cascade="all" fetch="join" class="AutoPolicy"/>

So AUTOPOLICYID is the PK for SampleCase as well as the FK to AutoPolicy?

My point is:
With Batch Fetching Hibernate should always (except the last batch) load x entries at once which means a "someID IN (?,?,?,?,?,?,?,?,?,?)" in the SQL.

Can you post the code where you load the list and iterate over it?

getmerohit · **Joined:** Mon Nov 10, 2008 3:46 am **Posts:** 12

I am not doing anything different for loading the list and iteration.

Code:

query = session.createQuery(queryString);
Iterator records = query.iterate();

Then i am using fetching record one by one and storing it after performing some operations.

pkleindl · **Joined:** Thu Jan 08, 2009 3:48 pm **Posts:** 168

Does your queryString only include SampleCase?

Because HQL ignores the configured mapping which means that on every iterate the associated records will be loaded separately

Based on your mapping i would suggest that you should include every "to-one" mapping with "inner join fetch" or "left join fetch" in your queryString

Can you try that out?

getmerohit · **Joined:** Mon Nov 10, 2008 3:46 am **Posts:** 12

But using the sql query which I posted earlier I am able to fetch one row of data with associations as well in one go. My query string is only "from SampleCase o" . I dont have privilege to modify the query string also.

I tried to use the query "from SampleCase o fetch all properties" but the sql query used remained same.

But my doubt is to fetch say 100 rows of data using one query itself.

pkleindl · **Joined:** Thu Jan 08, 2009 3:48 pm **Posts:** 168

Instead of using iterate (which only gets the next record) you could try using .list() together with .setFirstResult and .setMaxResults() surrounded by an outer loop to step through the FirstResult

You just have to avoid joining "to-many" associations with that approch and load them afterwards.

Your try with "fetch all properties" only tries to load properties (not associations!) regardless if defined lazy or not

mmerder · **Posted:** Wed Feb 04, 2009 7:45 am

fetch all properties fetches all properties and is only necessary if you use property-lazy-fetching. Your associations won't be fetched using that. If you want them to be fetched too, you have to do what pkleindl says, use join fetch