Le moyen le plus rapide d'insérer 30 000 lignes dans une table temporaire sur SQL Server avec C #

bulkinsert c# sql sqlbulkcopy sql-server

Question

J'essaie de découvrir comment améliorer les performances d'insertion dans une table temporaire dans SQL Server à l'aide de c #. Certaines personnes disent que je devrais utiliser SQLBulkCopy, mais je dois faire quelque chose de mal car cela semble fonctionner beaucoup plus lentement que la simple construction d'une chaîne d'insertion SQL à la place.

Mon code pour créer une table à l'aide de SQLBulkCopy est ci-dessous:

public void MakeTable(string tableName, List<string> ids, SqlConnection connection)
    {

        SqlCommand cmd = new SqlCommand("CREATE TABLE ##" + tableName + " (ID int)", connection);
        cmd.ExecuteNonQuery();

        DataTable localTempTable = new DataTable(tableName);

        DataColumn id = new DataColumn();
        id.DataType = System.Type.GetType("System.Int32");
        id.ColumnName = "ID";
        localTempTable.Columns.Add(id);

        foreach (var item in ids)
        {
             DataRow row = localTempTable.NewRow();
             row[0] = item;
             localTempTable.Rows.Add(row);
             localTempTable.AcceptChanges();
        }


        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
        {
            bulkCopy.DestinationTableName = "##" + tableName;
            bulkCopy.WriteToServer(localTempTable);

        }
    }

De cette façon, mes inserts prennent beaucoup de temps à courir. J'ai obtenu mes inserts pour travailler plus vite d'une autre manière:

J'ai créé le bit inserts en tant que chaîne et l'ai rejoint dans mon instruction SQL create temp table:

Création de chaîne d'inserts:

public string prepareInserts(string tableName, List<string> ids)
    {
        List<string> inserts = new List<string>();

        var total = ids.Select(p => p).Count();
        var size = 1000;

        var insert = 1;

        var skip = size * (insert - 1);

        var canPage = skip < total;

        while (canPage)
        {
            inserts.Add(" insert into ##" + tableName + @" (ID) values " + String.Join(",", ids.Select(p => string.Format("({0})", p))
                        .Skip(skip)
                        .Take(size)
                        .ToArray()));
            insert++;
            skip = size * (insert - 1);
            canPage = skip < total;
        }

        string joinedInserts = String.Join("\r\n", inserts.ToArray());

        return joinedInserts;

    }

Utilisation dans l'instruction SQL après la création de la requête:

inserts = prepareInserts(tableName, ids);

var query = @"IF EXISTS
                                            (
                                            SELECT *
                                            FROM tempdb.dbo.sysobjects
                                            WHERE ID = OBJECT_ID(N'tempdb..##" + tableName + @"')
                                            )
                                                BEGIN
                                                    DELETE FROM ##" + tableName + @"
                                                END
                                            ELSE
                                                BEGIN
                                                    CREATE TABLE ##" + tableName + @"
                                                    (ID int)
                                                END " + inserts;

            var command = new SqlCommand(query, sqlConnection);
...

Depuis que j'ai vu des gens me dire (sur l’échange de pile https://dba.stackexchange.com/questions/44217/fastest-way-to-insert-30-thousand-rows-in-sql-server/44222?noredirect= 1 # comment78137_44222 ) Que je devrais utiliser SQLBulkCopy et que ce serait plus rapide, je pense que je devrais améliorer ma façon de le faire. Donc, si quelqu'un peut suggérer comment je peux améliorer mon code SQLBulkCopy OU me dire s'il existe une meilleure instruction insert pouvant améliorer les performances de mon application, ce qui serait formidable.

Réponse acceptée

Votre problème peut être dans localTempTable.AcceptChanges(); Depuis qu'il commet vos modifications.
Si vous faites la prochaine, je pense que ça ira plus vite

    foreach (var item in ids)
    {
         DataRow row = localTempTable.NewRow();
         row[0] = item;
         localTempTable.Rows.Add(row);

    }

    localTempTable.AcceptChanges();

    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
    {
        bulkCopy.DestinationTableName = "##" + tableName;
        bulkCopy.WriteToServer(localTempTable);

    }

De MSDN - DataSet.AcceptChanges

Valide toutes les modifications apportées à ce DataSet depuis son chargement ou depuis le dernier appel de AcceptChanges.


Réponse populaire

Je lance moi-même ce code avec des objets StopWatch pour mesurer le temps. Ce sont les AcceptChanges à chaque itération qui ralentissent.

public void MakeTable(string tableName, List<string> ids, SqlConnection connection)
{
    SqlCommand cmd = new SqlCommand("CREATE TABLE ##" + tableName + " (ID int)", connection);
    cmd.ExecuteNonQuery();

    DataTable localTempTable = new DataTable(tableName);

    DataColumn id = new DataColumn();
    id.DataType = System.Type.GetType("System.Int32");
    id.ColumnName = "ID";
    localTempTable.Columns.Add(id);

    System.Diagnostics.Stopwatch sw1 = new System.Diagnostics.Stopwatch();        

    sw1.Start();
    foreach (var item in ids)
    {
        DataRow row = localTempTable.NewRow();
        row[0] = item;
        localTempTable.Rows.Add(row);

    }
    localTempTable.AcceptChanges();
    long temp1 = sw1.ElapsedMilliseconds;
    sw1.Reset();
    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
    {
        bulkCopy.DestinationTableName = "##" + tableName;
        bulkCopy.WriteToServer(localTempTable);

    }
    long temp2 = sw1.ElapsedMilliseconds;
}

Résultat lorsque AccpetChanges est dans une boucle foreach

entrez la description de l'image ici

Et quand ce n'est pas

entrez la description de l'image ici

La différence est de 3 ordres de grandeur :)



Sous licence: CC-BY-SA with attribution
Non affilié à Stack Overflow
Est-ce KB légal? Oui, apprenez pourquoi
Sous licence: CC-BY-SA with attribution
Non affilié à Stack Overflow
Est-ce KB légal? Oui, apprenez pourquoi