I am having a problem between chosing VIEW and TABLE from a SQL server database
Suppose the data table is name ACTION (of size 30M) with schema ACTIONID/ ACTIONNAME/USERID/ ACTIONDATE.
The Table is updated once everyday with 20K new rows For my purpose I only want a query on ACTION rows which are as recent as 1 week
I want queries to run faster selecting from a smaller dataset(as there are joins as well)
Instead of querying the 30M database everytime; I create a view that will store data from current date to 7 days earlier
I set the view to refresh ONLY once a day and NOT everytime there is an insert update delete on the ACTION table
That way my queries are faster (as from a smaller dataset) BUT my view doesnt refresh 20k times a day making the performance a disastrous one
Question : Can a VIEW be actually configured to REFRESH only periodically and NOT when table updates?
SOLUTION B(Indexed View)
I create a View which stores all the ACTION table rows between 9/16/2010 and 9/9/2010 (today and a week, hardcoded)
When new 20K entries are inserted; the view isnt refreshed even once as all the dates ate outside range(despite the ACTION table is updated)
A trigger is attached to view to run a script everyday The trig
View Complete Post