Archives of the TeradataForum

Message Posted: Tue, 13 Sep 2005 @ 16:09:11 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Here are 3 approaches using JDBC--multi-statement request, parameter markers, and parameter array.

Multi-statement requests won't get the benefit of the statement cache since the values are in the SQL.

Parameter array will benefit from the statement cache since the SQL text does not change from request to request.

Parameter arrays are supported in Teradata V2R6. In that case a single SQL statement is followed by many sets of related values. The performance can be quite good particularly if the pack count exceeds the amp count.

     msStatement = conn.createStatement();
     for (i =0; i < numInserts;i += packCur ) {              // per batch loop
             for (j = 0; ((j < packCur) && (j < (numInserts-i)));j++) {
                     msStatement.addBatch("INSERT INTO INSERT_TEST VALUES ("
     +
     rowSeq++ + "," +  rowSeq++ + ");");
             }
             msStatement.executeBatch();
             msStatement.clearBatch();
             msStatement.close();
     }
     conn.commit();



     for (i =0; i < numInserts;i += packCur ) {              // per batch loop
             myStatement.delete(0,myStatement.length());
             for (j = 0; ((j < packCur) && (j < (numInserts-i)));j++) {
                     myStatement.append("Insert into insert_test values(?,?);");
             }
             pmStatement = conn.prepareStatement(myStatement.toString());
             for (j = 0; ((j < packCur) && (j < numInserts - i));j++) {
                     pmStatement.setLong((int)j*2+1,rowSeq++);
                     pmStatement.setLong((int)j*2+2,rowSeq);
             }
             pmStatement.executeUpdate();
             pmStatement.close();
             batchSeq++;
             }
             //conn.commit();

     paStatement = conn.prepareStatement ("Insert into insert_test values (?,?)");
     for (i =0; i < numInserts;i += packCur ) {              // per batch loop
       for (j = 0; ((j < packCur) && (j < numInserts - i));j++) {
              //padColStr = padCol.toString();
              paStatement.setLong((int)1,batchSeq++);
              paStatement.setLong((int)2,batchSeq);
              paStatement.addBatch();
     }
       paStatement.executeBatch();
       paStatement.clearBatch();
     }
     conn.commit();

	<Prev	Next>		<<First	<Prev	Next>	Last>>