February 20, 2013 at 8:18 am
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
February 20, 2013 at 12:27 pm
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply