Join versus Lookup for Flat Files

[pbackstrom]

I am joining many flat files into a single table. I have a series of joins (a to b, then ab to c, then abc to d etc). I am wondering if one driving table with a series of lookups would be any better or worse for performance or for any other reason. It would sure make the flow through the mapping look better and make maintenance easier if fields are added to the tables.

Several of the tables number are close to the same number of rows (1:1 or 1:few), so it's not a clear lookup.

It looks from the doc like both functions cache and use memory similarly, but wonder if anyone has any comment or experience one way or the other.

Thanks.

 

[blom0344]

Why not test both options?
I have mappings with some 20 lookups that perform very nicely as long as the lookup tables are small. I'd hate to build and maintain them with using only joins....

In your case I would not expect much of a difference, but that is more a gut-feeling than hard facts

Ties Blom
Information analyst
tbl@shimano-eu.com

 

[pbackstrom]

FYI (anyone), I took the time to rewrite it as lookups, for future maintenance sanity. It runs in half the time. None of the files are large (1700 records), YMMV, but thought it was interesting. Hurray for flat file lookups.