It depends on how much data your processing...i.e., the Lookup gonna cache.
Its always the unconnected since, it returns one port value....both of them serves different purposes depends on the usage.
--> Need to use for dynamic cache purpose
---> Use for returning multiple columns from single port
---> It will execute for each and every row, so it tooks long time
---> static cache
---> It will return one port value
---> This will execute only if the lookup condition satisfies.
performance wise unconnected lookup is better because its like reusable one and it will lookup the values only the condition meet the criteria.
Generally Unconnected Lookup transformation will improve performance.But the scenario is demanded for using dynamic cache or multiple output ports,then we will go for Connected lkp.
Unconnected can be called on conditionally and can be call multiple times in mapping.
We can call lkp by passing deferent values from deferent ports in expression .
The choice of LKP depends upon the amount of data in the mapping stream.
If the condition is such for every incoming record the lkp condition is utilized then better use a connected lkp rather than an unconnected vice-versa.
Connected lookup used for dynamic cache and return multiple columns from single port , But Un-connected lookup is for static cache and return one port value. Performance depends on how much data we are processing (cache). Better to use Unconnected Lookup transformation it will improve performance.