Teradata JDBC driver is too slow when access CLOB access from Teradata. This is sample code I tested with my system

Third Party Software

Teradata JDBC driver is too slow when access CLOB access from Teradata. This is sample code I tested with my system

When we using Teradata jdbc driver to fetch CLOB objects from Teradata, the JDBC driver is slow and takes almost 700 secs to fetch 100 MB data across the wire. Please let me know how to resolve this issues with JDBC driver. I tested with Oracle driver, it responds in 10 secs. There is a huge difference.

CREATE MULTISET TABLE usn1_uat.IMPOASCAMPAIGN_CLOB ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      CAMPAIGNKEY DECIMAL(10,0),

      TARGETSEARCHTERM CLOB(100000) CHARACTER SET LATIN)

NO PRIMARY INDEX ;

Drivers used : terajdbc4.jar, tdgssconfig.jar

import java.sql.*;

public class SelectApp {

  public static void main(String args[]) {

      String url = "jdbc:teradata://<xx.xx.xxx.xxx>/database=usn1_uat,tmode=ANSI,charset=UTF8";

      String uname = "<USERNAME>";

      String passwd = "<PASSWORD>";

      long sTime = System.currentTimeMillis();

      String statement = "SELECT campaignkey, TARGETSEARCHTERM from ImpOasCampaign_CLOB where TARGETSEARCHTERM is not null";

      Boolean flag = fetchStatement(url, uname, passwd, statement);

      if (flag.TRUE)

      {

          System.out.println("SUCCESS");  

      }

      long rTime = (System.currentTimeMillis() - sTime) / 1000;

        System.out.println("*** End Fetch time (" + rTime

                + ") ***");

        System.out.println("size after " );

        System.gc();

  }

  public static Boolean fetchStatement(String url, String uname, String passwd, String statement){

      Clob myClob = null;

      String clob_portion = null;

      int clob_total = 0;

      try {

         Class.forName("com.teradata.jdbc.TeraDriver");

       }

      catch( Exception e ) {

         System.out.println("Failed to load Teradata driver.");

         return null;

       }

       try {

          Connection con = DriverManager.getConnection(url, uname, passwd);

          Statement select = con.createStatement();

          select.setFetchSize(1857408);

          ResultSet result = select.executeQuery(statement);         

          //System.out.println("Got results:");

          //System.out.println("val = " + result.getFetchSize());

          while(result.next()) { // process results one row at a time

              //This reads the campaign key from teradata database

             //System.out.println("KEY ==>" + result.getBigDecimal("campaignkey"));

             // to read clob object from teradta

             //System.out.println("VALUE ==>" + result.getClob("TARGETSEARCHTERM").length());

             // Now moving a CLOB object from Teradata to Java application.

             myClob = result.getClob("TARGETSEARCHTERM");

             // Not we read from clob and convert into UTF-8, This the one of the way to convert string into UTF-8 String.

             // There are plenty of ways, so we have a solution.

             int clob_length = (int) result.getClob("TARGETSEARCHTERM").length();

             clob_total += clob_length;

              clob_portion = new String(myClob.getSubString(1,clob_length).getBytes("UTF-8"));

          }

          System.out.println("Total clob size" + clob_total);

          select.close();

          con.close();

          return true;

        }

        catch( Exception e ) {

          e.printStackTrace();

          return null;

        }

      }

}

1 REPLY
Teradata Employee

Re: Teradata JDBC driver is too slow when access CLOB access from Teradata. This is sample code I tested with my system

Some more information would be helpful...

What version of the Teradata Database are you using? What version of the Teradata JDBC Driver are you using?

How many rows did your query select? How big is each CLOB value in each row?