×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Writing view to table - fastest/easiest way?

Writing view to table - fastest/easiest way?

Writing view to table - fastest/easiest way?

(OP)
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.

RE: Writing view to table - fastest/easiest way?

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:

CODE

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:

CODE

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."
 

RE: Writing view to table - fastest/easiest way?

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close