19

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 doing something wrong as it seems to work much slower than simply building an SQL insert string instead.

My code to create table using SQLBulkCopy is below:

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

        }
    }

This way my inserts take a long time to run. I got my inserts to work faster in another way:

I created the inserts bit as a string and joined it in my SQL create temp table statement:

Creation of inserts string:

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;

    }

Using them in the SQL statement after creating query:

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

Since I've seen people telling me (on stack exchange https://dba.stackexchange.com/questions/44217/fastest-way-to-insert-30-thousand-rows-in-sql-server/44222?noredirect=1#comment78137_44222 ) That I should use SQLBulkCopy and that would be faster I believe that I should improve the way I do it. So if anyone can suggest how I can improve my SQLBulkCopy code OR tell me if there is a better insert statement that can improve my application's performance that would be great.

Community
  • 1
  • 1
Jenninha
  • 1,357
  • 2
  • 20
  • 42
  • Where does the data for this list of strings come from in the first place? – Dan Bracuk Jun 10 '13 at 16:35
  • It will be a set of keys that will come from another application. I haven't done this link yet. For now it's an array that I created with some ids for test. – Jenninha Jun 10 '13 at 16:38
  • 30,000 id's probably come from a database somewhere. If so, I'd be looking for a way to do all this with sql. – Dan Bracuk Jun 10 '13 at 16:45
  • 1
    It probably won't come from SQL, that's why the need to create a temporary table for my case. – Jenninha Jun 10 '13 at 16:48

3 Answers3

17

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.

Mzf
  • 5,210
  • 2
  • 24
  • 37
  • It did run faster. Thanks! – Jenninha Jun 11 '13 at 08:48
  • For some metrics - I implemented this code for a process that inserted 400,000 records and it takes about 3-4 seconds to finish (3.866 according to SQL Server Profiler), which was about half the time of a previous implementation (using an indexed temp table) – Timothy G. May 24 '22 at 16:57
6

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

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

Result when AccpetChanges is inside foreach loop

enter image description here

And when it’s not

enter image description here

Difference is 3 orders of magnitude :)

Ron Biggs
  • 353
  • 3
  • 6
0

Use IDataReader and it will run even faster

instead of cmd.ExecuteNonQuery(); Execute

cmd.ExecuteReader()
Enfantcool
  • 221
  • 1
  • 6
  • 3
    You are right that using a IDataReader is better than doing a DataTable but you are incorrect about doing `cmd.ExectuteReader()`, it still should be `cmd.ExecuteNonQuery();` because the code does not actually return any result sets. – Scott Chamberlain Jul 07 '16 at 19:13