bad performance using CLOBs with DB2

steckemetz · **Posted:** Wed Jun 14, 2006 4:26 am

Hi,

hibernate version: 3.1.2
DB2 version, 7.* and 8.*

We have a performance problem when reading CLOBs in DB2.
In short:

a simple table tclob with two colums:
id STRING,
text CLOB(4000)

two queries:

Quote:

select id, text from tclob

Quote:

select id, varchar(text,4000) from tclob

using the second query we are up to 10 times faster.

Here is the test class:

Code:

public class TestClob {
   private String id;
   private String text;
   
   public TestClob() {
   }
   
   public String getId() {
      return id;
   }
   public void setId(String aId) {
      this.id = aId;
   }
   public String getText() {
      return text;
   }
   public void setText(String aText) {
      this.text = aText;
   }
}

Here is the mapping:

Code:

<?xml version="1.0"?>

<!DOCTYPE hibernate-mapping PUBLIC 
   "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
   "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>

   <class name="de.ifbfag.base.db.clob.TestClob" table="tclob" lazy="false">

      <id name="id" type="java.lang.String">
         <generator class="assigned" />
      </id>

       <property name="text" type="text" lazy="false">
            <column name="text" length="4000" />
        </property>
      </property>-->
   </class>

</hibernate-mapping>

Here is the testcode:

Code:

StringBuffer textBuf = new StringBuffer();
for (int i = 0; i < 2000; i++)
   textBuf.append('t');
String text = textBuf.toString();

long t0 = System.currentTimeMillis();
for (int i = 0; i < 2000; i++) {
   TestClob t = new TestClob();
   t.setId("t" + i); //$NON-NLS-1$
   t.setText(text);
   session.save(t);         
}
commit();
long t1 = System.currentTimeMillis();
System.out.println("save: " + (float)(t1-t0)/1000 + " secs.");
      
query = session.createQuery("from " + TestClob.class.getName());
List list = query.list();
long t2 = System.currentTimeMillis();
System.out.println("hql query: " + (float)(t2-t1)/1000 + " secs.");

query = session.createSQLQuery("select * from ${sqlid}.tclob");
list = query.list();
for (Iterator iter = list.iterator(); iter.hasNext();) {
   Object[] element = (Object[]) iter.next();
   SerializableClob object = (SerializableClob) element[1];
   try {
      long length = object.length();
      String subString = object.getSubString(1, (int) length);
   }
   catch (SQLException e) {
      getLogger().logException(e);
   }
}
long t3 = System.currentTimeMillis();
System.out.println("sql query: " + (float)(t3-t2)/1000 + " secs.");

sqlQuery = session.createSQLQuery("select id, VARCHAR(text,4000) from ${sqlid}.tclob");

list = sqlQuery.list();
for (Iterator iter = list.iterator(); iter.hasNext();) {
   Object[] element = (Object[]) iter.next();
   String str = (String) element[1];
   long length = str.length();
}
long t4 = System.currentTimeMillis();
System.out.println("sql query using cast: " + (float)(t4-t3)/1000 + " secs.");

Here is the result:

Code:

save: 2.313 secs.
hql query: 2.484 secs.
sql query: 1.437 secs.
sql query mit cast: 0.297 secs.

We already searched the forum, but nobody else seems to have these problems.
We have approx. 100 tables using clobs.

Here are some remarks and possible solutions:

1. We are getting a times 2 better performance using our own ClobUserTyp with the single modification
of:

Code:

   public Object get(ResultSet rs, String name) throws HibernateException,
         SQLException {
      String value = rs.getString(name);
      return rs.wasNull() ? null : value;
   }

2. When using a formula in the mapping reading speed is certainly ok:

Code:

      <property name="text" type="text" formula="varchar(text,4000)">
      </property>

But writing is disabled, so this cannot be a solution.

3.

Code:

      <property name="text" type="text" column="varchar(text,4000)">
      </property>

does work when writing.

I thought about some more or less insane solutions:

1. Mapping the text property twice:

Code:

      <property name="text" type="ClobWriteType">
      </property>
      <property name="textRead" type="text" formula="varchar(text,4000)">
      </property>

Where the first is responsible for writing, the second for reading.
ClobWriteType would do nothing on read. textRead setter and getter are mapped
to text. This helps a bit, but since the select already contains the column text
without cast, it is slow.

2. Mapping the classes twice: one mapping for reading and one for writing.

Perhaps there are some more solution to this ?
I would be happy for help.

Barthel Steckemetz