Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kimi86
Yak Posting Veteran
79 Posts |
 Posted - 2012-07-19 : 11:14:04
|
| Hi Guys,I have cerated massive view called source_View with almost 4 levels of views withing it, around 260 columns and 15 million records. Users need to be able to do a select from this view daily. Anyways this was not working and so i have created a table and a job which adds new records to this table from view on daily basis and hence does not that too long. USers are also happy since select from the table is faster. How ever my problem is that i need to run this job for the first time for all the 15 miilion records. this is just one time.Can you seggest any coding tricks that i can apply to do this just once. I dont want to leave it running for 2 days only to know the server had hanged. One thing that i can think of is updating 1000 records at a time only and then committingMy code looks like this.Select policy_key into #T1 from (Select policy_keys from source_View exceptSelect policy_keys from destination_Table ) T1Insert into destination_TableSelect * from source_View where policy_key in (Select policy_key from #T1) |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-07-19 : 11:17:44
|
| If you are familar with SSIS, it's pretty easy to pump data from one place to another and you can configure the batch size so you do not have to commit all 15 ROWS in one transaction. That should be faster and allow you to monitor progress.It's also possible to gain some performance on loads by disabling indexes for the load and then enabling them afterwards. |
 |
|
|
Kimi86
Yak Posting Veteran
79 Posts |
Posted - 2012-07-20 : 05:51:59
|
| Do u think SSIS will help since the sourec it self is the view which is so heavy.. Any other ideas?? |
|
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2012-07-20 : 06:32:46
|
| Why you don't like to create indexed view. It will manage records physically as you will create a clustered index.--------------------------http://connectsql.blogspot.com/ |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-20 : 10:31:19
|
| does 15 million rows come daily? if not, isnt it enough to add only incremental changes each day? then you could use same method of having table which gets populated each night through a job to get only deltas for the day------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
one time load