java Sybase JConnect：ENABLE_BULK_LOAD 用法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2112395/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sybase JConnect: ENABLE_BULK_LOAD usage
提问by Chris Kannon
Can anyone out there provide an example of bulk inserts via JConnect (with ENABLE_BULK_LOAD) to Sybase ASE?
任何人都可以通过 JConnect (with ENABLE_BULK_LOAD) 向 Sybase ASE提供批量插入的示例吗？
I've scoured the internet and found nothing.
我在互联网上搜索过，一无所获。
回答by Chris Kannon
I got in touch with one of the engineers at Sybase and they provided me a code sample. So, I get to answer my own question.
我与 Sybase 的一位工程师取得了联系，他们为我提供了一个代码示例。所以，我可以回答我自己的问题。
Basically here is a rundown, as the code sample is pretty large... This assumes a lot of pre initialized variables, but otherwise it would be a few hundred lines. Anyone interested should get the idea. This can yield up to 22K insertions a second in a perfect world (as per Sybase anyway).
基本上这里是一个纲要，因为代码示例非常大......这假设有很多预初始化的变量，否则它将是几百行。任何有兴趣的人都应该明白这一点。在理想情况下，这每秒最多可以产生 22K 次插入（无论如何按照 Sybase）。
SybDriver sybDriver = (SybDriver) Class.forName("com.sybase.jdbc3.jdbc.SybDriver").newInstance();
sybDriver.setVersion(com.sybase.jdbcx.SybDriver.VERSION_6);
DriverManager.registerDriver(sybDriver);
//DBProps (after including normal login/password etc.
props.put("ENABLE_BULK_LOAD","true");
//open connection here for sybDriver
dbConn.setAutoCommit(false);
String SQLString = "insert into batch_inserts (row_id, colname1, colname2)\n values (?,?,?) \n";
PreparedStatement pstmt;
try
{
pstmt = dbConn.prepareStatement(SQLString);
}
catch (SQLException sqle)
{
displaySQLEx("Couldn't prepare statement",sqle);
return;
}
for (String[] val : valuesToInsert)
{
pstmt.setString(1, val[0]); //row_id varchar(30)
pstmt.setString(2, val[1]);//logical_server varchar(30)
pstmt.setString(3, val[2]); //client_host varchar(30)
try
{
pstmt.addBatch();
}
catch (SQLException sqle)
{
displaySQLEx("Failed to build batch",sqle);
break;
}
}
try {
pstmt.executeBatch();
dbConn.commit();
pstmt.close();
} catch (SQLException sqle) {
//handle
}
try {
if (dbConn != null)
dbConn.close();
} catch (Exception e) {
//handle
}
回答by rod howard
After following most of your advice we didn't see any improvement over simply creating a massive string and sending that across in batches of ~100-1000rows with a surrounding transaction. we got around: *Big String Method [5000rows in 500batches]: 1716ms = ~2914rows per second. (this is shit!).
在遵循您的大部分建议之后，我们没有看到与简单地创建一个巨大的字符串并通过周围的事务以大约 100-1000 行的批次发送它相比有任何改进。我们解决了：*大字符串方法 [500 批次中的 5000 行]：1716 毫秒 = 每秒约 2914 行。（这是狗屎！）。
Our db is sitting on a virtual host with one CPU (i7 underneath) and the table schema is:
我们的数据库位于一个带有一个 CPU（下面是 i7）的虚拟主机上，表架构是：
CREATE TABLE
archive_account_transactions
(
account_transaction_id INT,
entered_by INT,
account_id INT,
transaction_type_id INT,
DATE DATETIME,
product_id INT,
amount float,
contract_id INT NULL,
note CHAR(255) NULL
)
with four indexes on account_transaction_id (pk), account_id, DATE, contract_id.
在 account_transaction_id (pk)、account_id、DATE、contract_id 上有四个索引。
Just thought I would post a few comments first we're connecting using:
只是想我会先发表一些评论，我们正在使用以下方法进行连接：
jdbc:sybase:Tds:40.1.1.2:5000/ikp?EnableBatchWorkaround=true;ENABLE_BULK_LOAD=true
we did also try the .addBatch syntax described above but it was marginally slower than just using java StringBuilder to build the batch in sql manually and then just push it across in one execute statement. Removing the column names in the insert statement gave us a surprisingly large performance boost it seemed to be the only thing that actually effected the performance. As the Enable_bulk_load param didn't seem to effect it at all nor did the EnableBatchWorkaround we also tried DYNAMIC_PREPARE=false which sounded promising but also didn't seem to do anything.
我们也尝试了上面描述的 .addBatch 语法，但它比仅使用 java StringBuilder 在 sql 中手动构建批处理稍慢，然后在一个执行语句中将其推送。删除插入语句中的列名给我们带来了惊人的巨大性能提升，这似乎是唯一真正影响性能的事情。由于 Enable_bulk_load 参数似乎根本没有影响它，EnableBatchWorkaround 我们也尝试了 DYNAMIC_PREPARE=false ，这听起来很有希望，但似乎也没有做任何事情。
Any help getting these parameters actually functioning would be great! In other words are there any tests we could run to verify that they are in effect? I'm still convinced that this performance isn't close to pushing the boundaries of sybase as mysql out of the box does more like 16,000rows per second using the same "big string method" with the same schema.
任何让这些参数实际运行的帮助都会很棒！换句话说，我们是否可以运行任何测试来验证它们是否有效？我仍然相信这种性能并没有接近推动 sybase 的界限，因为开箱即用的 mysql 使用相同模式的相同“大字符串方法”每秒更像是每秒 16,000 行。
Cheers Rod
干杯棒
回答by DmitryA
In order to get the sample provided by Chris Kannon working, do not forget to disable auto commit mode first:
为了让 Chris Kannon 提供的示例工作，不要忘记先禁用自动提交模式：
dbConn.setAutoCommit(false);
And place the following line before dbConn.commit():
并将以下行放在 dbConn.commit() 之前：
pstmt.executeBatch();
Otherwise this technique will only slowdown the insertion.
否则这种技术只会减慢插入速度。
回答by Yardena
Don't know how to do this in Java, but you can bulk-load text files with LOAD TABLE SQL statement. We did it with Sybase ASA over JConnect.
不知道如何在 Java 中执行此操作，但您可以使用 LOAD TABLE SQL 语句批量加载文本文件。我们通过 JConnect 使用 Sybase ASA 做到了这一点。
回答by Gladwin Burboz
Support for Batch Updates
支持批量更新
Batch updates allow a Statement object to submit multiple update commands as one unit (batch) to an underlying database for processing together.
批量更新允许一个 Statement 对象将多个更新命令作为一个单元（批量）提交给底层数据库一起处理。
Note:To use batch updates, you must refresh the SQL scripts in the sp directory under your jConnect installation directory. CHAPTER
注意：要使用批量更新，必须刷新 jConnect 安装目录下 sp 目录中的 SQL 脚本。章节
See BatchUpdates.java in the sample(jConnect 4.x) and sample2 (jConnect 5.x) subdirectories for an example of using batch updates with Statement, PreparedStatement, and CallableStatement. jConnect also supports dynamic PreparedStatements in batch.
有关将批处理更新与 Statement、PreparedStatement 和 CallableStatement 结合使用的示例，请参见示例(jConnect 4.x) 和 sample2 (jConnect 5.x) 子目录中的BatchUpdates.java。jConnect 还支持批量动态 PreparedStatements。
Reference:
参考：
http://download.sybase.com/pdfdocs/jcg0420e/prjdbc.pdf
http://download.sybase.com/pdfdocs/jcg0420e/prjdbc.pdf
.
.
Other Batch Update Resources
其他批量更新资源
http://java.sun.com/j2se/1.3/docs/guide/jdbc/spec2/jdbc2.1.frame6.html
http://java.sun.com/j2se/1.3/docs/guide/jdbc/spec2/jdbc2.1.frame6.html
