Dear all, I am bit confused and also not getting the right answer on google regarding the best transfor to use. Basicall I need to find out the new rows and Updated rows after Merge Join transform, same i can do with lookup. Can some one explain me which one is better and why.Anky

please check the similar thread ... USER "TheViewMaster" had shared some real time fact with his data. check the result here... it will help you... " Test1 (Lookup): Source: multi-flat-file source (4 .txt's) with total of 175513 records and 88 columns Lookup is a query access table 248250 records pulling 61280 records and about 25 columns 2 outputs - Listing Found (56523 rows) and Error Listing Not found (118990 rows) Also lookup is Full Cache mode and gives Warning: found duplicate key values. Result: Finished, 4:11:00 PM, Elapsed time: 00:00:15.437 Note: Memory usage of PC peaked at 1.8GB with CPU usage jumping to 100% once. Test 2 (Merge Join): 1st Source: multi-flat-file source (4 .txt's) with total of 175513 records and 88 columns 2nd source: OLE DB Source with query access table 248250 records pulling 61280 records and about 25 columns with ORDER BY ID. Out put is marked sorted by ID column. 1st source is Sorted using "Sort transform". Then "Merge Joined" with ole db via Left outer join (Sort on left) Then "Conditional Split" based on ISNULL(oledbsource.ID) Result: Finished, 4:49:33 PM, Elapsed time: 00:01:14.235 Note: Memory usage of PC peaked at 2.6GB with CPU usage jumping to 100% twice. " Let us TRY this | Mail me My Blog :: http://quest4gen.blogspot.com/

Hi Thanks for the reply but in the Merge Join test 2, they have used Sort Transform which can be done at the OLEdb Source source also by writing an execute SQL query. and then changing the IsSorted option in Advance Editor to true. So in that case we dont even have to use Sort Transform. Pls comment on this Thanks Anky

Hi Thanks for the reply but in the Merge Join test 2, they have used Sort Transform which can be done at the OLEdb Source source also by writing an execute SQL query. and then changing the IsSorted option in Advance Editor to true. So in that case we dont even have to use Sort Transform. Pls comment on this Thanks Anky yes, you are correct.. so better write a SQL query with all the joins and get the data in DFT. Let us TRY this | Mail me My Blog :: http://quest4gen.blogspot.com/

Yup but my question still stays as it is that which one to use Lookup or Merge join , if i have already sorted the data at the source then which one will be faster and efficient in case of heavy volume and why ? waiting for a quick reply Thanks Anky

Hi Thanks for the reply but in the Merge Join test 2, they have used Sort Transform which can be done at the OLEdb Source source also by writing an execute SQL query. and then changing the IsSorted option in Advance Editor to true. So in that case we dont even have to use Sort Transform. Pls comment on this Thanks Anky Yep, if you can sort at source then do so. Sorting in the dataflow using the Sort transformation is very very resource intensive (i.e. slow). Really, there is no good way to answer your original question because there are a lot of factors that can influence your decision. For example, if you have masses of incoming data and a very small lookup set then the Lookup may well be the better option; if your lookup set is large then merge Join may well be better. My advice is to you is the same as it always is in these circumstances - test and measure, test and measure, test and measure! Hope that helps.http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Also be aware that Lookup and merge Join do NOT exhibit the same behaviour in all circumstances. For example, if the key in your lookup set is not unique then Merge Join will join with all instances of that key where in the Lookup it will just use the first one it finds. http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me