0

I need to join 2 tables with more than 20 million reocrds.Which of the below mentioned solution is good

    1 one Oledb componet and a lookup component

or

 2 Two oldeb componets,2 sort componets and a merge join componets
user1254579
  • 3,901
  • 21
  • 65
  • 104
  • 2
    From my personal experience, Lookup table size should be smaller to gain the maximum performance gain. I would prefer to do it with T-SQL with join hints. – Zerotoinfinity Mar 12 '14 at 16:19
  • I agree. If the data is on the same server use T-SQL to join it on the server first. Also the merge join can result in multiple results (if multiple records match) whereas the lookup will not so they actually do different things – Nick.Mc Oct 22 '14 at 07:47

2 Answers2

1

depends among other things on how many bytes from the look up table you need. If you go for merge join it probably is a good idea to read the by sql ordering by key and then connect directly to the merge join transformation.

Jayvee
  • 10,670
  • 3
  • 29
  • 40
  • Do you recommend the T-SQL join instead? If i use merge join then i have to use the sort component rt? Sort is very expensive too – user1254579 Mar 12 '14 at 16:38
  • 1
    Unless your lookup can be cached, i.e. very small record size given the 20M rows, it seems that joining is a better idea. You may also want to explore a complete solution based on sql command, not just the sort but also the join. – Jayvee Mar 12 '14 at 16:49
0

When there are many records, the lookup option is better: it gives better performance because it is a synchronous transformation.

Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
raj
  • 1