Always use dtexec.exe to test performance of your dataflows. No exceptions.

jamiet — Wed, 12 Jan 2011 21:51:00 GMT

Earlier this evening I posted a blog post entitled Investigation: Can different combinations of components effect Dataflow performance? where I compared the performance of three different dataflows all working to the same overall goal. I wanted to make one last point related to the results but I thought it warranted a blog post all of its own.

Here is a screenshot of one of the dataflows that I was testing:

Pretty complicated I’m sure you’ll agree. Now, when I executed this dataflow in the test it was executing in ~19seconds however in that case I was executing using the command-line tool dtexec. I also tried executing inside the BIDS development environment and in that case it took much longer – 139seconds. That’s more than seven times as long.

The point I want to make is very simple. If you are testing your dataflows for performance please use dtexec. Nothing else will suffice.

@Jamiet

UPDATE: Matt Masson from the SSIS team has posted a great blog post explaining exactly why dtexec is quicker. Go read it at Why does my package run slower in BIDS than DTEXEC?

Search results matching tags 'sql server integration services' and 'dtexec'

Always use dtexec.exe to test performance of your dataflows. No exceptions.