lookup_ext very slow in DS 4.2

[Former Member]

In the process of upgrading from DS 4.1 to 4.2 (on separate servers), and for the most part it is going well, however have a certain dataflow giving me problems.

I've narrowed the issue down to one query transform where it performs 7 separate lookup_ext functions. In 4.1, they all run without issue, and the entire dataflow completes in about 100 seconds. In 4.2 one lookup proceeds very, very slowly (one row every 5-10 seconds) and never completes. Source and target DBs are shared between 4.1 and 4.2, so that is not the issue.

I have not changed any piece of the dataflow during the upgrade from 4.1 to 4.2. Things I have tried:

- Changing DOP to 1 (defaults to 2)

- Changing cache settings on the lookup_ext function (pre_load, demand, no_cache)

- Changing to pageable/in-memory cache

The only workaround I've found is to completely replace the lookup_ext with a left outer join and an nvl (default value for no match on the lookup_ext is 0), but I have to set cache=yes on BOTH source tables. Join rank has no impact. But this is not a desirable workaround since we use lookup_ext in many places.

Has any one else experienced this and if so how did you resolve it?

[Former Member]

Approximately how many rows we talking about?

Does your Target table have Update Control - Auto correct Load - 'yes', by any chance ?

How about Lkps with large data set to 'Run as a Separate Process' ?

[Former Member]

The source table set combines about 1.2 million rows with about 600,000 rows, and the target table ends up with about 550,000 rows.

I don't have auto correct load set to yes, and none of the lookups are set to run as a separate process.

I cut down the result set to about 158,000 rows by adding another where clause to one of the queries, and in 4.1 the job ran in about 1.5 minutes, and it took 35 minutes in 4.2.

[Former Member]

Sorry, I meant to say, set the Lookups with large tables to 'Run as a separate process' if you haven't already.

Anyway you said it was running faster before upgrade, If you didn't change anything, it might be something to do with upgrade, Lets see if some one who has similar upgrade issue might comment...