CODEWITHSUNDEEP
c#entity-frameworksql-server-2005
nickfinity
nickfinity

Reputation: 1119

Entity Framework timeout error due to database block

Have a project that uses the entity framework (v1 with .NET 3.5). It's been in use for a few years, but it's now being used by more people. Started getting timeout errors and have tracked it down to a few things. For simplicity sake let's say my database has three tables, product, part, and product_part. There are ~1400 parts and a handful of products.

The user has the ability to add any number of parts to a product. My problem is that when there are many parts added to the product the inserts take a long time. I think it's mostly due to network traffic/delay, but to insert all 1400 takes around a minute. If someone goes in and tries to view the details of a part while those records are being inserted I get a timeout and can see a block in the Activity Monitor of SQL Server.

What can I do to avoid this? My apologies if this has been asked before and I missed it.

Thanks,
Nick

Upvotes: 1

Views: 654

Answers (1)

usr
usr

Reputation: 171246

I think the root problem is that your write transaction is taking so long. EF is not good at executing mass DML. It executes each insert in a separate network roundtrip and separate statement.

If you want to insert 1400 rows, and performance matters, do the insert in one single statement using TVP's (INSERT ... SELECT * FROM @tvp). Or switch to bulk-copy but I don't think that will be advantageous at only 1400 rows.

If your read transactions are getting blocked, and this is a problem, switch on snapshot isolation. That takes care of the readers 100% as they never block under snapshot isolation.

Upvotes: 1

Related Questions