SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


OLE DB Source -- SQL Command performance?


OLE DB Source -- SQL Command performance?

Author
Message
peterzeke
peterzeke
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1838 Visits: 1766
The following question is based on my zero-understanding of how SSIS performs regarding the use of server memory.

Does someone know if a SQL command in an OLE DB source in SSIS 2005 acts like pass-through query or open query? I'm essentially wondering where the "load" ends up on SQL server -- i.e., does the memory allocated for SQL Server get leveraged when pulling the data or is some other memory space for SSIS being leveraged?

The above question is related to a recent change that needed to be made to an SSIS package that was using a merge-join followed by a conditional split as a method for dealing with a range-lookup: i.e., join data from one data source to another using matching IDs and where the "event date" of table A is between the StartDate and EndDate of table B.

A decision was made to remove the merge-join + conditional split approach and instead embed this into a single SQL command in an OLE DB source by merely using a left join from table A to table B. This change doesn't seem to improve the overall performance time. Should there be an expectation that performance would improve?

thanks,
Pete



Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36960 Visits: 14411
peterzeke (2/20/2013)
The following question is based on my zero-understanding of how SSIS performs regarding the use of server memory.

Does someone know if a SQL command in an OLE DB source in SSIS 2005 acts like pass-through query or open query? I'm essentially wondering where the "load" ends up on SQL server -- i.e., does the memory allocated for SQL Server get leveraged when pulling the data or is some other memory space for SSIS being leveraged?

It's just like any other client. The query gets executed on the SQL Server in the DB engine's memory space and the results are stored in SSIS memory space as they are returned and flow back through the Connection Manager and into some other process happening within the SSIS package.

The above question is related to a recent change that needed to be made to an SSIS package that was using a merge-join followed by a conditional split as a method for dealing with a range-lookup: i.e., join data from one data source to another using matching IDs and where the "event date" of table A is between the StartDate and EndDate of table B.

A decision was made to remove the merge-join + conditional split approach and instead embed this into a single SQL command in an OLE DB source by merely using a left join from table A to table B. This change doesn't seem to improve the overall performance time. Should there be an expectation that performance would improve?

I would expect a performance gain if for no other reason than you would likely be processing less data over the wire getting it from the database engine into the SSIS package. I would also be a little surprised if the database engine could not do a better job of joining the data within the database engine than when done manually within SSIS, however maybe SSIS is doing a great job of keeping pace in that area.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search