SQL Server Insert / Update / Deletion and Selection of Performance Suggestions / Deadlocks

Question

I came across a task when I need to create a web application in the .NET Framework. In this application, users will only (99% of the time) have read-only access, because they will simply see the data ( SELECT ).

However, the backend database will be a beast where records / inserted / deleted will be updated every minute. The forecast is that at least a year will add about 10 million entries to the system, in less than 5 tables.

Question / Request 1 :

Since these updates / inserts will occur very often (every minute or two last), I was hoping to get some tips so that when changing some lines, the select query could not cause a thread deadlock or vice versa.

Question / request 2 :

My calculated hunch is that in normal situations, only a few hundred records will be inserted every minute 24/7 (and updated / deleted based on some conditions). If I write a C # tool that will receive data from any number of sources (xml, csv or direct from some tables from a remote db, the configuration file or registry setting will determine in which format the data is imported), and then paste / update / delete, will it be fast enough and / or cause problems with blocking?

I hope my questions are quite complicated ... Please let me know if all this is vague ...

Thanks in advance

Answer 1

I will answer first your question # 2:. According to the scenario you described, it will be fast enough. But don't forget to issue direct SQL commands to the database. I personally have a very, very similar scenario, and the application works without any problems, but when a scheduled task performs multiple inserts / deletes using a tool (for example, nhibernate), deadlocks occur. So, again, if possible, follow the direct SQL statements.

Question # 1: You can use "SELECT WITH NOLOCK".

For instance:

 SELECT * FROM table_sales WITH (NOLOCK) 

It avoids blocks in the database. But you must remember that you can read outdated information (again, in the scenario you described, this will probably not be a problem).

You can also try "READ COMMITTED SNAPSHOT", it has been supported since 2005, but for this example I will keep it simple. Find a little about it to decide which one might be the best choice for you.