Skip to end of metadata
Go to start of metadata
Summary

In order to lookup a row in another table we have many options, not only the lookup_ext function with its three different caching options, or a join, sometimes other approaches are made like adding the lookup_ext into a custom function where some pre- and postprocessing can be done. But what is the performance impact of each method?

Attached is the ATL (DI 11.5.1) with the six DF_xxxx objects, the source_ds and target_ds and a custom function.

For the test we will use the CUSTOMER_MASTER table with its 520'000 rows for both, the source table and the lookup table.

The results:

Method

Time

Database Join

40secs

DI Join

36secs

DI Join (nested loop)

93secs

lookup_ext (pre_load_cache)

46secs

lookup_ext (no_cache)

126secs

sql function

1260secs

lookup inside a custom function

53secs

Calling a stored procedure

142secs

Further considerations

Requirement

Join

lookup_ext

sql function

stored procedure

Return all matching rows (master-to-detail-join)

Yes

No

No

No

Return one row at max

only if PK is used

Yes

Yes

Yes

Return at least one row

use outer join

Yes

Yes

Yes

Make the lookup table prominent in the dataflow

Yes

No

No

No

Hide all the small lookup tables

No

Yes

Yes

Yes

Control the join method and caching

Somewhat

Yes

Yes but no caching

Yes but no caching

Allows different SQL for each lookup call

No

No

Yes

Yes

Reuse existing database code

No

Somewhat

Yes

Yes

Allows to return multiple columns

Yes

Yes

No

Yes

Find the max/min record for a given condition

No

Yes

Yes

Yes

Handy Hint

If multiple rows should be returned, like joining to a master record all its detail records, you have to use joins. If multiple rows could be found but only one should be returned, then you cannot use joins. In all other cases you can have the tendency to use lookup_ext() for small lookup tables and for cases where the lookup table is not so important, so it should not be visible in the dataflow right away. Often you have either this or that, then you can decide based on these thoughts.

Attachment

ATL.zip (7.17 KB)



  • No labels