Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

OLE DB Source -- SQL Command performance? Expand / Collapse
Author
Message
Posted Wednesday, February 20, 2013 8:18 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 7:28 AM
Points: 317, Visits: 1,530
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




Post #1422112
Posted Wednesday, February 20, 2013 12:27 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:04 PM
Points: 7,141, Visits: 12,768
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
Post #1422245
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse