CODEWITHSUNDEEP
c#sqlfirebirdfirebird-.net-provider
Crudler
Crudler

Reputation: 2286

Firebird dotnet batch inserts

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

Upvotes: 3

Views: 5700

Answers (3)

cincura.net
cincura.net

Reputation: 4150

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.

Upvotes: 2

Rob Segerink
Rob Segerink

Reputation: 106

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.

Upvotes: 1

Diego
Diego

Reputation: 36126

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

Upvotes: 5

Related Questions