June 19, 2009 at 9:46 am
I have a SSIS package that among other things executes a stored procedure that processes data from one "raw" data table to a production table. When the SP is run manually it runs in 7-8 Min for 4000 rows. But, when run from the SSIS package using a SQL task with the OLE DB connection it takes around an hour.
The SSIS package executes on a local db server, but the SP that is executed with the SQL task runs on a remote database server. So far, cannot explain why it takes so much longer. With smaller record sets of up to 1000 there is no difference.
June 20, 2009 at 12:18 pm
It looks like this may be down to hardware. We moved it to another test environment with a little better hardware and more horsepower and it improved drastically. Still, if anyone has any idea why this may occur the information would be greatly appreciated.
June 20, 2009 at 12:56 pm
SMAAG (6/20/2009)
It looks like this may be down to hardware. We moved it to another test environment with a little better hardware and more horsepower and it improved drastically. Still, if anyone has any idea why this may occur the information would be greatly appreciated.
The Agent comes with fixed threads so it may be related to what else the Agent is doing in your network that is the Agent work load. In 2005 the Agent can put your Job in a queue but system resource and the Agent load management can improve the duration.
http://technet.microsoft.com/en-us/magazine/2006.10.sqlqa.aspx
Kind regards,
Gift Peddie
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply