Execution times slow

[GShen]

HI,
I have been migrating DTS's from sql 2000 to 2005 and every new package seems to running a lot slower by 30-40 seconds or more. I have a DTS I ran today (4 records). I obviously runs in blink speed (0 seconds). The new migrated SSIS runs in 34 seconds. Is there a reason for any of this ? Some of these jobs run at night and it is not a big problem but I am afraid of a job which has a lot of records to import or export will take a long time to execute. Is this inherent to the migration ? Should I recreate the SSIS from scratch ? Or Lastly, is there just more overhead on the SSIS and regardless of how long it each step took before I should just add 30-35 seconds to each step. So a 1 second DTS is about 30-35 seconds with SSIS and a 10 second step will be about 40-45 seconds.

Any help is appreciated. I don't want to keep going forward and find out I am doing something wrong or something is not set up properly.

Remember when... everything worked and there was a reason for it?

 

[mrdenny]

SSIS is based on .NET so it has a lot more overhead than DTS did. This will be part of your problem.

Try creating an empty package and running it and see how long it takes to fire up.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[JahDW]

HELP! I'm having a similar issue, but with much longer execution times. It's quite ridiculous actually. Basically my packages (about 20 of them in all) create tables from views within the same DB. As the year progresses and our tables grow, the individual packages naturally take a bit longer to execute. But in SQL 2000 DTS the max would be like 5 minutes for a particular package when the views had hundreds of thousands of records. It's now the beginning of the year and the view has 16,000 rows and the takes about 15 seconds to execute in management studio... but the SSIS package takes literally over one hour. About 63 minutes to be exact, and I can't for the life of me figure out why it's taking so long. Anyone have any ideas?

I'm running SQL 2005, windows server 2k3, 4gb ram, 4 x 2400mhz... basically just a bit better than our previous server that ran SQL 2000.

 

[mrdenny]

How is the package setup? Is it all T/SQL, is it a data pump task, etc?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog