Firebird dotnet batch inserts

Question

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

Have you tried putting it all in a transaction? – Mauricio Scheffer Jan 31 '12 at 13:50 — Mauricio Scheffer, Jan 31 '12 at 13:50
no, any pointers on how to do this? – Crudler Jan 31 '12 at 15:16 — Crudler, Jan 31 '12 at 15:16

score 5 · Answer 1 · answered Jan 31 '12 at 14:02

5

try something like this:

using(FbConnection c = new FbConnection(csb.ToString()))
{
    FbBatchExecution fbe = new FbBatchExecution(c);
    //loop through your commands here
    {
        fbe.SqlStatements.Add(cmd);
    }
    fbe.Execute();
}

answered Jan 31 '12 at 14:02

Diego

34,802
21
91
134

Yes! try to add your code on a different method that returns a command ready for execution and call that method on the loop – Diego Jan 31 '12 at 16:42

cincura.net · Accepted Answer · 2012-02-04T08:12:25.180

2

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:

execute block
as
begin
  insert into ...;
  insert into ...;
  ...
end

and execute this.

BTW the FbBatchExecution will send one command a time as well.

edited Feb 04 '12 at 08:12

answered Feb 02 '12 at 12:57

cincura.net

4,130
16
40

Check the link and example I added to post. – cincura.net Feb 04 '12 at 08:13

score 1 · Answer 3 · answered Feb 01 '12 at 21:36

You should only add the parameters once, and only change the values off them in the loop so something like:

create the parameters once:

command.Parameters.Add("@NUMID", FbDbType.Int)
....

in the loop do:

foreach (DataRow r in dsResult.Tables[0].Rows)
{
  command.Parameters["@NUMID"].Value = r["NUMID"];
  ....
}

this should really speed things up.

Firebird dotnet batch inserts

3 Answers3