Skip to end of metadata
Go to start of metadata

One optimization built in into the lookup_ext is when the condition contains a constant. In case of demand cache or no cache it has no impact, each row will be selected from the lookup table individually anyhow. However in pre_load_cache mode the lookup cache does not need to read all records from the lookup table, it can rather apply the filter when building the cache already.

Take this example here, I want to lookup only those records of region_id = 'US'.

When turning on the trace setting Trace_SQL_Transform, we can see the SQL statement sent to the database to build the cache, it does include the constant filter already.

Actually, the same should be true if the constant used is a global variable, but it is not. I filed bug ADAPT01526522 for that.

  • No labels

1 Comment

  1. Former Member


    Has ADAPT01526522 bug resolved? Does now the LKP SQL statement sent to the database to build the cache include the constant filter when a global variable/ substitution parameter/parameter is used?