CODEWITHSUNDEEP
ssislookup
Zerotoinfinity
Zerotoinfinity

Reputation: 6530

Performance issues with lookup

i am facing a terrible issue with the SSIS packages. In my packages I have one lookup and i have a condition like this

OLEDB Source has around 400,000 records and lookup table has around 1,200,000 records. Both the tables can grow but the one which is coming from the OLEDB source will have max around 900,000. Both table has around 40-50 columns to lookup.

There are 51528912896 bytes of physical memory with 32689860608 bytes free. There are 4294836224 bytes of virtual memory with 249348096 bytes free. The paging file has 120246493184 bytes with 109932904448 bytes free.

is there is any effective solution to this?

Upvotes: 1

Views: 4547

Answers (1)

Mike Honey
Mike Honey

Reputation: 15017

At that scale I would be considering using a Merge Join transformation instead of a Lookup. Order by your keys in your OLE DB Source SQL code and define the sort manually (ref http://www.ssistalk.com/2009/09/17/ssis-avoiding-the-sort-components/ ). While slower than a cached lookup, this design tends to scale better in terms of memory use.

Upvotes: 1

Related Questions