How to restrict console application not to lock database?

Question

ASP.NET

Good afternoon.
There is an ASP.Net application that works quickly and stably.
And there is a console application that is launched by the scheduler and loads data into the same database that the ASP.Net application works with. At the time of loading, especially if there is a lot of data, the ASP.Net site crashes by timeout. And while the console application does not load all the data, the site lies.
At the time of loading, the load on the server is 100%. It is possible to somehow limit this console application, so that the database is loaded into the database and the site does not fall.

Answer 1

What ORM are you using?
There are several possible approaches here.
1. Add caches to the application so that it returns the old values, and requests run in the background
2. Tweak the insert request. Batch data insertion might help.

Answer 2

Lower the priority of the process (this is in case timeouts occur due to lack of processor time)

Answer 3

Instead of SELECT + UPDATE use Upsert considering https://michaeljswart.com/2017/07/sql-server-upser...

Basically what it does. Takes an XML file. Collects from it an array of what needs to be loaded into the database.
And for each element of the array, it looks, if there is one in the database (still by code), then it updates the properties, if not, it adds a new element.

And if we are talking about the price list, when the price list data is usually replaced by the old ones with the new ones entirely, then it makes sense to create a new table from one XML, and delete the old one. Then the table of the current price list will not be locked for updating.
If importing from XML is such a resource-intensive operation that it affects production, then you can lower the priority of the import process. Or run from another server.

Answer 4

Quantize. Just like the operating system does with processes. There are many processes, but the central processor is one. The OS just gives a small interval of time to each process, everyone runs in turn and everyone is happy.
The easiest way is to split the entire array into packs of 100 (10, 1000), stupidly do Thread.Sleep(300 or less) between processing the pack, thereby allowing the server to process requests. Select parameters experimentally.