I am wondering how I should most quickly fetch the data from a group of tables for reporting / viewing.
My tables are slowly changing, meaning that I use a getDate() function to pull the right records between effective and expire dates. An admin may be making changes occassionally to some records, but mostly the data is read-only by site visitors. It's ok for the changes made by the admin not to go live immediately, of if urgent, for them to click a button or something to force them live.
I have a big query which brings a dozen tables together and can be a bit slow.
It includes case statements and left joins and getDate() which not allowed in Indexed Views.
I'm wondering if it makes sence to create a table that I populate from my big query, add various indexes to the table and populate it every night or every few hours or something. Are there downsides to this? It seems indexed views are not an option...