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

Answer 1

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.

Answer 2

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();
}

Answer 3

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.