CODEWITHSUNDEEP
sqlperformanceselectssis
user1589188
user1589188

Reputation: 5736

Does it matter where I SELECT fewer columns in SSIS in order to increase performance?

Say in a data flow task, I have an OLE DB source. I would like to increase the performance of the SSIS. Does it matter where I SELECT less columns?

  1. Create a view in database that SELECT less columns, use that as the source.

  2. Type SQL SELECT inside the source to select less columns.

  3. Choose the table then untick the columns inside the source.

Thank you

Upvotes: 0

Views: 976

Answers (2)

billinkc
billinkc

Reputation: 61211

A trivial clarification on @praveen's answer. There is a performance difference, however slight, between selecting a table in the drop down list and writing the equivalent SELECT * (and even then, enumerate your columns) because of the overhead of the open rowset call.

That said, if you need to get the best performance out of SSIS, be as explicit as possible. If you only need 5 columns out of 30, explicitly select those columns. SSIS might pull back all 30 columns and filter them client-side. You just wasted network bandwidth and you had to read the clustered index to get all that data where a covering index might have existed. Increased read cost + increased network cost + increased memory cost for the columns SSIS is going to discard? You're paying a good bit of expense up front before any work can begin.

You cannot get faster throughput than your starting component supplies.

Upvotes: 4

praveen
praveen

Reputation: 12271

In SSIS ,you shouldn't be using Table name or view from a variable as they tend to pull all the data .It behaves as Select *.

The adapter opens a rowset-based on the table or view. Then it calls OpenRowset
in the validation phase to retrieve column metadata, and later in the execution
phase to read out the data.

The best way is type out the SQL command which increases the performance . But using View , the performance is increased many folds .Check this article for using View in OLEDB SOurce

Upvotes: 5

Related Questions