0

enter image description here

In one of my project have a table with above structure named TDocuments.

CID column is the Company ID and the table gets 40,000 companies every morning from a Windows application client database by connecting to backend API service and sending a request to my ASP.NET / C# web application (running on the .NET framework) with EF 6 send 50-100 thousand rows.

My policy is to delete all previous client data from the server database every day (with a delete query).

await _context.Database.ExecuteSqlCommandAsync(delete query)

Then I insert all the data again because new data has arrived on the client side or data has been edited and deleted (bulk copy).

await bulkCopy.WriteToServerAsync(dataTable);

Now last _id is 633,538,597

From client we send data page by page than every page have 10,000 records.

These days sometimes we have the following error when deleting or inserting:

Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Database details:

  • Recovery mode is full
  • Database size above 50 GB
  • Table count above 20
  • SQL Server 2017

This web server started 7 years ago.

Even when running a query directly in the SQL Server Management Studio, we have a slow speed.

I need to provide a more optimal solution in coding or database

Thank you for guiding me.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
JAvAd
  • 114
  • 1
  • 9
  • Hello @JAvAd you can create a stored procedure for deleting records. sp can delete data faster than other methods. – AliNajafZadeh Jul 16 '22 at 17:43
  • Please provide more details, e.g. when you run queries in SSMS, is the delete query slow, or bulk insert, both, what's their times? What is the delete query? The question has too little details - we would need to guess what's slow and why. – Michael Entin Jul 17 '22 at 00:44

1 Answers1

0

Without knowing the exact details of your delete query & current indexes on the table I can only provide suggestions.

  • Transform your query to a stored procedure so that SQL can optimize the execution plan
  • Make sure you have proper indexes on your table if your delete query queries against any columns other than your PK (or reindex your PK, but that shouldn't be your first course of action)
  • Check your Cascading rules if you have FK in your table

If all else fails, increase your timeout (CommandTimeout) on your DBContext.

What I can also suggest is manually checking the execution plan of your query and taking it from there

PS: As a side note, we often experience slow downs during DB & server backups during off-peak times. might be worth checking if any long and resource-heavy tasks run on your server when the service runs

Andre Schoultz
  • 1
  • 1