0

One of our systems has a table that receives around 20k inserts per day - it currently contains ~10m rows. We've just pushed out a system upgrade and I'm getting some shockingly slow performance on inserts to this table now (30-40s traces). Inserts will only ever insert a single row at a time. Entity Framework is generating the following SQL:

declare @generated_keys table([ID] uniqueidentifier)

insert [dbo].[Table]([Col1], Col2], etc)

output inserted.[ID] into @generated_keys values (@0, @1, etc)

select t.[ID] from @generated_keys as g join [dbo].[Table] as t on g.[ID] = t.[ID] where @@ROWCOUNT > 0

Nothing out of the ordinary - at least as far as I can see.

The table has 12 FKs. As the entity represents a time-based event, the clustered index is on a DateCreated column. There are 8 further non-clustered indexes, with various included columns. The ID column is the PK and has a unique, non-clustered index.

As a note, I have noticed that, due to an omission, the ID column is using newid() instead of newsequentialid().

The actual execution plan comprises the following major parts:

7x 3% - Clustered Index Seek on FK tables

1x 8% - Table Insert (@generated_keys)

1x 74% - Clustered Index Insert, 1 actual row, 0.09 cost

I'd really like to get to the bottom of this but I'm stumped now. Any advice?

James
  • 1,028
  • 9
  • 20
  • have you tried ditching the ClusteredIndex? – Mike Miller May 24 '16 at 10:10
  • It's in place as almost all select operations on this table return data in date order. Also, given that inserts are in date order, a clustered index on this column made sense in my eyes, rather than using the PK column (uniqueidentifier). – James May 24 '16 at 10:25
  • Is the slow running insert consistent or are you getting so many running quickly then one taking an age? – Martin Brown May 24 '16 at 11:28
  • As you said. The first query in a series/batch can take a while, but subsequent runs complete very quickly (at a speed I'd expect at all times). – James May 24 '16 at 11:43
  • it seems not the query itself is the problem, but rather connection management/ model building. Which of those I can't really tell, however I assume the latter since the model seems complex. – DevilSuichiro May 24 '16 at 13:52

1 Answers1

-2

As you have noticed, SaveChange make a database round-trip for every record insert. So if you add 20k record per day, 20k database round-trip is performed.

Modifying & Improved index will make almost no difference since the real issue is the number of database round-trip!

To fix the performance, you must use a library which allows performing Bulk Insert

There is three majors library supporting it:

You can learn more about the three libraries here: Entity Framework Bulk Insert Library Reviews & Comparisons

Disclaimer: I'm the owner of the project Entity Framework Extensions

Jonathan Magnan
  • 10,874
  • 2
  • 38
  • 60
  • Bulk inserts are not the issue, those 20k inserts occur over a 24h timespan. Saving them up for a bulk insert isn't an option. – James May 24 '16 at 15:29