Me and my colleagues are using quite big size normalized database.
Basically it's used for warehouse management and daily minimum 100 of the user perform data manipulation operations.
80% of the users access stock information from web.
20% users are from the warehouse operation team.
Most of the time in a day, warehouse operation team performs major operations of stock taking, stock adjustment, sales, purchases, reporting etc.. But at the same time web users get quite slow response from SQL Database.
Keeping in view increasing number of web users, my question is how I could make the web access faster at the same time when major operations/transactions are running in the background?
As web users are accessing subset of the tables which are being used by Operations team, what strategy I could implement to keep the database responsive as well sync at same time?
Please bear with my language skills, I hope this is right place to ask this question, otherwise please guide.