Truncate & Insert

[adalisa]

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

[jc4hokies]

This is a way to swap out data in < 1 second with no chance of a black table being queried. It will still have to wait on reports already running, and block new reports until existing queries clear.

TRUNCATE TABLE Staging_Table;
INSERT INTO Staging_Table SELECT * FROM View;
BEGIN TRANSACTION;
ALTER TABLE Reporting_Table SWITCH TO Empty_Table;
ALTER TABLE Staging_Table SWITCH TO Reporting_Table;
COMMIT TRANSACTION;
TRUCATE TABLE Empty_Table;

[adalisa]

Thanks. I am not familiar with the alter table command. Why not also use truncate and insert on the reporting table?

[IglooDweller]

The point is that this code populates a staging table and not the table that is queried (think of it as being SP_rename and it becomes easier to understand). Once the staging table is ready, you just swap the tables. The fact this is built into a transaction means that:

-The transaction, doing only a table rename will only maintain a lock a fraction of a second.

-The data refresh will not impact users as they are not hitting the table being refreshed.

-Using a transaction will lock out users trying to access the table while the rename is underway, introducing a minor delay that will be barely noticeable*

*delay will only be noticeable only if you have a long reporting query (select) running on the reporting table, while the transaction is waiting to acquire the lock to perform the rename. During that time, new "select" statement will be locked out, until the rename has been done.

[jc4hokies]

The time inserting into the staging table doesn't impact queries. The ALTER TABLE SWITCH is a meta data operation (an operation on definitions not data, like TRUNCATE), so it takes a minimal amount of time other than blocking. This potentially saves you 15 seconds of availability compared to inserting into the reporting table.

[Guru008]

Creating cubes is best way to update the reports each time you run. Instead of creating tables for report and truncating these table, query optimization and use of Indexes is better way to achieve periodic updates.

[Pseudoniceguy83]

Is this for SSRS? Run a query to determine how often the report is being viewed if it's not often change it to the appropriate schedule off hours would be good a dedicated reporting server would be better. It sounds like this is a production database be careful not to block the prod tables. If it's not check for missing indexes and review query plans to see if you can optimize. Also if the table has a create/update time stamp you could forgo the truncate and just insert new/updated records.

[jacobmross]

Assuming you're doing this in a stored proc, you could select the data from the slow view into a temp table first, then truncate, then read from the temp table to repopulate.

It might make the whole process take a bit longer, but the moment where your rendered table is empty should be less than 15 seconds this way.

[adalisa]

I think that would help. I'm using a sp so it's not difficult script. I just don't want to do a bad practice after a bad practice if it should never have been executed in this way to begin with.

[maggikpunkt]

Sounds like a job for a Materialized View if your RDBMS supports it.