Mapping and optimized loads

[ramonarcusa36]

Hey guys.

I've always read and heard that it's better using ApplyMap than Join, whenever it's possible. I've tried to substitute some Joins which seemed to be perfect candidates for Mapping. However, after using mappings it takes my script much longer to run. I feel like it's not optimized anymore.

Does that make any sense to you?

It that's how it works, I guess I should only use ApplyMap instead of Join when I'm already running an unoptimized load. Right?

Thanks in advance.

[dplr-rn]

manipulations to a load (i.e. other than load *) will end up making the load un optimized.

Applymap is definitely faster than join in a standalone scenario.
you can try loading from qvd into a temp table and then resident loading that one to do apply map. this may be faster than a join in some scenarios and not in some other

but overall the idea is to use best/fastest approach for specific business scenario / logic.

[Brett_Bleess]

The only thing I have that may help is the following Design Blog post on the subject, hopefully that may provide you the info you need to decide in this use case which way is the better way to go!

Join versus Apply Map

Regards,
Brett

[jerrysvensson]

It depends.

If your ApplyMap contains a lot of rows and your source is a qvd file.

A join could be faster, since mapping load is not optimized and depends on how fast your disk is.

I have encountered this and my reloads got a lot faster using join instead of ApplyMap(). 

Compare both solutions if you have time.

[jonathandienst]

AFAIK a join is not optimized. Optimized loading refers to a very fast loading from a QVD into a table with minimal transformation. If you are doing it with a join, the actual load step may be optimized (if loading from a qvd), but a join is just a join.

A mapping load is never optimized. This means that there are scenarios where a normal join may be faster than building a mapping table and using ApplyMap() -- especially is the mapping table is very large and used only once. There are reasons for using an applymap other than speed, of course (no doubling, filling in default values etc).

This is a case for testing both scenarios to determine which works best for you.