THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

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

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:

image

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?

Published Wednesday, January 12, 2011 10:51 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

John Welch said:

Also, for your readers (I know you know this :)) - DTEXECUI is not the same thing as DTEXEC. It's significantly slower that running DTEXEC from the command prompt.

January 12, 2011 4:56 PM
 

Jim said:

What causes the difference in performance from dtexec to bids?

January 12, 2011 9:05 PM
 

jamiet said:

Jim,

Someone from the SSIS team once said to me "making all those boxes change colour and showing those rowcounts incresae aint cheap". Its simply that - BIDS has to do a lot of work to give you all that UI stuff. It doesn't have to do it if you're using dtexec.

Jamie

January 13, 2011 1:33 AM
 

Paul Clancy said:

Hi Jamie, good post and a good point. Follow-on question: do you think that it's reasonable to assume that if you have good performance in BIDS then performance will always be at least as good and probably better with Dtexec (asks the lazy developer)?

January 13, 2011 4:26 AM
 

jamiet said:

Hiya Paul,

Well, never say never, right? But I can't think of any circumstance where perf in dtexec would be slower. BIDS is essentially a shell on top of dexec (not quite true - but we can think of it that way) so it does everything that dtexec does and more besides.

-Jamie

January 13, 2011 4:34 AM
 

Chris Randall said:

Just to add (for other lazy developers) "Start without debugging" (Ctrl-F5) from within BIDS accomplishes the same thing - invokes DTEXEC directly. No need to go spelunking through a command prompt to run it (though you should learn that anyway) :)

January 13, 2011 3:49 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement