Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: Urgent: Question on Teradata batch upsert
 
From:   Bob Hahn

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 23 Jun 2019