» The fastest way to insert 100K records
I’m doing some performance tests today with our three different database backends: MySql, Firebird and SQL Server.My goal is to find the fastest way to insert a big number of records into a table taking into account the different backends.My test table is the following in the three databases:
CREATE TABLE testtable (
iobjid BIGINT NOT NULL,
ifield0 BIGINT,
ifield1 BIGINT);
iobjid is a primary key and the other two fields are also indexed.So, let’s go with the first loop:
IDbConnection conn = // grab a connection somehow
conn.Open();
try
{
IDbCommand command = conn.CreateCommand();
command.CommandText = "delete from testtable";
command.ExecuteNonQuery();
int initTime = Environment.TickCount;
IDbTransaction t = conn.BeginTransaction(
IsolationLevel.RepeatableRead);
command.Transaction = t;
for( int i = 1; i < 100000; i++)
{
command.CommandText = string.Format(
"INSERT INTO testtable "+
" (iobjid, ifield0, ifield1)"+
" VALUES ( {0}, {1}, {2} )",
i, 300000-i, 50000 + i);
command.ExecuteNonQuery();
}
t.Commit();
Console.WriteLine("{0} ms", Environment.TickCount - initTime);
}
finally
{
conn.Close();
}
How long does it take to insert 100K records on my old laptop?
· Firebird 2.0.1 (embedded) -> 38s
· SQL Server 2005 -> 28s
· MySql 5.0.1 -> 40sI’ve repeated the test with all the possible IsolationLevel values and didn’t find any difference.Insert with paramsMy second test tries to get a better result using parameters on the commands... Here is the code:
IDbConnection conn = //get your connection
conn.Open();
try
{
IDbCommand command = conn.CreateCommand();
command.CommandText = "delete from testtable";
command.ExecuteNonQuery();
int initTime = Environment.TickCount;
IDbTransaction t = conn.BeginTransaction(
IsolationLevel.RepeatableRead);
command.Transaction = t;
// sqlserver and firebird use ‘@’ but mysql uses ‘?’
string indexParamName =
GetParametersNamePrefix() + "pk";
string field0ParamName =
GetParametersNamePrefix() + "field0";
string field1ParamName =
GetParametersNamePrefix() + "field1";
command.CommandText = string.Format(
"INSERT INTO testtable "+
" (iobjid, ifield0, field1) "+
"VALUES ( {0}, {1}, {2} )",
indexParamName, markerParamName, revisionParamName);
IDbDataParameter paramIndex = command.CreateParameter();
paramIndex.ParameterName = indexParamName;
command.Parameters.Add(paramIndex);
IDbDataParameter paramField0 = command.CreateParameter();
paramField0.ParameterName = field0ParamName;
command.Parameters.Add(paramField0);
IDbDataParameter paramField1 = command.CreateParameter();
paramField1.ParameterName = field1ParamName;
command.Parameters.Add(paramField1);
for( int i = 0; i < 100000; i++)
{
paramIndex.Value = i;
paramField0.Value = 300000 -i;
paramField1.Value = 50000 + i;
command.ExecuteNonQuery();
}
t.Commit();
Console.WriteLine("{0} ms",
Environment.TickCount - initTime);
}
finally
{
conn.Close();
}
How long does it take now?
· Firebird -> 19s
· SQL Server-> 20s
· MySql -> 40sSo, it seems MySql is not affected by parameters, but the other two really get a performance boost!One insert to rule them allLet’s now try a last option: what about inserting all the values in a single operation? Unfortunately neither SQLServer nor Firebird support multiple rows in the values part of an insert. I know they can use some sort of union clause to do something similar, but performance is not better.So, let’s try with MySql:
IDbConnection conn = // grab your conn
conn.Open();
try
{
IDbCommand command = conn.CreateCommand();
command.CommandText = "delete from testtable";
command.ExecuteNonQuery();
int initTime = Environment.TickCount;
IDbTransaction t = conn.BeginTransaction(
IsolationLevel.RepeatableRead);
command.Transaction = t;
StringBuilder builder = new StringBuilder();
builder.Append(string.Format(
"INSERT INTO testtable "+
" (iobjid, ifield0, ifield1) "+
"VALUES ( {0}, {1}, {2} )",
0, 300000, 50000));
for( int i = 1; i < 100000; i++)
{
builder.Append(string.Format(
", ( {0}, {1}, {2} )",
i, 300000-i, 50000 + i));
}
command.CommandText = builder.ToString();
command.ExecuteNonQuery();
t.Commit();
Console.WriteLine("{0} ms",
Environment.TickCount - initTime);
}
finally
{
conn.Close();
}
And the winner is... MySql takes only 9 seconds to insert the 100K records... but only using the multi-value insert operation.Enjoy!
