Writing view to table - fastest/easiest way?

[swhitten]

I have a view that returns approximately 5 million rows and is growing at the rate of several thousand rows per month. I'm using a reporting tool that crashes when I try to report off the view. I tested writing the view to a table and reporting off the table, and the reporting tool works fine when I report off the table created from the view. So I would like to automate what I did manually and run reports off the table.

I used Import Data in SQL 2005, and it took about 4 hours for the 5 million rows to write from the view to the table. I need to schedule this to update the table about 3 times a week in advance of running the reports. I don't know of a way easily identify new/changed records to write ONLY those to the table because there are many tables and views underlying the view I want to write to the table. So I think I will have to re-write the entire table every time.

What is the most efficient/fastest way to schedule this to run?

Thanks in advance for your help.

 

[RiverGuy]

Well firstly, if you are using SQL 2005, you would want to use SSIS, not DTS. But neither would be necessary if you are simply copying data from a view to a table within the same SQL Server instance.

Secondly, four hours to copy five million rows to a table from a view seems like a long time, unless you have an extremely wide table. You'll probably get the most benefit as far as speed is concerned by optimizing your query which defines the view, and possibly adding and/or maintaining your indexes and statistics. You should be able to get the four hours down to something more reasonable.

Thirdly, to do the actual insert, your easiest solution would be something like:

TRUNCATE TABLE YourTableName;
INSERT INTO YourTableName SELECT * FROM YourViewName;

You could then schedule that statement in a SQL Agent job to run three times per week. But, if at all possible, I would suggest you attempt to do an incremental load. Instead of rebuilding the entire table, if only certain rows have changed, just re-insert those changed rows. For example:

DECLARE @LastUpdatedDate DATETIME
SELECT @LastUpdatedDate = LastUpdatedDate FROM SomeMetadataETLTable WHERE TableName = 'YourViewName'

DELETE YourTableName WHERE LastUpdatededDate >= @LastUpdatedDate;

INSERT INTO YourTableName SELECT * FROM YourViewName WHERE LastUpdatedDate >= @LastUpdatedDate;

UPDATE SomeMetadataETLTable SET LastUpdatedDate = (SELECT MAX(LastUpdatedDate) FROM YourTableName) WHERE TableName = 'YourViewName';

With the incremental load solution, you would either have to maintain a table to keep track of the date you wish to load, or use some more basic logic such as "load everything from the past week."

 

[swhitten]

Thanks. I am working on cleaning up my views; I think that's where the real slow down is occurring.