September 17, 2009 at 12:17 am
Hi All,
We are Creating a SSIS package for data transfer between two MS SQl server databases, At one point we need to use Lookup transformation, But we are in Dilemma whether to use built-in Lookup transformation or to write a SQL query in the Execute SQL task. Because we have no idea, In performance which one is better .If any one has idea of this ,inputs are appreciated.
Regards,
Guru
September 17, 2009 at 10:28 am
Hello,
I think from a performance perspective, the one that has the most resources (processers, memory, etc.) will perform better. So if the server that the sp resides on has better hardware than the one the SSIS package runs on or vice versa, I'd go with that one.
In addition to this:
I'd lean more towards the sp because it is more centralized and can be changed/updated if need be.
Alternatively, I'd lean on the lookup task because I like to keep everything in the backage and not have to rely on another component (server that has sp)
I could argue use for it in either direction. The biggest thing is deciding where the most benefit is and going with that.
Strick
September 22, 2009 at 8:38 pm
gurukiran.bhat (9/17/2009)
Hi All,We are Creating a SSIS package for data transfer between two MS SQl server databases, At one point we need to use Lookup transformation, But we are in Dilemma whether to use built-in Lookup transformation or to write a SQL query in the Execute SQL task. Because we have no idea, In performance which one is better .If any one has idea of this ,inputs are appreciated.
Regards,
Guru
So you are transfering data from one SQL Server to another. Ok, what are you trying to accomplish with either the Exec SQL Task, why do you think you need either?
CEWII
September 23, 2009 at 4:51 am
In my experience if the lookup dataset is small enough to use full caching then the SSIS lookup component is blindingly fast - everything is done in memory & highly optimised. Just remember fully cached lookups are case sensitive!
If you are having to use partial caching then perhaps a Sproc may be faster.....
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply