1

I'm inserting lots of data into SQL Server with many of them having foreign key dependencies. Meaning if I insert 1 row in my Person table, I'm also inserting 1 row in 8 other related tables.

This import takes a lot of time already on my local machine (about 4h30min for 300k (x9) rows), but it takes almost exactly double the time to insert this data on various servers. All of the servers are more powerful than my local machine, so it most likely is not a hardware problem.

What could be the bottleneck here? How do I best go about finding out the cause? I'm using SQL Server 2017, but we've had this issue since SQL Server 2015 (the one we started the project)

  • 1
    Large inserts via Entity Framework don't sound like the best idea. Can you show some code samples of what you're actually doing so we could suggest ways in which not using Entity Framework will solve your problem? – Mark Sinkinson Oct 17 '17 at 8:02
1

All of the servers are more powerful than my local machine, so it most likely is not a hardware problem.

This is a common misconception.

Single-row inserts, like EF generates, are not expensive in CPU cycles. Typically bottleneck is writing to the log file. Your local machine may well give you better log write performance than a powerful server. Your local machine may have a fast, local SSD. Or you may have write caching enabled on your local disk.

What could be the bottleneck here?

My guess is log writes. If you commit after each row, then this will be much worse. In EF each call to SaveChanges() uses a separate transaction, so you want to batch 10s to 100s of rows in each call. You also don't want to have too many rows added to a DbContext instance, as they are all tracked for the lifetime of the DbContext.

But more generally, SQL Server will tell you where the bottleneck is. You can look at the session wait stats for the session performing the insert in SQL 2016+:

select *
from sys.dm_exec_session_wait_stats
where session_id = 51
order by wait_time_ms desc

Or you can look at the server-wide wait stats while the import is running:

https://blogs.msdn.microsoft.com/dbrowne/2013/01/18/my-favorite-query-for-investigating-sql-server-performance/

  • Thanks, I'll look into wait stats right away! We're already batching 100 inserts, but I have no idea if we clear the DbContext. Gonna look into this as well and check back here when I have some results. – FullStackDAU Oct 18 '17 at 6:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.