我如何批量插入？ [英] How can I bulk insert?

问题描述

我想用jdbc将数据插入TERADATA。但它很慢。我怎样才能让它更快？

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

我写了这段代码：

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

我应该添加类似TYPE = FASTLOAD的参数连接字符串？或其他什么？

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

推荐答案

如果要加载到空表，我会考虑使用JDBC FastLoad 。有关将数据插入Teradata表的JDBC性能的更多详细信息，请参阅Teradata Developer Exchange上的以下文章： 加速JDBC / ODBC应用程序

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

如果您的表不是为空，将数据加载到首先为空的登台（中间）表可能是有意义的。然后使用ANSI MERGE 操作将 INSERT / UPDATE 逻辑应用于目标表。 MERGE 操作的执行速度将快于传统的 INSERT 和 UPDATE 语句，因为该操作在块级而不是行级工作。在某些情况下，您甚至可以在将数据应用于目标表之前避免假脱机源数据。

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.

这是一个 集合 。程序205到209是使用FastLoad的示例。

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

这篇关于我如何批量插入？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！