Why my BIDS is slow in design mode ?
One of our customers was saying that their SSIS Project with some complex “Data Flow Task”s with lots of Lookup Transformations was opening very slowly in BIDS (“Business Intelligence Development Studio”) or I don’t know if I should say “Visual Studio” …
The very first thing that I check was “validation” part. As you know, by default all tasks are set to validate its incoming and/or outcoming parts to protect SSIS package developers for possible metadata changes in source/destination data sources. For example, another developer may change a column’s datatype in a table without letting you know for an OLE DB Source connecting to a SQL Server, Oracle etc. If the component sees a validation problem it will warn you with an exclamation icon on itself and if you hover on the component, you’ll see the detail as a tooltip.
But sometimes this “validation” part might be really time consuming for really complex tasks that you developed. If that is the case, you should be aware of some properties like ValidateExternalMetadata and DelayValidation .
We do have a very nice document here explaining what are they for :
http://msdn.microsoft.com/en-us/library/ms137625(SQL.90).aspx
Enough reference info 🙂 Let’s go back to our story …
Since our customer’s Data Flow Task was containing lots of transformations I asked customer to change “DelayValidation” properties for the tasks as “True” and “ValidateExternalMetadata” to “False. It didn’t help again …
The same project was working fine on my same testing environment. After digging around, we noticed that customer installed “BIDS Helper” and it was doing some extra jobs. Noticed the text in the status bar of BIDS was saying “BIDS Helper: Highlighting Expressions and Configurations” . Without “BIDS Helper”, a sample complex Data Flow Task was opening in a few seconds but with BIDS Helper, it was taking 3 or 4 minutes.
Asked customer to remove “BIDS Helper” and see that the complex data flow task is opening in seconds on customer’s machine too.
I’m not very familiar with “BIDS Helper” but the thing that I know is BIDS Helper is the winner of SQL Heroes 2008 Contest .
If you’ve installed any Visual Studio add-in and experienced a slow performance in design mode with BIDS while opening the tasks, try disabling/removing the add-ins and test without all those add-ins.
The BIDS Helper team has spent hundreds of hours working on making the expression highlighter be as efficient as possible. (Unfortunately, the SSIS APIs to determine what’s controlled by an expression are rather sluggish.) That being said, we would really like to see this feature included in the SSIS product itself, since it will be able to highlight expressions and configurations more efficiently than BiDS Helper. I would urge everyone to vote for this request on Connect:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=170582
But until this gets included in the product, I would suggest a few things:
1. Check you’ve got the lastest BIDS Helper installed. Significant performance improvements in the expression and configuration highlighter have been made since the first release.
2. Consider disabling the expression highlighter using the Tools… Options… BIDS Helper settings screen. And consider using the BIDS Helper Expression List to see what’s controlled by expressions instead:
http://www.codeplex.com/bidshelper/Wiki/View.aspx?title=Expression%20List&referringTitle=Home
We feel the BIDS Helper Expression Highlighter is a brilliant feature that’s helpful 95% of the time. If you have a large, complex data flow where it is slow, you’re welcome to disable the expression highlighter.
If you have suggestions on improving this feature, feel free to post them to the Issue Tracker tab: