La manera más rápida de insertar grabaciones de 100K

La manera más rápida de insertar grabaciones de 100K

16:46 0 Comments

Estoy haciendo hoy algunos tests de rendimiento con nuestras tres diferentes backends de bases de datos: MySql, Firebird y SQL Server.

Mi objetivo es encontrar la manera más rápida de insertar un gran número de grabaciones en una tabla teniendo en cuenta los diferentes backends.

Mi tabla de test es la siguiente en las tres bases de datos:

CREATE TABLE testtable (
iobjid BIGINT NOT NULL,
ifield0 BIGINT,
ifield1 BIGINT);

iobjid es la clave primaria y los otros dos campos también están indexados.

Así que vamos con el primer bucle:

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

¿Cuánto tiempo me ha llevado insertar grabaciones de 100k en mi antiguo portátil?

• Firebird 2.0.1 (embebido) -> 38s
• SQL Server 2005 -> 28s
• MySql 5.0.1 -> 40s

He repetido el test con todos los posibles valores en niveles de aislamiento y no encuentro ninguna diferencia.


Insertar con parámetros

Mi segundo test intenta obtener un mejor resultado usando parámetros en los comandos…Aquí está el código:

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

¿Cuánto tiempo tarda ahora?

• Firebird -> 19s
• SQL Server-> 20s
• MySql -> 40s

Parece que MySQL no resulta afectado por parámetros, ¡pero los otros dos realmente obtienen un rendimiento muy superior!

Una inserción para todos

Intentemos ahora una última opción: ¿qué pasaría si insertáramos todos los valores en una sola operación? Desafortunadamente ni el servidor SQL ni el de Firebird soportan múltiples filas en la parte de valores de una inserción. Sabemos que sí pueden usar algún tipo de cláusula de unión para hacer algo similar, pero el rendimiento no es superior.

Intentémoslo con 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();
}

Y el ganador es… MySQL tarda sólo 9 segundos en insertar las grabaciones de 100K… pero únicamente usando la operación de múltiples valores.

¡Disfrutad!