-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 6 posts ] 
  Previous topic | Next topic 
Author Message
Robert Roeser
 Post subject: Performance Problems with Hibnerate on large table.
PostPosted: Tue Jan 11, 2005 8:27 pm 
Newbie

Joined: Tue Jan 11, 2005 8:16 pm
Posts: 4
Hibernate version:2.1.7

Name and version of the database you are using: DB2 8 on AIX

The generated SQL (show_sql=true): true

Debug level Hibernate log excerpt:

I am having a problem with hibernate running a query against a table with 2.9 million records. The max records has been set at 200 records. When I try to run hibernate it takes several minutes to run the query, but when I try the same query through JDBC it takes only 0.18 secs.

Here is the configuration from hibernate.cfg.xml:
Code:
<property name="connection.datasource">jdbc/MDIDB</property>
<property name="jndi.class">com.ibm.websphere.naming.WsnInitialContextFactory</property>
<property name="dialect">net.sf.hibernate.dialect.DB2Dialect</property>
        <!-- Change to 'true' to see Hibernate-generated SQL -->
        <property name="show_sql">true</property>
        <property name="use_outer_join">true</property>
        <property name="max_fetch_depth">10</property>
        <property name="transaction.factory_class">net.sf.hibernate.transaction.JTATransactionFactory</property>
      <property name="hibernate.transaction.manager_lookup_class">net.sf.hibernate.transaction.WebSphereTransactionManagerLookup</property>
      <property name="connection.provider_class">com.freightliner.util.FTLDataSourceConnectionProvider</property>
        <property name="statement_cache.size">20</property>
        <property name="jdbc.batch_size">20</property>
        <property name="jdbc.fetch_size">25</property>
        <property name="jdbc.use_scrollable_resultsets">true</property>
      <property name="cglib.use_reflection_optimizer">true</property>
        <property name="hibernate.cache.us_minimal_puts">true</property>


Here is the code from the DAO that executes the query:
Code:
Query query = SessionUtil.getSession().createQuery(m_stringBuffer.toString());
query.setFetchSize(MdiConfig.getMaxDbResults());
query.setMaxResults(MdiConfig.getMaxDbResults());
list = query.list();


This is basically the query that hibernate is generating. I removed the column names. There are over 100 hundred of them:
Code:
select * from ( select rownumber() over() as rownumber_, ...... from TABLE TABLE0_) as temp_ where rownumber_ <= ?

[/code]
Top
 Profile  
 
gavin
 Post subject:
PostPosted: Tue Jan 11, 2005 10:30 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
Sorry, your description is useless. You need to tell us what is taking all the time. 200 rows is tiny and should not take very long. Presumably you have some broken association fetching strategy or something.
Top
 Profile  
 
Robert Roeser
 Post subject:
PostPosted: Wed Jan 12, 2005 1:50 pm 
Newbie

Joined: Tue Jan 11, 2005 8:16 pm
Posts: 4
If I knew why it was taking so long I wouldnt have asked. There doesn't seem to be a bottle neck with the database as the EXACT same query - ie the one that hibernate generates runs in sub-second time via JDBC.

I forgot to mention that the webserver is WebSphere 5.1

If anyone has seen this behavior before with WAS 5, DB2, and hibernate advice would be helpful.
Top
 Profile  
 
Robert Roeser
 Post subject:
PostPosted: Wed Jan 12, 2005 1:52 pm 
Newbie

Joined: Tue Jan 11, 2005 8:16 pm
Posts: 4
Also, I stepped through the debugger in WSAD and it hangs on

list = query.list();

This is where it takes a long time to return.
Top
 Profile  
 
Robert Roeser
 Post subject:
PostPosted: Wed Jan 12, 2005 1:59 pm 
Newbie

Joined: Tue Jan 11, 2005 8:16 pm
Posts: 4
Here is the mapping file for the DTO:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping package="com.freightliner.mdi.dto">
<class name="DealerItemDTO" table="DEALER_ITEM" schema="MDI" dynamic-insert="true">
<composite-id name="dealerItemPK" class="com.freightliner.mdi.dto.pk.DealerItemPK">
<key-property name="dlrId" column="DLR_ID" type="string"/>
<key-property name="itemId" column="ITEM_ID" type="string"/>
<key-property name="compId" column="COMP_ID" type="string"/>
</composite-id>

<version name="hibVers" column="HIB_VERS" type="integer"/>
<property name="m_dlrItemId" column="DLR_ITEM_ID" type="string" access="field" />
<property name="m_dlrUomCd" column="DLR_UOM_CD" type="string" access="field" />
<property name="m_dlrPlnInd" column="DLR_PLN_IND" type="character" access="field" />
<property name="m_dlrStkCd" column="DLR_STK_CD" type="string" access="field" />
<property name="m_dlrAutoManCd" column="DLR_AUTO_MAN_CD" type="character" access="field" />
<property name="m_dlrSrcCd" column="DLR_SRC_CD" type="string" access="field" />
<property name="m_dlrOhQty" column="DLR_OH_QTY" type="integer" access="field" />
<property name="m_dlrAllocQty" column="DLR_ALLOC_QTY" type="integer" access="field" />
<property name="m_dlrBoQty" column="DLR_BO_QTY" type="integer" access="field" />
<property name="m_dlrJobQty" column="DLR_JOB_QTY" type="integer" access="field" />
<property name="m_dlrMtdSlsQty" column="DLR_MTD_SLS_QTY" type="integer" access="field" />
<property name="m_dlrMtdLostSlsQty" column="DLR_MTD_LOST_SLS_QTY" type="integer" access="field" />
<property name="m_dlrMtdHitsQty" column="DLR_MTD_HITS_QTY" type="integer" access="field" />
<property name="m_dlrMtdRtnsQty" column="DLR_MTD_RTNS_QTY" type="integer" access="field" />
<property name="m_dlrDaysOutOfStk" column="DLR_DAYS_OUT_OF_STK" type="integer" access="field" />
<property name="m_dlrItemCost" column="DLR_ITEM_COST" type="double" access="field" />
<property name="m_dlrBinLoc" column="DLR_BIN_LOC" type="string" access="field" />
<property name="m_dlrLoadDateTs" column="DLR_LOAD_DATE_TS" type="timestamp" access="field" />
<property name="m_itemDesc" column="ITEM_DESC" type="string" access="field" />
<property name="m_supsFromItemId" column="SUPS_FROM_ITEM_ID" type="string" access="field" />
<property name="m_supsToItemId" column="SUPS_TO_ITEM_ID" type="string" access="field" />
<property name="m_supsInfoTyp" column="SUPS_INFO_TYP" type="string" access="field" />
<property name="m_supsTyp" column="SUPS_TYP" type="character" access="field" />
<property name="m_supsActInd" column="SUPS_ACT_IND" type="character" access="field" />
<property name="m_supsEffDate" column="SUPS_EFF_DATE" type="date" access="field" />
<property name="m_supsModTyp" column="SUPS_MOD_TYP" type="character" access="field" />
<property name="m_itemWght" column="ITEM_WGHT" type="double" access="field" />
<property name="m_itemWghtUom" column="ITEM_WGHT_UOM" type="string" access="field" />
<property name="m_stkCost" column="STK_COST" type="double" access="field" />
<property name="m_critCd" column="CRIT_CD" type="character" access="field" />
<property name="m_i2FcBypInd" column="I2_FC_BYP_IND" type="character" access="field" />
<property name="m_i2IpBypInd" column="I2_IP_BYP_IND" type="character" access="field" />
<property name="m_i2RpBypInd" column="I2_RP_BYP_IND" type="character" access="field" />
<property name="m_i2OrdReplnPol" column="I2_ORD_REPLN_POL" type="character" access="field" />
<property name="m_i2KeepOptCd" column="I2_KEEP_OPT_CD" type="integer" access="field" />
<property name="m_i2LeadTimeDays" column="I2_LEAD_TIME_DAYS" type="integer" access="field" />
<property name="m_i2LeadTimeDaysOvr" column="I2_LEAD_TIME_DAYS_OVR" type="character" access="field" />
<property name="m_i2LeadTimeDaysStdDev" column="I2_LEAD_TIME_DAYS_STD_DEV" type="integer" access="field" />
<property name="m_i2LeadTimeDaysStdDevOvr" column="I2_LEAD_TIME_DAYS_STD_DEV_OVR" type="character" access="field" />
<property name="m_i2LeadTimeUom" column="I2_LEAD_TIME_UOM" type="string" access="field" />
<property name="m_i2SrcPolicy" column="I2_SRC_POLICY" type="string" access="field" />
<property name="m_i2OrderPriority" column="I2_ORDER_PRIORITY" type="double" access="field" />
<property name="m_i2Supplypct" column="I2_SUPPLYPCT" type="double" access="field" />
<property name="m_i2TransAgent" column="I2_TRANS_AGENT" type="string" access="field" />
<property name="m_i2TransMode" column="I2_TRANS_MODE" type="string" access="field" />
<property name="m_i2TransCost" column="I2_TRANS_COST" type="double" access="field" />
<property name="m_i2DfltMad" column="I2_DFLT_MAD" type="double" access="field" />
<property name="m_i2PlnrIdPrev" column="I2_PLNR_ID_PREV" type="string" access="field" />
<property name="m_i2PlnrId" column="I2_PLNR_ID" type="string" access="field" />
<property name="m_i2ProfileId" column="I2_PROFILE_ID" type="string" access="field" />
<property name="m_i2Rop" column="I2_ROP" type="double" access="field" />
<property name="m_i2Outl" column="I2_OUTL" type="double" access="field" />
<property name="m_i2MeanDmd" column="I2_MEAN_DMD" type="double" access="field" />
<property name="m_i2StdDev" column="I2_STD_DEV" type="double" access="field" />
<property name="m_i2SvcLvl" column="I2_SVC_LVL" type="double" access="field" />
<property name="m_i2Qualifier" column="I2_QUALIFIER" type="string" access="field" />
<property name="m_loadDateTs" column="LOAD_DATE_TS" type="timestamp" access="field" />
<property name="m_stsCd" column="STS_CD" type="character" access="field" />
<property name="m_mdiVndrId" column="MDI_VNDR_ID" type="string" access="field" />
<property name="m_suplrTyp" column="SUPLR_TYP" type="string" access="field" />
<property name="m_mdiAutoManCd" column="MDI_AUTO_MAN_CD" type="character" access="field" />
<property name="m_mdiStdPkgQty" column="MDI_STD_PKG_QTY" type="integer" access="field" />
<property name="m_jobQty" column="JOB_QTY" type="integer" access="field" />
<property name="m_fullBinQty" column="FULL_BIN_QTY" type="integer" access="field" />
<property name="m_mdiPlnInd" column="MDI_PLN_IND" type="character" access="field" />
<property name="m_mdiStkCd" column="MDI_STK_CD" type="character" access="field" />
<property name="m_replnCd" column="REPLN_CD" type="character" access="field" />
<property name="m_rsnCd" column="RSN_CD" type="string" access="field" />
<property name="m_minQty" column="MIN_QTY" type="integer" access="field" />
<property name="m_maxQty" column="MAX_QTY" type="integer" access="field" />
<property name="m_manCtrlLoadDateTs" column="MAN_CTRL_LOAD_DATE_TS" type="timestamp" access="field" />
<property name="m_reviewDateTs" column="REVIEW_DATE_TS" type="timestamp" access="field" />
<property name="m_ovrLvl" column="OVR_LVL" type="string" access="field" />
<property name="m_forceFcInd" column="FORCE_FC_IND" type="character" access="field" />
<property name="m_dlrSupsFromCompId" column="DLR_SUPS_FROM_COMP_ID" type="string" access="field" />
<property name="m_dlrSupsFromItemId" column="DLR_SUPS_FROM_ITEM_ID" type="string" access="field" />
<property name="m_dlrSupsToCompId" column="DLR_SUPS_TO_COMP_ID" type="string" access="field" />
<property name="m_dlrSupsToItemId" column="DLR_SUPS_TO_ITEM_ID" type="string" access="field" />
<property name="m_dlrSupsDateTs" column="DLR_SUPS_DATE_TS" type="timestamp" access="field" />
<property name="m_suplrId" column="SUPLR_ID" type="string" access="field" />
<property name="m_i2OrdSchedule" column="I2_ORD_SCHEDULE" type="string" access="field" />
<property name="m_i2CalendarUsage" column="I2_CALENDAR_USAGE" type="character" access="field" />
<property name="m_i2EffEndDateTs" column="I2_EFF_END_DATE_TS" type="timestamp" access="field" />
<property name="m_comitQty" column="COMIT_QTY" type="integer" access="field" />
<property name="m_availQty" column="AVAIL_QTY" type="integer" access="field" />
<property name="m_availQtyPrev" column="AVAIL_QTY_PREV" type="integer" access="field" />
<property name="m_ooQty" column="OO_QTY" type="integer" access="field" />
<property name="m_mtdSlsQty" column="MTD_SLS_QTY" type="integer" access="field" />
<property name="m_mtdLostSlsQty" column="MTD_LOST_SLS_QTY" type="integer" access="field" />
<property name="m_mtdHitsQty" column="MTD_HITS_QTY" type="integer" access="field" />
<property name="m_mtdRtnsQty" column="MTD_RTNS_QTY" type="integer" access="field" />
<property name="m_mtdDaysOutOfStk" column="MTD_DAYS_OUT_OF_STK" type="integer" access="field" />
<property name="m_mtdDaysActiveCnt" column="MTD_DAYS_ACTIVE_CNT" type="integer" access="field" />
<property name="m_mtdOhSum" column="MTD_OH_SUM" type="integer" access="field" />
<property name="m_mtdOhSumCnt" column="MTD_OH_SUM_CNT" type="integer" access="field" />
<property name="m_mtdAvgDlySls" column="MTD_AVG_DLY_SLS" type="double" access="field" />
<property name="m_mtdLostSls" column="MTD_LOST_SLS" type="double" access="field" />
<property name="m_mtdAvgInvtryDolr" column="MTD_AVG_INVTRY_DOLR" type="double" access="field" />
<property name="m_mtdTurns" column="MTD_TURNS" type="double" access="field" />
<property name="m_mtdInvtryDolr" column="MTD_INVTRY_DOLR" type="double" access="field" />
<property name="m_mtdSrvcLvl" column="MTD_SRVC_LVL" type="double" access="field" />
<property name="m_mtdCostOfSls" column="MTD_COST_OF_SLS" type="double" access="field" />
<property name="m_i2ItemId" column="I2_ITEM_ID" type="string" access="field" />
<property name="m_i2DpItemId" column="I2_DP_ITEM_ID" type="string" access="field" />
<property name="m_i2CritCostCd" column="I2_CRIT_COST_CD" type="string" access="field" />
<property name="m_ordUom" column="ORD_UOM" type="string" access="field" />
<property name="m_mosHistAvail" column="MOS_HIST_AVAIL" type="integer" access="field" />
<property name="m_i2SuplrIdPrev" column="I2_SUPLR_ID_PREV" type="string" access="field" />
<property name="m_i2SuplrId" column="I2_SUPLR_ID" type="string" access="field" />
<property name="m_i2SuplrDlrCd" column="I2_SUPLR_DLR_CD" type="string" access="field" />
<property name="m_i2LaneType" column="I2_LANE_TYPE" type="string" access="field" />
<property name="m_i2ParamId" column="I2_PARAM_ID" type="string" access="field" />
<property name="m_i2ResultMeasId" column="I2_RESULT_MEAS_ID" type="string" access="field" />
<property name="m_i2MeanDmdCompTyp" column="I2_MEAN_DMD_COMP_TYP" type="integer" access="field" />
<property name="m_i2MeanDmdLookaheadDays" column="I2_MEAN_DMD_LOOKAHEAD_DAYS" type="integer" access="field" />
<property name="m_i2DlrTyp" column="I2_DLR_TYP" type="string" access="field" />
<property name="m_i2UpdDateTs" column="I2_UPD_DATE_TS" type="timestamp" access="field" />
<property name="m_i2SendInd" column="I2_SEND_IND" type="character" access="field" />
<property name="m_i2ActCd" column="I2_ACT_CD" type="character" access="field" />
<property name="m_i2ActualActCd" column="I2_ACTUAL_ACT_CD" type="character" access="field" />
<property name="m_i2SuplrPlnrInd" column="I2_SUPLR_PLNR_IND" type="character" access="field" />
<property name="m_plnrId" column="PLNR_ID" type="string" access="field" />
<property name="m_jobQtyPrev" column="JOB_QTY_PREV" type="integer" access="field" />
<property name="m_replnCdPrev" column="REPLN_CD_PREV" type="character" access="field" />
<property name="m_mtdSlsQtyPrev" column="MTD_SLS_QTY_PREV" type="integer" access="field" />
<property name="m_mtdUpdDateTs" column="MTD_UPD_DATE_TS" type="timestamp" access="field" />
<property name="m_i2DlrTypPrev" column="I2_DLR_TYP_PREV" type="string" access="field" />
<property name="m_i2FcTechnique" column="I2_FC_TECHNIQUE" type="string" access="field" />
<property name="m_userId" column="USER_ID" type="string" access="field" />
<property name="m_updateDateTs" column="UPDATE_DATE_TS" type="timestamp" access="field" />

