SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 Optimize Transfer SQL Server Objects Task
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

abacusdotcom
Posting Yak Master

Nigeria
130 Posts

Posted - 04/26/2013 :  06:42:14  Show Profile  Visit abacusdotcom's Homepage  Send abacusdotcom a Yahoo! Message  Reply with Quote
Hi all,

Am using presently using Transfer SQL Server Objects Task in my etl process.

It takes like 6 hours for the task to complete, which is outrageous. I need help on how I can optomize the control or a suggestion on another approach.

Thanks.

'Abacus

I sign for fame not for shame but all the same, I sign my name.

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/26/2013 :  06:43:41  Show Profile  Reply with Quote
which all objects you're trying to transfer? whats the amount of objects involved?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

abacusdotcom
Posting Yak Master

Nigeria
130 Posts

Posted - 04/26/2013 :  07:01:58  Show Profile  Visit abacusdotcom's Homepage  Send abacusdotcom a Yahoo! Message  Reply with Quote
Hi Visakh16,

All in total about 6 items, but they are very large transaction tables. Minimum of 2millions rows.

I sign for fame not for shame but all the same, I sign my name.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/26/2013 :  07:43:19  Show Profile  Reply with Quote
6 items and 6 hours?? thats really on high side

Why do you've to transfer objects in etl? can you give us some context on what you're trying to do?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

abacusdotcom
Posting Yak Master

Nigeria
130 Posts

Posted - 04/26/2013 :  08:49:20  Show Profile  Visit abacusdotcom's Homepage  Send abacusdotcom a Yahoo! Message  Reply with Quote
There are two servers. Server A is the process engine, while server B is the report portal. After every scheduled processing, another etl moved the processed data from Server A to B.

For smaller tables, I use Transfer SQL Server Objects Task to move them, drop and create..
and for large table, I used delete and insert.

Thanks.

I sign for fame not for shame but all the same, I sign my name.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/29/2013 :  01:23:01  Show Profile  Reply with Quote
hmm...can you try using a ssis package to truncate and load the tables instead?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000