CODEWITHSUNDEEP
c#sql-serverentity-frameworkasp.net-corevisual-studio-2019
XavierAM
XavierAM

Reputation: 1775

SQL Server request inserting 200 rows in a local database takes 20 seconds and growing

I am working on a console app (C#, asp-core 2.1, Entity Framework Core) which is connected to a local SQL Server database, the default (localdb)\MSSQLLocalDB (SQL Server 2016 v13.0) provided with Visual Studio.

The problem I am facing is that it takes quite a long time to insert data into a table. The table has 400.000 rows, 6 columns, and I insert them 200 at a time.

Right now, the request takes 20 seconds to be executed. And this execution time keeps increasing. Considering the fact that I still have 20.000 x200 rows to insert, it's worth figuring out where does this problem comes from!

A couple of facts :

So, my questions are :

Here is the SQL code of the table in question :

CREATE TABLE [dbo].[KfStatDatas] 
(
    [Id]           INT IDENTITY (1, 1) NOT NULL,
    [DistrictId]   INT           NOT NULL,
    [StatId]       INT           NOT NULL,
    [DataSourceId] INT           NOT NULL,
    [Value]        NVARCHAR(300) NULL,
    [SnapshotDate] DATETIME2(7)  NOT NULL
);

EDIT I ran SQL Server Management Studio, and I found the request that is the slowing down the whole process. It is the insertion request.

But, by looking at the SQL Request create by Entity Framework, it looks like it's doing an inner join and going through the whole table, which would explain why the processing time increases with the table.

I may miss a point but why would you need to enumerate the whole table to add rows?

Raw request being executed :

SELECT [t].[Id] 
FROM [KfStatDatas] t
INNER JOIN @inserted0 i ON ([t].[Id] = [i].[Id])
ORDER BY [i].[_Position]

Request View

EDIT and SOLUTION

I eventually found the issue, and it was a stupid mistake : my Id field was not declared as primary key! So the system had to go through the whole DB for every inserted row. I added the PK and it now takes...100 ms for 200 rows, and this duration is stable.

Thanks for your time!

Upvotes: 1

Views: 595

Answers (2)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89036

I think you may simply missing an primary key. You've declared to EF that Id is the Entity Key, but you don't have a unique index on the table to enforce that.

And when EF wants to fetch the inserted IDs, without an index, it's expensive. So this query

SELECT t.id from KfStatDatas t
inner join @inserted0 i 
  on t.id = i.id
order by i._Position

performs 38K logical reads, and takes 16sec on average.

So try:

ALTER TABLE [dbo].[KfStatDatas]
ADD CONSTRAINT PK_KfStatDatas
PRIMARY KEY (id)

BTW are you sure this is EF6? This looks more like EF Core batch insert.

Upvotes: 2

Dan Guzman
Dan Guzman

Reputation: 46202

No 400K rows is not large.

The most efficient way to insert a large number of rows from .NET is with SqlBulkCopy. This should take seconds rather than minutes for 400K rows.

With batching individual inserts, execute the entire batch in a single transaction to improve throughput. Otherwise, each insert is committed individually, requiring a synchronous flush of the log buffer to disk for each insert to harden the transaction.

EDIT:

I see from your comment that you are using Entity Framework. This answer may help you use SqlBulkCopy with EF.

Upvotes: 0

Related Questions