Insert operation is taking more when comapred to MySQL and PostgreSQL

Time estimates after Insert operation

1. Insert 1, 00,000 records

Insert Record
Insert Transaction Start Time: 1/12/2011 5:30:23 PM
Insert Transaction End Time: 1/12/2011 5:33:47 PM
Elapsed Time in Insert Operation: 00:07:58.5903766

2. Insert 1, 50,000 records

Insert Record
Insert Transaction Start Time: 1/12/2011 5:52:36 PM
Insert Transaction End Time: 1/12/2011 6:01:32 PM
Elapsed Time in Insert Operation: 00:08:56.3574804

3. Insert 2, 00, 000 records

Throwing exception "Unable to allocate memory from OS"

In MySQL or PostgreSQL, the same 1 or 2 Lakhs of records are inserted in seconds ~ 40 seconds.

So please help me to improve the performance using C# .NET

I will paste you the code used for Insert operation:

FbConnectionStringBuilder cs = new FbConnectionStringBuilder();
cs.UserID = "SYSDBA";
cs.Password = "masterkey";
cs.DataSource = "localhost";
cs.Database = "c:\\Shibu\\Parameters.fdb";
cs.Port = 3050;
cs.Dialect = 3;
cs.Charset = "NONE";
cs.Role = string.Empty;
cs.ConnectionLifeTime = 15;
cs.Pooling = false;
cs.MinPoolSize = 0;
cs.MaxPoolSize = 50;
cs.PacketSize = 8192;
cs.ServerType = 0;

FbConnection myConnection = new FbConnection(cs.ToString());

string insertString = "INSERT INTO DeviceInfo (id, name, type, ipaddress, currentDateTime, status) VALUES " +
"(@id, @name, @type, @ipaddress, @currentDateTime, @status)";

FbCommand insertCommand = null;
FbTransaction myTransaction = myConnection.BeginTransaction();

for (Int32 counter = 1; counter <= 100000; counter++)
{
insertCommand = new FbCommand(insertString, myConnection, myTransaction);

insertCommand.Parameters.Add("@id", counter);
insertCommand.Parameters.Add("@name", "s");
insertCommand.Parameters.Add("@type", "M");
insertCommand.Parameters.Add("@ipaddress", "S");
insertCommand.Parameters.Add("@currentDateTime", "1999-01-08 04:05:06");
insertCommand.Parameters.Add("@status", '1');
insertCommand.Parameters.Add("@sv1", 1.5);
insertCommand.Parameters.Add("@sv2", 1.5);
insertCommand.Parameters.Add("@sv3", 1.5);
insertCommand.Parameters.Add("@sv4", 1.5);

// Execute Update.
insertCommand.ExecuteNonQuery();
}

// Commit changes
myTransaction.Commit();

//Release all the resources.
myTransaction.Dispose();

So please help me.

I am not familiar with C# so I do not know whether this can be changed but
you seem to be building the query for every iteration of the loop.
I assume it would be possible to build and prepare the query once outside the loop.

Is the code identical to the code you use against the other databases?
Is the table definition, including all constraints, indexes and triggers identical to
the tables in the other databases?

Is this the real code?
These parameters do not exist in your SQL
insertCommand.Parameters.Add("@sv1", 1.5);
insertCommand.Parameters.Add("@sv2", 1.5);
insertCommand.Parameters.Add("@sv3", 1.5);
insertCommand.Parameters.Add("@sv4", 1.5);

I see no code to catch exceptions. Are you getting any?
Are all the records inserted?

What happens if you Commit (commitRetaining) every 10,000 inserts?

How can you run this code more than once, the second time through the values of
counter will cause a PK violation assuming ID is the PK?

Not much help; but some suggestions.

Clive