How to spread traffic on a table
-
I am not sure if I am phrasing the question right, which is probably also why I haven't been able to find an answer anywhere. The situation is as follows: I have an ERP system using an MS SQL 2005 database. There are several tables in the database that contain document data (i.e. orders, deliveries, invoices, etc). These tables get a lot of read and write action under normal use of the ERP system. There are several heavy ad hoc queries saved in the system, and also some user applications and timed console apps that run heavy queries (sales reports, purchase forecasts, and such). The result is that in daily use such a strain is placed on the document tables that we get constant timeouts, and/or applications (and the ERP system itself) grinding to a halt when too many users / applications are using the system at the same time. The question is: Considering that I can hardly or not at all touch database settings, and I certainly can not fool around with the table structures themselves, I am thinking of making our applications and ad hoc queries not perform read operations directly on the tables in question. Also most of the data should be up to date to within say an hour. What are my options ? Would using views or a synchronized db help ? Any other solution ? Cheers, Johan
My advice is free, and you may get what you paid for.