I am developing an application that will use firebird embedded and/or postgres depending on user sophistication. The argument for firebird embedded is that there should be less installation, firewall, UAC, etc issues. So far postgres is like walking on clouds but I have hit a roadblock with firebird. The app is .net and I am using this provider http://www.firebirdsql.org/en/net-provider/ version 3.0.2
Everything technically works but with firebird embedded I am inserting only around 100 records per second whereas with postgres it's over 3000 per second! With postgres I launch a large amount of INSERT INTO... statements as one command and it's fine. For firebird it's not going well. Here is what does work (slowly)
String query = @"INSERT INTO Customers(ID, Name, SiteID) VALUES(1,'delta',2);
INSERT INTO Customers(ID, Name, SiteID) VALUES(2,'phoenix',2);
";
FbScript fbs = new FbScript(query);
fbs.Parse();
FbConnection fbc = new FbConnection(ConnectionString);
FbBatchExecution fbe = new FbBatchExecution(fbc, fbs);
fbe.Execute(true);
However, I am trying to do without the parse. Something similar to the second answer here Run multiple insert queries against firebird database using isql or here http://www.firebirdfaq.org/faq336/
String sql = @"set term ^ ;
EXECUTE BLOCK AS BEGIN
INSERT INTO Customers(ID, Name, SiteID) VALUES(1,'delta',2);
INSERT INTO Customers(ID, Name, SiteID) VALUES(2,'phoenix',2);
end^";
FbCommand cmd = new FbCommand();
PrepareCommand(cmd, connection, (FbTransaction)null, CommandType.Text, sql, commandParameters, out mustCloseConnection);
cmd.ExecuteNonQuery();
With this I get the exception
Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, column 5
term
Is the first way the only way? It's so slow :(
You don't need the set term statements, these are isql specific thing. So try
String sql = @"EXECUTE BLOCK AS BEGIN
INSERT INTO Customers(ID, Name, SiteID) VALUES(1,'delta',2);
INSERT INTO Customers(ID, Name, SiteID) VALUES(2,'phoenix',2);
END";