0

I'm using Entity Framework 5 on .NET 4.5 with SQL Server 2012. I have a function that downloads a lot of data from a 3rd party source and parses it into my database. It downloads, parses to my entities and inserts/updates to my db in batches of 2,500 records.

For the insert/update to my database, I'm making use of the SqlBulkCopy class. When it is doing the insert/update to my database, the table it's inserting/updating to seems to be partially unresponsive. If I run a simple select query from SSMS it will pull some records and then hang until the app has finished the bulk insert/update. This data in my db is fed to multiple sources via WCF service and if I try to load the data at one of the sources, it times out due to the database not responding with the data. Once the insert/update finishes, the WCF service and SSMS queries both continue to run fine.

I thought this may be due to the SqlBulkCopy so I removed the use of that class and just called a context.SaveChanges() which produces the exact same result. I then ran a test taking EF completely out of the picture and used classic ADO.NET (without SqlBulkCopy) and everything works great. I plugged the SqlBulkCopy back in, left EF out, and again, the table seems to be locked until it finishes.

So I am assuming the culprit here is both EF and SqlBulkCopy. Any ideas on how I can get around this and still use EF and SqlBulkCopy? or what may be causing this to happen?

I don't think examples of my code are necessary here, but if you would like to see, just let me know. Thanks in advance.

Improve this question
2
  • I think this is a server setting. I had this issue on a shared hosting server one of my clients uses. I could literally not even SELECT TOP 1 while the data was being transferred. It would just sit there "Executing query.." for 30-45 minutes. Commented Nov 12, 2012 at 22:49
  • This is not a shared server. The db runs in a sql cluster consisting of 2 nodes with central storage. Only one runs at a time and the other is failover. The app is hosted on 2 other load balanced dedicated web servers. The "Executing Query" for long periods of time is the same thing I experience through SSMS. If you know what setting this may be, it could be helpful. Commented Nov 12, 2012 at 23:37

1 Answer 1

Reset to default
1

I'm thinking this is because of transactions. When you use SqlBukCopy or context.SaveChanges(), the inserts happen within a transaction. When you replaced that code with classic ADO.NET, did you wrap the inserts in a transaction or did you let each Insert occur inside it's own implicit transaction?

When a transaction is pending, it locks portions of the table being updated. If it inserts enough data, it might lock a lot of pages in the table or the whole table (this is called lock escalation).

The best way around this is to split the update into smaller chunks. There are a few other options (e.g. reading uncommitted data, etc.) but those have significant downsides and should typically be avoided.

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

6 Comments

I have read about the row lock that SqlBulkInsert does by default. I can also understand it being done in transactions. However, if that is the case, wouldn't the select query still execute but exclude the records involved with the transaction? As for the ADO.NET, I did not wrap them in a transaction, they were executed in their own implicit transaction. I will try breaking it into small chunks (maybe 500?) and I will also try wrapping the ADO.NET version into a transaction and see the results from that. Thanks for you input.
The select query can't exclude records involved in a transaction; that would violate some of the basic tenets of data integrity relational databases adhere to. In other words, if a select needs to read data that is locked, it has to wait. Also, even though SqlBulkInsert does row locking by default, the lock can escalate to page or table locks when enough data is being inserted.
Okay, I ran some tests. I tried inserting 50,000 records using only ADO.NET and a transaction. During the comm.ExecuteNonQuery(), which is what took all the time, the table continued to respond as normal. I put a breakpoint on the transaction.Commit() line right under the ExecuteNonQuery() function and when the break point hit the commit line, the table quit responding as it does with EF & SqlBulkCopy. When I stepped over, which did not have a time delay, the table started responding as normal again. So looks like it is definitely the committing of the transaction.
I think the best solution here is going to be to not even use EF or SqlBulkCopy for the inserting and just use ADO.NET with each record inserted on their own without a transaction. The transaction is not important because if it errors halfway through, the remaining records will just be picked up in the next download. Unless you know of a way to not use transactions with EF/SqlBulkCopy, which I looked for but could not find.
You should also look into the SqlBulkCopy class; it was made specifically for this purpose. It gives pretty good control over the transaction, batch size. Most importantly, it runs much faster than 50,000 inserts.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.