How can I bulk insert?

问题:

I want to insert data to TERADATA with jdbc.But it is slow. How can I make it faster?

I wrote this code:

connection_tera= DriverManager.getConnection
             (
             "jdbc:teradata://192.168.x.xx/database=DBC,tmode=ANSI,charset=UTF8","dbc","dbc"
             ); 

stmt_tera = connection_tera.prepareStatement("insert into a.b values(?)");
//some code here to start while loop 
  stmt_tera.setObject(i,reset.getobject(i));
  stmt_tera.addBatch();  
 if(addedBatchNumber%100==0)
  stmt_tera.executeBatch(); 
  connection_tera.commit(); 
  stmt_tera.clearBatch();
//some code here and finish while loop

Should I add paramater like TYPE=FASTLOAD to connection string? or something else?

回答1:

If you are loading to an empty table I would consider using JDBC FastLoad. For more details on the performance of JDBC to insert data into a Teradata table please refer to the following article on the Teradata Developer Exchange: Speed up your JDBC/ODBC Applications

If your table is not empty, it may make sense to load the data to a staging (intermediate) table that is empty first. Then use the ANSI MERGE operation to apply the INSERT/UPDATE logic to the target table. The MERGE operation will perform faster than the traditional INSERT and UPDATE statements because the operation works at the block level instead of row level. In some instances you can even avoid spooling the source data before the data is applied to the target table.

Here is a collection of sample Teradata JDBC Driver programs. Programs 205 through 209 are examples of using FastLoad.

回答2:

Additionally you can also consider another side of the coin..Meaning you can think of performing multiple row insert with single query

insert into table1 (First,Last) values ('Fred','Smith'),
  ('John','Smith'),
  ('Michael','Smith'),
  ('Robert','Smith');

The benefits are

• Connecting/interacting with database is an expensive operation. Say you have to insert 100 rows using your code so you would write your application in such a way to fire 100 quires( 100 db interactions ).. Instead of this, build your sql query as mentioned above and try insert and check the performance.
• You are avoiding n number of database interactions.
• Insert operation is seamlessly faster if you do like this.. This has been widely adopted technique to restore/import databases.

Hope this will be helpful.. Cheers!

Cheers!

回答3:

If I'm reading this correctly, you are executing and committing a batch that has only one insert statement in it - I don't think that is your intention ( or, if it is, I think you are misunderstanding how batches are expected to be used )

Seems like you need to have an inner loop that adds an arbitrary number of statements to the batch which you then submit via executeBatch()