C#múltiples insertos paralelos en la base de datos

multithreading ado.net

Question

Tengo una tabla de datos con alrededor de 3000 filas. Cada una de esas filas debe insertarse en una tabla de base de datos. Actualmente, estoy ejecutando un bucle foreach como debajo:

obj_AseCommand.CommandText = sql_proc;
obj_AseCommand.CommandType = CommandType.StoredProcedure;
obj_AseCommand.Connection = db_Conn;
obj_AseCommand.Connection.Open();

foreach (DataRow dr in dt.Rows)                
{
    obj_AseCommand.Parameters.AddWithValue("@a", dr["a"]);
    obj_AseCommand.Parameters.AddWithValue("@b", dr["b"]);
    obj_AseCommand.Parameters.AddWithValue("@c", dr["c"]);

    obj_AseCommand.ExecuteNonQuery();
    obj_AseCommand.Parameters.Clear();
}

obj_AseCommand.Connection.Close();

¿Puede aconsejar cómo puedo ejecutar paralelamente el SP en la base de datos ya que el enfoque anterior toma alrededor de 10 minutos para insertar 3000 filas?

Answer 1

Puede usar SqlBulkCopy . Vea el código de muestra a continuación. El método WriteToServer , escribe The datatable en la base de datos, siempre que sean de la misma asignación

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(ConSQL)) {
if (ConSQL.State == ConnectionState.Closed) {
    ConSQL.Open();
}

bulkCopy.ColumnMappings.Add(0, 0);
bulkCopy.ColumnMappings.Add(1, 1);
bulkCopy.ColumnMappings.Add(2, 2);

bulkCopy.DestinationTableName = "dbo.TableName";

bulkCopy.WriteToServer(dataTable);

bulkCopy.Close(); //redundant - since using will dispose the object

}

Answer 2

Puedes usar SqlBulkCopy

guía está aquí

Answer 3

es mejor pasar toda la tabla de datos a la base de datos

obj_AseCommand.CommandText = sql_proc;
obj_AseCommand.CommandType = CommandType.StoredProcedure;
obj_AseCommand.Connection = db_Conn;
obj_AseCommand.Connection.Open();
obj_AseCommand.Parameters.AddWithValue("@Parametername",DataTable);
obj_AseCommand.ExecuteNonQuery();

en la base de datos debe crear el tipo de tabla que coincida exactamente con su tabla de datos

CREATE TYPE EmpType AS TABLE 
(
    ID INT, Name VARCHAR(3000), Address VARCHAR(8000), Operation SMALLINT //your columns
)

en el procedimiento de la tienda puede hacer algo como esto ...

create PROCEDURE demo

@Details EmpType READONLY // it must be read only
AS
BEGIN
    insert into yourtable   //insert data
    select * from @Details 
    END

Answer 4

Nota al margen: 10 minutos para 3000 filas es excesivo incluso con una tabla ancha y un solo hilo. ¿Qué hace tu proceso? Supuse que el procesamiento no es trivial, de ahí la necesidad del SPROC, pero si solo está haciendo inserciones simples, según el comentario de @ 3dd, SqlBulkCopy producirá insertos de ~ 1M filas por minuto en una tabla razonablemente estrecha.

Puede hacer esto en paralelo usando TPL, por ejemplo, específicamente con la sobrecarga localInit de Parallel.ForEach :

Parallel.ForEach(dt.Rows,
    () =>
    {
        var con = new SqlConnection();
        var cmd = con.CreateCommand();
        cmd.CommandText = sql_proc;
        cmd.CommandType = CommandType.StoredProcedure;
        con.Open();

        cmd.Parameters.Add(new SqlParameter("@a", SqlDbType.Int));
        // NB : Size sensitive parameters must have size
        cmd.Parameters.Add(new SqlParameter("@b", SqlDbType.VarChar, 100));
        cmd.Parameters.Add(new SqlParameter("@c", SqlDbType.Bit));
        // Prepare won''t help with SPROCs but can improve plan caching for adhoc sql
        // cmd.Prepare();
        return new {Conn = con, Cmd = cmd};
    },
    (dr, pls, localInit) =>
    {
        localInit.Cmd.Parameters["@a"] = dr["a"];
        localInit.Cmd.Parameters["@b"] = dr["b"];
        localInit.Cmd.Parameters["@c"] = dr["c"];
        localInit.Cmd.ExecuteNonQuery();
        return localInit;
    },
    (localInit) =>
    {
        localInit.Cmd.Dispose();
        localInit.Conn.Dispose();
    });

Notas:

• A menos que realmente sepa lo que está haciendo, en general deberíamos dejar TPL para decidir el grado de paralelismo. Sin embargo, dependiendo de la cantidad de contención (léase: bloqueos para el trabajo de la base de datos) de los recursos, puede ser necesario restringir el límite superior de las tareas concurrentes (la prueba y el error pueden ser útiles, por ejemplo, intente con concurrencias de 4, 8, 16 tareas concurrentes, etc. ver cuál proporciona la mayor producción y controlar el bloqueo y la carga de la CPU en su servidor Sql.
• Del mismo modo, dejar el particionador predeterminado de TPL suele ser lo suficientemente bueno como para dividir las DataRows entre las tareas.
• Cada tarea necesitará su propia conexión Sql.
• En lugar de crear y eliminar el comando en cada llamada, créelo una vez por tarea y luego siga reutilizando el mismo Comando, solo actualice los parámetros cada vez.
• Utilice LocalInit / Local Finally lambdas para hacer por tarea de configuración y limpieza, como la eliminación de comandos y conexiones.
• También podría considerar usar .Prepare() si está utilizando las versiones AdHoc Sql o Sql anteriores a 2005.
• Supongo que enumerar DataTable''s filas DataTable''s es seguro para subprocesos. Querrá verificar esto por supuesto, por supuesto.