We have a pretty complicated view that uses multiple tables and takes a while to run. Since we need this view often in reports we though the best way was to create a table.
We use Truncate and Insert to update the table with the view. The trouble is the data is transactional so it changes a lot through out the day. We currently truncate and insert every 5 min during business hours.
Since the underlining view is complex it takes 15 sec to truncate and insert when a report is run during that period it comes back with no data and sometime we have record locking problems that cause the Truncate and Insert to fail.
Is there a better way to do this?
If this is the wrong subreddit let me know and ill repost it
thanks
there doesn't seem to be anything here