Is it possible to insert large amount of data using linq-to-sql?

Question

I need to insert large amount of data into SqlServer 2008. My project is based on linq-to-sql.

I process csv file with 100.000 rows. Each row is mapped to Order object. Order contains also collection of Category and Code objects. I need to map each row to object in order to validate it.

Then I need to insert all these objects into database.

List<Order> orders = Import("test.csv");
db.Orders.InsertAllOnSubmit(orders);
db.SubmitChanges();

OR

foreach(Order order in orders)
db.Orders.InsertOnSubmit(order);
db.SubmitChanges();

Both ways are slow. Is there any workaround? I may use other approach than l2sql for this task.

I read about SqlBulkCopy class - would it handle inserting child entities as well?

Answer 1

Hey,

This CSV reader was really fast for me: http://www.codeproject.com/KB/database/CsvReader.aspx

But yes, a bulk copy operation utilizing only SQL Server would be faster if you have the option to.

LINQ to SQL doesn't have a bulk update capability that I'm aware of... you have to iterate through.

HTH.

Answer 2

As @Brian points out LINQ to SQL does not do bulk insert, but this blog talks about away to get it to work.

The author seems to have added the code since I first read it (it's from 2008).

Answer 3

I think it is better to insert objects by groups with, for instance, 1000 objects, then dispose session.

Performance here is balanced between two edges: memory overusing caused by keeping all 100,000 objects in memory at one side, and time for creating session and reconnecting database at another side.

By the way, there is no significant difference between session.InsertAllOnSubmit(data) and foreach(var i in data) session.Insert(i).

Answer 4

Try using smaller transactions.

foreach(List<Order> orderbatch in orders.Batch(100))
{
  db.Orders.InsertOnSubmit(orderbatch); 
  db.SubmitChanges();   
}


public static IEnumerable<List<T>> Batch<T>(this IEnumerable<T> source, int batchAmount)
{
  List<T> result = new List<T>();
  foreach(T t in source)
  {
    result.Add(t);
    if (result.Count == batchSize)
    {
      yield return result;
      result = new List<T>();
    }
  }
  if (result.Any())
  {
    yield return result;
  }
}