Fastest Way To Insert 30 Thousand Rows In A Temp Table On Sql Server With C#

I am trying to find out how I can improve my insert performance in a temporary table in SQL Server using c#. Some people are saying that I should use SQLBulkCopy however I must be

Solution 1:

Your problem may be in localTempTable.AcceptChanges(); Since it commit your changes. If you do the next , I think it will run faster

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

    }

From MSDN - DataSet.AcceptChanges

Commits all the changes made to this DataSet since it was loaded or since the last time AcceptChanges was called.

Solution 2:

I run this code myself with StopWatch objects to measure time. It’s the AcceptChanges in every iteration that makes go slow.

publicvoidMakeTable(string tableName, List<string> ids, SqlConnection connection)
{
    SqlCommandcmd=newSqlCommand("CREATE TABLE ##" + tableName + " (ID int)", connection);
    cmd.ExecuteNonQuery();

    DataTablelocalTempTable=newDataTable(tableName);

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

    System.Diagnostics.Stopwatchsw1=newSystem.Diagnostics.Stopwatch();        

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

    }
    localTempTable.AcceptChanges();
    longtemp1= sw1.ElapsedMilliseconds;
    sw1.Reset();
    using (SqlBulkCopybulkCopy=newSqlBulkCopy(connection))
    {
        bulkCopy.DestinationTableName = "##" + tableName;
        bulkCopy.WriteToServer(localTempTable);

    }
    longtemp2= sw1.ElapsedMilliseconds;
}

Result when AccpetChanges is inside foreach loop

And when it’s not

Difference is 3 orders of magnitude :)

Solution 3:

Use IDataReader and it will run even faster

instead of cmd.ExecuteNonQuery(); Execute

cmd.ExecuteReader()