Search code examples
sql-serverentity-frameworkinsertentity-framework-6timeout

Entity Framework just stopping with timeout during INSERT

I have a small c# application which using Entitiy Framework 6 to parse text files into some database structure.

In general file content is parsed into 3 tables: 

Table1 --(1-n)-- Table2 --(1-n)-- Table3

the application worked for months without any issues on Dev, Stage and Production environment.

Last week it stopped on stage and now I am trying to figure out why.

One file contains ~ 100 entries Table1, ~2000 Entries Table 2, ~2000 Entries Table 3

.SaveChanges() is called after each file.

I get the following timeout exception:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated.

AutoDetectChangesEnabled is set to false.

Because there is a 4th table were I execute one update statement after each file there were transactions arround the whole thing, so I removed the 4th table and transaction stuff but the problem persists.

To test if it's just an performance issue I set Database.CommandTimeout = 120 without any effect, it's still running into timeout after 2 minutes.

(Before the issue one file was stored in about 5 seconds which is absolutely ok)

If I look at the SQL Server using SQL Server Profiler I can see the following after .SaveChanges() is called:

SQL Server Profiler Only the first few INSERT statements for Table3 are shown (always first 4-15 statements and all of them shortly after .SaveChanges())

After that: no new entries until the timeout occurs.

I have absolutely no idea what to check because there is no error or something like that in code.

If I look at SQL Server, there is absolutely no reason for it to delay the queries or something like that (CPU, memory and disk space are ok).

Would be glad for each comment on this, if you want more infos please let me know.

Best Regards

Solution

  • Fixed it by rebuilding fragmented indexes in Table1.

    The following article was helpful to understand how to take care of fragmented indexes:

    https://solutioncenter.apexsql.com/why-when-and-how-to-rebuild-and-reorganize-sql-server-indexes/

    (If some mod is still thinking this is no valid answer, any explanation would be great)