I have seven (could be more in future) complicated views that take a few seconds to run each. The data that the views are based on is changing constantly. All the views produce a result with the same column format. Each row in a view includes an aggregated key (a few columns concatenated) which, together with another column (int), can form a unique key for each row.
I have a main table that my website uses to view the data quickly and I need to feed each views output into that main table on a regular basis to update fresh data. When updating main table with the views output all previous entries from that view (uniquely identified in main table by aggreagtedKey and int column) can be deleted.
My plan was to set an agent to run each minute for each view and MERGE the view output into the main table .
While I am pretty sure this will work I want to know if this is the best method or if anyone can suggest something more efficient. My aims are below
refresh main table as quickly as possible with fresh data from complicated views
ensure that the main table is able to be read by website quickly
prevent any locking of records that could result in timeouts
ensure it is as scalable as possible as the number of complicated views may increase in the future
I am using MSSQL 2008R2 STD
Let me know if you need any more info and thanks in advance ;-)