Is there a faster way to use SqlBulkCopy than using a DataTable?

c# sqlbulkcopy sql-server

Question

I add more than a million entries to my application and do a lot of processing on them. They must all be in the memory for processing.

Then, I want to dump every record that has been edited into a blank table.

The data load in a matter of seconds, and I am left with a huge collection ofMyRecord items.

utilizing savingsSqlBulkCopy takes just a few seconds.

However SqlBulkCopy demands (I think) aDataTable - as well as placing my recordings in aDataTable is sluggish, using 7500 records per minute

dataTable.Rows.Add(myRecord.Name, myRecord.Age, ....)

Is it possible to do this intermediate stage more quickly?

1
2
11/9/2017 5:03:37 PM

Accepted Answer

You must buffer everything into a DataTable before sending it to the server, which adds to the latency. Sending the records directly to SqlBulkCopy and allowing the class to handle its own batching and buffering will improve speed.

A SqlBulkCopy and an IDataReader are compatible. You may push data that you read from any data reader to SqlBulkCopy since all ADO.NET data readers implement this interface.

In other circumstances, if your objects are organized into an IEnumerable, you may build an IDataReader on top of an IEnumerable by using Marc Gravel's ObjectReader from the FastMember package. Since this data reader loads all not at once, no data is cached until SqlBulkCopy requests it:

Using Marc Gravel as a model:

IEnumerable<SomeType> data = ... 

using(var bcp = new SqlBulkCopy(connection)) 
using(var reader = ObjectReader.Create(data, "Id", "Name", "Description")) 
{ 
  bcp.DestinationTableName = "SomeTable"; 
  bcp.WriteToServer(reader); 
}
10
11/9/2017 5:31:04 PM

Popular Answer

I'm not sure what the problem is. The program below runs quickly. I think the sluggish pace is caused by reading data rather than publishing to the DataTable.

       static void Main(string[] args)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("Col A", typeof(int));
            dt.Columns.Add("Col B", typeof(string));
            dt.Columns.Add("Col C", typeof(int));
            dt.Columns.Add("Col D", typeof(string));
            dt.Columns.Add("Col E", typeof(int));
            dt.Columns.Add("Col F", typeof(string));
            dt.Columns.Add("Col G", typeof(int));
            dt.Columns.Add("Col H", typeof(string));
            dt.Columns.Add("Col I", typeof(int));
            dt.Columns.Add("Col J", typeof(string));

            DateTime begin = DateTime.Now;

            for (int i = 0; i < 7500; i++)
            {
                dt.Rows.Add(new object[] {
                    i + 10000, "b", i + 20000, "d", i + 30000, "f", i + 40000, "h", i + 50000, "i"
                });
            }

            DateTime end = DateTime.Now;

            Console.WriteLine((end - begin).ToString());

            Console.ReadLine();
        }


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow