-->
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
  [ 2 posts ] 
  Previous topic | Next topic 
Author Message
mron1002
 Post subject: Issue with Bulk Inserts/Updates with SQL Server 2000
PostPosted: Wed Dec 28, 2005 2:04 pm 
Newbie

Joined: Thu May 05, 2005 4:53 pm
Posts: 11
Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 3.0.5

Mapping documents:

<hibernate-mapping>
<!--
Auto-generated mapping file from
the hibernate.org cfg2hbm engine
-->
<class
name="com.tdemand.generated.pojo.TdInventory"
table="TD_INVENTORY"
schema="trd0nedbo"
>
<id
name="InventoryTblId"
type="java.lang.Long"
>
<column name="INVENTORY_TBL_ID" length="18" not-null="true" unique="true" sql-type="NUMBER" />
<generator class="identity" />
</id>

<many-to-one
name="TdProduct"
class="com.tdemand.generated.pojo.TdProduct"
>
<column name="PRODUCT_TBL_ID" length="18" not-null="false" />
</many-to-one>

<many-to-one
name="TdOrganization"
class="com.tdemand.generated.pojo.TdOrganization"
>
<column name="ORGANIZATION_TBL_ID" length="18" not-null="false" />
</many-to-one>

<many-to-one
name="TdNode"
class="com.tdemand.generated.pojo.TdNode"
>
<column name="NODE_TBL_ID" length="18" not-null="false" />
</many-to-one>

<many-to-one
name="TdInvTimeBucket"
class="com.tdemand.generated.pojo.TdInvTimeBucket"
>
<column name="INV_TIME_BUCKET_TBL_ID" length="18" not-null="false" />
</many-to-one>

<property
name="Timeupdated"
type="java.util.Date"
>
<column name="TIMEUPDATED" length="7" not-null="false" sql-type="DATE" />
</property>

<property
name="BatchNum"
type="java.lang.String"
>
<column name="BATCH_NUM" length="10" not-null="false" sql-type="VARCHAR2" />
</property>

<property
name="Stocktype"
type="java.lang.String"
>
<column name="STOCKTYPE" length="1" not-null="false" sql-type="VARCHAR2" />
</property>

<property
name="Quantityinstock"
type="java.math.BigDecimal"
>
<column name="QUANTITYINSTOCK" length="22" not-null="false" sql-type="NUMBER" />
</property>

<property
name="BaseUom"
type="java.lang.String"
>
<column name="BASE_UOM" length="20" not-null="false" sql-type="VARCHAR2" />
</property>

<property
name="BaseUomQty"
type="java.math.BigDecimal"
>
<column name="BASE_UOM_QTY" length="22" not-null="false" sql-type="NUMBER" />
</property>

<property
name="Status"
type="java.lang.String"
>
<column name="STATUS" length="20" not-null="false" sql-type="VARCHAR2" />
</property>

<property
name="Isderived"
type="java.math.BigDecimal"
>
<column name="ISDERIVED" length="22" not-null="false" sql-type="NUMBER" />
</property>

<property
name="VersionNum"
type="java.lang.String"
>
<column name="VERSION_NUM" length="20" not-null="false" sql-type="VARCHAR2" />
</property>

<property
name="Eventtime"
type="java.util.Date"
>
<column name="EVENTTIME" length="7" not-null="false" sql-type="DATE" />
</property>

<property
name="OrgId"
type="java.math.BigDecimal"
>
<column name="ORG_ID" length="22" not-null="false" sql-type="NUMBER" />
</property>

</class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

if (prod != null && node != null && buck != null){





prodnode1 = prodImpl.findByProductNode(new BigDecimal(prod.getProductTblId().toString()),new BigDecimal(node.getNodeTblId().toString()));

if (prodnode1 == null){


try
{

session = HibernateUtil.currentSession();
prodProd.addProductNode(prodnode);
logger.info("Prod Id"+new BigDecimal(prod.getProductTblId().toString()));
logger.info("NOde Id"+new BigDecimal(node.getNodeTblId().toString()));
count1++;
}

catch (HibernateException e1){

throw new TdDataManagementException(e1);

}
finally {
try {

if (count1 % 2000 == 0){
HibernateUtil.currentSession().flush();
HibernateUtil.currentSession().clear();


}



} catch (HibernateException e1) {
e1.printStackTrace();
System.out.println("Exception thrown");
}

}
} //end if


inv1 = invImpl.findInventory(prod.getProductTblId(),node.getNodeTblId(),buck,"P");


if (inv1 != null){

logger.info ("inv1 :" +inv1.getTdProduct().getProductTblId());
}
// if inventory record queried is null
if (inv1 == null){

try {

session = HibernateUtil.currentSession();
invImpl.save(inv);
count ++;

}

catch(HibernateException e1){

throw new TdDataManagementException(e1);

}

finally {
try {

if (count % 2000 == 0){
HibernateUtil.currentSession().flush();
HibernateUtil.currentSession().clear();


}



} catch (HibernateException e1) {
e1.printStackTrace();
System.out.println("Exception thrown");
}

}


}
} //end if product and node and time buckets is not null