<map name="intransitShipments" table="INTRANSIT_SHIPMENTS" lazy="true" cascade="save-update">
<key>
<column name="DLR_ID" />
<column name="ITEM_ID" />
<column name="COMP_ID" />
</key>
<index column="DLR_PO_NO" type="string"/>
<one-to-many class="com.freightliner.mdi.dto.IntransitShipmentDTO"/>
</map>

<map name="history" table="HISTORY" lazy="true" cascade="save-update">
<key>
<column name="DLR_ID" />
<column name="ITEM_ID" />
<column name="COMP_ID" />
</key>
<index column="BCKT_ID" type="string"/>
<one-to-many class="com.freightliner.mdi.dto.HistoryDTO"/>
</map>

<many-to-one name="dealer" class="DealerDTO" insert="false" update="false">
<column name="DLR_ID"/>
</many-to-one>

</class>
</hibernate-mapping>

It runs the same with or withour the associations commented out.
Top
 Profile  
 
christian
 Post subject:
PostPosted: Wed Jan 12, 2005 2:07 pm 
Hibernate Team
Hibernate Team

Joined: Mon Aug 25, 2003 9:11 pm
Posts: 4592
Location: Switzerland
What about the rest of the stuff metioned in the form? Debug log? Please read the red box again and how to post messages to this forum. We are not mind readers and we don't know what your problem is.
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 6 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.