The fastest way to insert 100K records

The fastest way to insert 100K records

Tuesday, April 08, 2008 Pablo Santos dotnet 14 Comments

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!

Pablo Santos

I'm the CTO and Founder at Códice.
I've been leading Plastic SCM since 2005. My passion is helping teams work better through version control.
I had the opportunity to see teams from many different industries at work while I helped them improving their version control practices.
I really enjoy teaching (I've been a University professor for 6+ years) and sharing my experience in talks and articles.
And I love simple code. You can reach me at @psluaces.