Hibernate version:
2.1.6
Database
MySQL 4.0 (but the same problem has appeared under Oracle

Hi,
We have to develop system that inserts very fast thousends of rows to DB.
We have written following method to save one row (Listing 1).
Inserting 80 thousends of rows took 20hrs (or even longer) and there was problem with memory leaks.

After I've rewritten the procedure to pure JDBC (Listing 2). After modification, inserting of 80 thousends of rows took ONLY 1min 15sec !!!!!) Thousend times faster comparing to Hibernate!!!!
I am sure that we have done something wrong with Hibernate but what !!
Have you tried insert thousends of rows(object) in Hibernate in short time?

Listing 1 (Hiberante)
public class ExchangeRateLoadingStrategy implements ILoadingStrategy {

/** Log handler */
private static final Log LOG =
LogFactory.getLog(ExchangeRateLoadingStrategy.class);

/**
* Save data to DB
* @see net.lidotech.finca.parser.strategy.ILoadingStrategy#load(java.util.Map)
*/
public void load(Map rowData) throws LoadingException {
try {
SessionTxManager.openSessionStartTx();
} catch (DaoInitializeException e) {
/// this part is not interesting
}

ExchangeRate exchangeRate = new ExchangeRate();

String eCurrency = (String) rowData.get("CODE");
exchangeRate.setCurrencyCode(eCurrency);

BigDecimal eExchangeRate = (BigDecimal) rowData.get("EXCHANGE_RATE");
exchangeRate.setExchangeRate(eExchangeRate);

Long validFromL = (Long) rowData.get("VALID_FROM");
long validFrom = validFromL.longValue();
exchangeRate.setValidFrom(validFrom);

Long validTillL = (Long) rowData.get("VALID_TILL");
long validTill = validTillL.longValue();
exchangeRate.setValidTill(validTill);
exchangeRate.setNew(true);

try {
ExchangeRateDao.getInstance().save(exchangeRate);
SessionTxManager.commitTx();
} catch (DaoTxException e) {
/// this part is not interesting
}

//DISCONNECT SESSION
try {
SessionTxManager.disconnectSession();
} catch (DaoInitializeException e) {
// this part is not interesting

}

}
}

Where:
public static void openSessionStartTx()
throws DaoInitializeException, DaoTxException {
try {
openSession();
} catch (DaoInitializeException e) {
// not interesting
}

try {
transaction_ = session_.beginTransaction();
} catch (HibernateException e) {
// not interesting
}
}
and where sava is
public Serializable save(Object o) throws DaoException {
Transaction t = null;
updateSession();
try {
Serializable s = session_.save(o);
session_.flush();
return s;
} catch (HibernateException ex) {
throw DaoExceptionUtils.translate(daoClassName_, ex);
}
}



Listing 2 (pure JDBC)
public void load(Map rowData) throws LoadingException {
try {
ExchangeRate exchangeRate = new ExchangeRate();
String eCurrency = (String) rowData.get("CODE");
exchangeRate.setCurrencyCode(eCurrency);

BigDecimal eExchangeRate =
(BigDecimal) rowData.get("EXCHANGE_RATE");
exchangeRate.setExchangeRate(eExchangeRate);

Long validFromL = (Long) rowData.get("VALID_FROM");
long validFrom = validFromL.longValue();
exchangeRate.setValidFrom(validFrom);

Long validTillL = (Long) rowData.get("VALID_TILL");
long validTill = validTillL.longValue();
exchangeRate.setValidTill(validTill);
exchangeRate.setNew(true);

PreparedStatement st =
EtlParser.connection_.prepareStatement(
"insert into exchange_rate (code,valid_from,valid_till,exchange_rate,new)"
+ " values (?,?,?,?,?)");

st.setString(1, eCurrency);
st.setLong(2, validFrom);
st.setLong(3, validTill);
st.setBigDecimal(4, eExchangeRate);
st.setInt(5, 1);
st.executeUpdate();

} catch (SQLException e) {
LOG.error("SQLException >" + e.getMessage() + "<");
}
}
}

read this :

http://blog.hibernate.org/cgi-bin/blosx ... 8/27#batch

Wow!!! I've added this session.clear() and it works 100x faster. Inserting 80 thousend of rows took only 1min 46sec (for pure JDBC solution 1min 16sec). Thank you very much!!