Full stack trace of any exception that occurs:

Name and version of the database you are using:

SQL Server 2000

The generated SQL (show_sql=true):

[12/27/05 14:58:42:413 PST] 00000012 SystemOut O 59303394 [ORB.thread.pool : 1] DEBUG com.tdemand.server.datamanagement.inventory.dao.InventoryDAOHibernateImpl - query return size: 1
[12/27/05 14:58:42:413 PST] 00000012 SystemOut O 59303394 [ORB.thread.pool : 1] DEBUG com.tdemand.server.datamanagement.inventory.dao.InventoryDAOHibernateImpl - query return size: 1
[12/27/05 14:58:42:413 PST] 00000012 SystemOut O 59303394 [ORB.thread.pool : 1] INFO StagingProcessor - rows are 63592
[12/27/05 14:58:42:413 PST] 00000012 SystemOut O 59303394 [ORB.thread.pool : 1] INFO StagingProcessor - rows are 63592
[12/27/05 14:58:42:413 PST] 00000012 SystemOut O Hibernate: select tdorganiza0_.ORGANIZATION_TBL_ID as ORGANIZA1_224_, tdorganiza0_.PARENT_ORG_ID as PARENT2_224_, tdorganiza0_.ORG_ID as ORG3_224_, tdorganiza0_.COMPANY_ID as COMPANY4_224_, tdorganiza0_.ORGANIZATIONNAME as ORGANIZA5_224_, tdorganiza0_.ORGANIZATIONTYPE as ORGANIZA6_224_, tdorganiza0_.TIMECREATED as TIMECREA7_224_, tdorganiza0_.TIMEUPDATED as TIMEUPDA8_224_, tdorganiza0_.ISROOT as ISROOT224_, tdorganiza0_.EVENTTIME as EVENTTIME224_, tdorganiza0_.ORGGLN as ORGGLN224_, tdorganiza0_.COMPANY_NAME as COMPANY12_224_ from trd0nedbo.TD_ORGANIZATION tdorganiza0_ where tdorganiza0_.ORG_ID=? and tdorganiza0_.COMPANY_ID=?
[12/27/05 14:58:42:413 PST] 00000012 PmiRmArmWrapp I PMRM0003I: parent:ver=1,ip=192.168.1.102,time=1135664849396,pid=4920,reqid=16384,event=1 - current:ver=1,ip=192.168.1.102,time=1135664849396,pid=4920,reqid=535554,event=1 type=JDBC detail=executeQuery elapsed=0
[12/27/05 14:58:42:428 PST] 00000012 SystemOut O Hibernate: select tdorganiza0_.ORGANIZATION_TBL_ID as ORGANIZA1_224_, tdorganiza0_.PARENT_ORG_ID as PARENT2_224_, tdorganiza0_.ORG_ID as ORG3_224_, tdorganiza0_.COMPANY_ID as COMPANY4_224_, tdorganiza0_.ORGANIZATIONNAME as ORGANIZA5_224_, tdorganiza0_.ORGANIZATIONTYPE as ORGANIZA6_224_, tdorganiza0_.TIMECREATED as TIMECREA7_224_, tdorganiza0_.TIMEUPDATED as TIMEUPDA8_224_, tdorganiza0_.ISROOT as ISROOT224_, tdorganiza0_.EVENTTIME as EVENTTIME224_, tdorganiza0_.ORGGLN as ORGGLN224_, tdorganiza0_.COMPANY_NAME as COMPANY12_224_ from trd0nedbo.TD_ORGANIZATION tdorganiza0_ where tdorganiza0_.ORG_ID=? and tdorganiza0_.COMPANY_ID=?
[12/27/05 14:58:42:428 PST] 00000012 PmiRmArmWrapp I PMRM0003I: parent:ver=1,ip=192.168.1.102,time=1135664849396,pid=4920,reqid=16384,event=1 - current:ver=1,ip=192.168.1.102,time=1135664849396,pid=4920,reqid=535555,event=1 type=JDBC detail=executeQuery elapsed=0
[12/27/05 14:58:42:835 PST] 00000012 SystemOut O Hibernate: select tdnode0_.NODE_TBL_ID as NODE1_154_, tdnode0_.CAPACITY_TBL_ID as CAPACITY2_154_, tdnode0_.ORGANIZATION_TBL_ID as ORGANIZA3_154_, tdnode0_.ADDRESS_TBL_ID as ADDRESS4_154_, tdnode0_.LOOKUP_TBL_ID as LOOKUP5_154_, tdnode0_.LOCATIONTYPE_TBL_ID as LOCATION6_154_, tdnode0_.LVL as LVL154_, tdnode0_.HIERARCHY as HIERARCHY154_, tdnode0_.ISVIRTUAL as ISVIRTUAL154_, tdnode0_.TIMECREATED as TIMECRE10_154_, tdnode0_.TIMEUPDATED as TIMEUPD11_154_, tdnode0_.LOCATIONNAME as LOCATIO12_154_, tdnode0_.EVENTTIME as EVENTTIME154_, tdnode0_.ISACTIVE as ISACTIVE154_, tdnode0_.STORENUMBER as STORENU15_154_, tdnode0_.LOCATIONIDSTRING as LOCATIO16_154_, tdnode0_.EXTERNAL_LOCATION_ID as EXTERNAL17_154_, tdnode0_.TDE_PARENT_LOCATION_ID as TDE18_154_, tdnode0_.RETAILER_LOCATION_ID as RETAILER19_154_ from trd0nedbo.TD_NODE tdnode0_ where tdnode0_.RETAILER_LOCATION_ID=?
[12/27/05 14:58:42:850 PST] 00000012 PmiRmArmWrapp I PMRM0003I: parent:ver=1,ip=192.168.1.102,time=1135664849396,pid=4920,reqid=16384,event=1 - current:ver=1,ip=192.168.1.102,time=1135664849396,pid=4920,reqid=535556,event=1 type=JDBC detail=executeQuery elapsed=15
[12/27/05 14:58:43:116 PST] 00000012 SystemOut O Hibernate: select distinct tdcrossref0_.FROMID as col_0_0_ from trd0nedbo.TD_CROSSREFLOOKUP tdcrossref0_ inner join trd0nedbo.TD_LOOKUPTYPE tdlookupty1_ on tdcrossref0_.LOOKUPTYPE_TBL_ID=tdlookupty1_.LOOKUPTYPE_TBL_ID where tdlookupty1_.LOOKUPTYPE=? and tdcrossref0_.TOID=?
[12/27/05 14:58:43:116 PST] 00000012 PmiRmArmWrapp I PMRM0003I: parent:ver=1,ip=192.168.1.102,time=1135664849396,pid=4920,reqid=16384,event=1 - current:ver=1,ip=192.168.1.102,time=1135664849396,pid=4920,reqid=535557,event=1 type=JDBC detail=executeQuery elapsed=0

