CODEWITHSUNDEEP
sql-serverentity-frameworkdatabase-performance
Louis Ingenthron
Louis Ingenthron

Reputation: 1368

SQL Server EF Core Row Deletion Performance

I've got a simple 3-column table to track pending calculations. The first column is an integer Foreign Key linking to a clients table. The second is a date and the third is a priority flag. The primary key for the table is a composite of the client FK and the date.

This table is used for a calculation queue, so it sees a lot of churn. What I noticed, however, is that working with this table itself is sometimes slower than the actual calculations that it's a queue for. Specifically, when it has about 1000 rows, and the calculation is short-circuited, removing just one row from the queue and calling SaveChanges takes on average three seconds.

With this table constantly being added to and removed from, those three seconds add up quick. And it has to be done serially to ensure the queue is tackled in the correct order. It just seems like an inordinately long amount of time to remove a single row on a three-column table.

I'm guessing the composite key is to blame...? But I'm not sure, and I'm new to MS SQL and EF Core. Can anyone point me in the right direction to profiling this to identify the bottleneck?

Upvotes: 0

Views: 44

Answers (1)

gius
gius

Reputation: 9437

I would start by profiling the app through Visual Studio (Debug > Performance Profiler) to see where the problem actually is:

  1. If you find out that the call to SQL is actually slow, I would continue with checking what commands are actually sent to the SQL server and which of them takes so long. Use either ExpressProfiler, SQL Server Profiler extension for Azure Data Studio, or SQL Server Management Studio. This can also lead to poor SQL performance because of neglected maintenance such as fragmented indexes, etc.
  2. You might also find out that the problem is actually somewhere within EF logic, for example, change tracker trying to figure out what entities to update. In that case, you will need to find a way to avoid the particular issue (e.g., do not save after each delete, use SQL command to delete rows manually, use Dapper instead of EF, etc.).

I hope these ideas help you to fix the problem. Good luck ;-)

Upvotes: 1

Related Questions