Insert large amount of data into database using LINQ

Question

I want to insert around 1 million records into a database using Linq in ASP.NET MVC. But when I try the following code it didn't work. It's throwing an OutOfMemoryException. And also it took 3 days in the loop. Can anyone please help me on this???

db.Database.ExecuteSqlCommand("DELETE From [HotelServices]");

DataTable tblRepeatService = new DataTable();
tblRepeatService.Columns.Add("HotelCode",typeof(System.String));
tblRepeatService.Columns.Add("Service",typeof(System.String));
tblRepeatService.Columns.Add("Category",typeof(System.String));

foreach (DataRow row in xmltable.Rows)
{
     string[] servicesarr = Regex.Split(row["PAmenities"].ToString(), ";");

     for (int a = 0; a < servicesarr.Length; a++)
     {
         tblRepeatService.Rows.Add(row["HotelCode"].ToString(), servicesarr[a], "PA");
     }

     String[] servicesarrA = Regex.Split(row["RAmenities"].ToString(), ";");

     for (int b = 0; b < servicesarrA.Length; b++)
     {
         tblRepeatService.Rows.Add(row["hotelcode"].ToString(), servicesarrA[b], "RA");
     }
}

HotelAmenties _hotelamenties;

foreach (DataRow hadr in tblRepeatService.Rows)
{
     _hotelamenties = new HotelAmenties();
     _hotelamenties.Id = Guid.NewGuid();
     _hotelamenties.ServiceName = hadr["Service"].ToString();
     _hotelamenties.HotelCode = hadr["HotelCode"].ToString();

     db.HotelAmenties.Add(_hotelamenties);
}

db.SaveChanges();

tblRepeatService table has around 1 million rows.

Use SqlBulkCopy for bulk operations - Linq-to-EF isn't optimized and designed for bulk operations. See the relevant MSDN docs for details — marc_s
– marc_s, Commented Aug 25, 2014 at 4:31
You can use EntityFramework.BulkInsert, see stackoverflow.com/questions/22332863/entityframework-bulkinsert — abatishchev
– abatishchev, Commented Aug 25, 2014 at 4:39

Corey · Accepted Answer · 2014-08-25 05:53:56Z

2

Bulk inserts like this are highly inefficient in LINQtoSQL. Every insert creates at least three objects (the DataRow, the HotelAmenities object and the tracking record for it), chewing up memory on objects you don't need.

Given that you already have a DataTable, you can use System.Data.SqlClient.SqlBulkCopy to push the content of the table to a temporary table on the SQL server, then use a single insert statement to load the data into its final destination. This is the fastest way I have found so far to move many thousands of records from memory to SQL.

answered Aug 25, 2014 at 5:53

Corey

16.6k2 gold badges38 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ronan Thibaudau · Accepted Answer · 2014-08-25 04:44:24Z

1

If performance doesn't matter and this is a 1 shot job you can stick to the way you're using. Your problem is you're only saving at the end, so entity Framework has to store and generate the SQL for 1 million operations at once, modify your code so that you save every 1000 or so inserts instead of only at the end and it should work just fine.

int i = 0;
foreach (DataRow hadr in tblRepeatService.Rows)
{ 
     _hotelamenties = new HotelAmenties();
     _hotelamenties.Id = Guid.NewGuid();
     _hotelamenties.ServiceName = hadr["Service"].ToString();
     _hotelamenties.HotelCode = hadr["HotelCode"].ToString();

     db.HotelAmenties.Add(_hotelamenties);
     if((i%1000)==0){
     db.SaveChanges();
     }
     i++;
}    
db.SaveChanges();

answered Aug 25, 2014 at 4:44

Ronan Thibaudau

3,6013 gold badges34 silver badges79 bronze badges

2 Comments

flindeberg Over a year ago

Don't forget to drop the context and create a new one! The old one keeps references anyhow...

Ronan Thibaudau Over a year ago

That may not be an issue, my gut feeling is it's throwing an OOM exception while building the sql query, not due to the object graph, but yes if what i posted doesn't work it would be a good idea to partition them by 1K beforehand and then move the using context within the foreach group loop. If this doesn't solve the user issue i'll update my answer with this

Collectives™ on Stack Overflow

Insert large amount of data into database using LINQ

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related