Firebird dotnet 批量插入
有人可以帮我加快速度吗? 我有一个名为 dsresult 的数据集(来自 csv 文件),我想将其放入 firebird 表中。 目前我一次执行 1 行,但我更愿意分批执行 500 行。 我正在使用 firebird.net 提供程序,
string connectionString = "ServerType=1;User=SYSDBA;Password=masterkey;Dialect=3;Database=MYDB.FDB";
string sql = "INSERT INTO POSTIN (NUMID, CHANGE, PLACENAME, BOXCODE, STRCODE, TOWN) VALUES (@NUMID, @CHANGE, @PLACENAME, @BOXCODE, @STRCODE, @TOWN)";
FbConnection conn = new FbConnection(connectionString)
FbCommand command = new FbCommand(sql, conn);
foreach (DataRow r in dsResult.Tables[0].Rows)
{
command.Parameters.AddWithValue("@NUMID", r["NUMID"]);
command.Parameters.AddWithValue("@CHANGE", r["CHANGE"]);
command.Parameters.AddWithValue("@PLACENAME", r["PLACENAME"]);
command.Parameters.AddWithValue("@BOXCODE", r["BOXCODE"]);
command.Parameters.AddWithValue("@STRCODE", r["STRCODE"]);
command.Parameters.AddWithValue("@TOWN", r["TOWN"]);
command.ExecuteNonQuery();
}
它需要 aaaaaaaaaaages 才能运行。 在delphi中我只会使用cachedupdates。 一次发布 500 条记录 并在第 500 次
感谢时做出承诺
can someone help me speed this up.
I have a dataset (from a csv file) called dsresult and I want to pump it into a firebird table.
Currently I am doing it 1 row at a time, but I would prefer to do this in batches of 500 rows.
I am using the firebird.net provider
string connectionString = "ServerType=1;User=SYSDBA;Password=masterkey;Dialect=3;Database=MYDB.FDB";
string sql = "INSERT INTO POSTIN (NUMID, CHANGE, PLACENAME, BOXCODE, STRCODE, TOWN) VALUES (@NUMID, @CHANGE, @PLACENAME, @BOXCODE, @STRCODE, @TOWN)";
FbConnection conn = new FbConnection(connectionString)
FbCommand command = new FbCommand(sql, conn);
foreach (DataRow r in dsResult.Tables[0].Rows)
{
command.Parameters.AddWithValue("@NUMID", r["NUMID"]);
command.Parameters.AddWithValue("@CHANGE", r["CHANGE"]);
command.Parameters.AddWithValue("@PLACENAME", r["PLACENAME"]);
command.Parameters.AddWithValue("@BOXCODE", r["BOXCODE"]);
command.Parameters.AddWithValue("@STRCODE", r["STRCODE"]);
command.Parameters.AddWithValue("@TOWN", r["TOWN"]);
command.ExecuteNonQuery();
}
it takes aaaaaaaaaaages to run.
in delphi i would have just jused cachedupdates.
post 500 records at a time
and commit on the 500th
Thanks
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
尝试这样的事情:
try something like this:
Firebird 的有线协议不支持在一批(以及一次往返)中发送更多命令。可能最好的主意是使用
EXECUTE BLOCK
(又名匿名存储过程)并在那里发送插入内容。例如:
并执行这个。
顺便说一句,
FbBatchExecution
也将一次发送一个命令。Firebird's wire protocol doesn't support sending more commands in one batch (and in one roundtrip). Probably best idea is to use
EXECUTE BLOCK
(aka anonymous stored procedure) and send inserts there.For example:
and execute this.
BTW the
FbBatchExecution
will send one command a time as well.您应该只添加参数一次,并且只在循环中更改它们的值,例如:
创建参数一次:
在循环中执行:
这确实会加快速度。
You should only add the parameters once, and only change the values off them in the loop so something like:
create the parameters once:
in the loop do:
this should really speed things up.