Reading Clob column using teradata JDBC is too slow

Connectivity

Reading Clob column using teradata JDBC is too slow

Hi,

I am using the following code to read clob data from teradata. 

String ClobString;
StringBuilder clobStringBuilder = new StringBuilder();
Clob clobData = rs.getClob("clobcolumn");
if(clobData != null) {
int length = (int) rs.getClob("clobcolumn").length();
int BUF_LEN = 4096;
char[] bufr = new char[length];
BufferedReader buffReader = new BufferedReader(clobData.getCharacterStream());
try
{
//buffReader.read(bufr);
buffReader.read(bufr, 0, length);
//buffReader.read(bufr, 0, BUF_LEN);
/*int c = 0;
while((c=buffReader.read(bufr,0,BUF_LEN)) != -1) {
clobStringBuilder.append(bufr);
}*/

/*while (null != (clobString = buffReader.readLine()))
{
clobStringBuilder.append(clobString);
}*/
}
catch (IOException e)
{
e.printStackTrace();
}
finally
{
try
{
buffReader.close();
}
catch (IOException e)
{
e.printStackTrace();
}
clobData.free();
}
String str = new String(bufr);

I have tried various options. My table contains 230 rows with 3 clob columns and each column contains around 7000 characters.

The above code is taking between 30-35 seconds to read all data. Please let me know, if there is any efficient way of reading clob data.

teradata database : 14.10

Am using the terdata jdbc driver version : 14.0.0.40

Regards,

Suresh.

Tags (2)
4 REPLIES
Teradata Employee

Re: Reading Clob column using teradata JDBC is too slow

Reading each CLOB value requires a separate round-trip message exchange between the Teradata JDBC Driver and the Teradata Database.

If your application must read all the the character data, then using a VARCHAR data type will typically be much faster than using a CLOB data type.

The CLOB data type will be faster than VARCHAR only when your application doesn't need to access the character data for most of the result set rows.

If your character data values are all less than 7000 characters in length, then you should consider changing the column data types from CLOB to VARCHAR.

Junior Supporter

Re: Reading Clob column using teradata JDBC is too slow

Hi Toloman,

I am researching to know the performance impact of using a clob column/XML columns in tables.

What are the situations wherein we should consider using a clob column ? What are the performance impacts of using a clob column if i want to use such a column in where condition or join conditions ? What are other implications that we should consider before designing such a table ?

If you can give me some pointers to links/materials giving some details on this, then it will be very helpful.

--Samir

Teradata Employee

Re: Reading Clob column using teradata JDBC is too slow

In the future, please create a new forum topic for a new question. Please do not add an unrelated post to old thread.

>>> What are the situations wherein we should consider using a clob column ?

Teradata Database VARCHAR columns are limited to 64KB of data. You should use a CLOB column when you need to store more than 64KB of character data in a column.

>>> What are the performance impacts of using a clob column if i want to use such a column in where condition or join conditions ?

You cannot join on a CLOB column. That is not supported by the Teradata Database.

>>> What are other implications that we should consider before designing such a table ?

Each LOB value is stored intact on a single AMP. A LOB value is never split up into pieces and stored across multiple AMPs.

If you plan to store very large character values in the CLOB column, for example, character values in the range of megabytes to 1 GB, then you will need to allocate enough perm space to the table owner so that every AMP has enough room to store those large character values.

Junior Supporter

Re: Reading Clob column using teradata JDBC is too slow

Thanks Tomnolan !!