From the Websphere System.out logs
Debug level Hibernate log excerpt:


The Hibernate config file looks like

<hibernate-configuration>
<session-factory>
<property name="hibernate.jndi.url">iiop://localhost:2809/</property>
<property name="hibernate.jndi.class">com.ibm.websphere.naming.WsnInitialContextFactory</property>
<property name="hibernate.session_factory_name">HibernateSessionFactory</property>
<property name="hibernate.connection.datasource">jdbc/SQLPool1</property>
<!-- <property name="hibernate.connection.driver_class">oracle.jdbc.driver.OracleDriver</property> -->
<!-- <property name="hibernate.connection.url">jdbc:oracle:thin:@192.168.1.58:1521:truedema</property>
<property name="hibernate.connection.username">apps</property>
<property name="hibernate.connection.password">apps</property>-->
<property name="hibernate.connection.pool_size">20</property>
<!-- configuration pool via c3p0-->
<!-- <property name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property> -->

<property name="c3p0.acquire_increment">1</property>
<property name="c3p0.idle_test_period">100</property>
<property name="c3p0.max_size">100</property>
<property name="c3p0.max_statements">1000</property>
<property name="c3p0.min_size">1</property>
<property name="c3p0.timeout">100</property>



<property name="show_sql">true</property>
<property name="dialect">org.hibernate.dialect.SQLServerDialect</property>
<!-- <property name="hibernate.hbm2ddl.auto">update</property> -->
<property name="hibernate.jdbc.batch_size">2000</property>
<property name="hibernate.cache.use_second_level_cache">false</property>
<property name="hibernate.cache.use_query_cache">true</property>
<property name="hibernate.generate_statistics">true</property>


The issue seems to be that the Prepared Statement Object is not keeping a compiled version of the SQL, everytime we are paying a penalty for compilation and hence a simple insert/update script is running for hours for a million row insert. I have turned on maxstatements to a very high number in the Websphere Data Source but we are still paying this cost.

If I cache the result set from the Select's do an in memory processing and just change the method to insert/update it finishes a million row insert in less than 20 minutes. Can anybody point me to what could be the probable cause?
Top
 Profile  
 
mron1002
 Post subject:
PostPosted: Wed Dec 28, 2005 6:26 pm 
Newbie

Joined: Thu May 05, 2005 4:53 pm
Posts: 11
By the way this code is wrapped in a Stateless Session bean, which invokes DAO methods but the transaction semantics are all in the SSB
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
  [ 2 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.