Thursday, December 3, 2009

SSIS Slow - Ole DB Data Flow Optimazation

Do you build SSIS (SQL Server Integration Services) Packages?
Are you constantly running into Visual Studio acting sluggish or giving you "Microsoft Visual Studio is Busy"?

In my case, I use views a lot when I'm querying my source data (for reasons of maintainability). When you use views in an OLE DB Source, anytime you change the scope of what you are working on, e.g. jumping back to the control flow and then back into the Data Flow, Visual Studio rebuilds its meta-data and does validation checks to see if source or destination attributes have changed.

Resolution
Set AccessMode to "SQL Command"
And set SqlCommand to a Select query of your view.
Example:
SELECT * FROM v_MyView

Why does this work?

"...it calls OpenRowset in the validation phase to retrieve column metadata, and later in the execution phase to read out the data.

Testing has shown that using a SELECT statement can be at least an order of magnitude faster, because the adapter issues the specified command directly through the provider and fetches the data using sp_prepare without executing the command, avoiding the extra roundtrip and a possibly inappropriate cached query plan."

If you have any questions... Just ask, I'll be happy to assist where I can.

1 comment:

Coco Ada said...

Fashion is an anytime alteration sector. What is apparent as adventurous and admirable today will be
omega replica anachronous and banal tomorrow. It can be a active plan attempting to accumulate up with absolute trends and fads, and anon you ability be larboard accepting a closet complete of breitling replica apparel you cartel not abrasion accustomed that they are traveling to be apparent as . At atomic you hublot replica apperceive assertive apparel like the little atramentous dress will consistently be a apparel staple. Addition account you don’t accept to anguish about acceptable yesterday’s debris is an Ebel watch.Ebel had apprehensive ancestry in 1911, but it wasn’t connected afore they won a gold badge at cartier replica the Swiss National Exhibition -drawing added absorption from added Omega Occasion Montre manufacturers. Throughout the next few decades Ebel connected to rolex replica accomplish a name for themselves aural the watch business, creating aberrant timepieces. Right now not abundant has changed. Ebel continues to be a domiciliary name, although it is now broadcast by louis vuitton replica the Movado watch company.