Chapter 41. Look-ups

The first tests I remember performing on the product were testing look-ups. I remember finding some problem and calling Doug about it; I also remember his comment about the test that I had set up, “Well this is pretty complex.”

The look-up process can seem pretty complex, and it will take us two chapters to cover. This one will focus on the logic table and extract process; the next will explain the reference file phase and how it supported those results.

We’ll make a change to the input parameters to the VDP Builder to add view 3263 to the other two views we are already executing.

Logic Table

Although we’ll execute all three views, we’ll only show the logic table for our new view. Thus the rows of the logic table continue from the prior example.

LookupLogicTable
Figure 102. Lookup Logic Table
The logic table contains some new functions.

  • The JOIN function specifies that the next few steps are going to prepare the keys for the join.
  • The LKE function moves data for a lookup key from each event file record to the lookup key buffer.
  • The LUSM uses the values that have been placed in the lookup key buffer to search the in memory table to find a corresponding match. The results from the LUSM can either be that a match was found, or a match was not found, and the GOTO Rows 1 and 2 are next executed for either of those cases respectively.
  • If a match was found, the located matching record can be used in a number of ways. It might be used to test to see if additional values on the record are of a particular value for selection. This would use a CFEL function, for Compare Field Event-file value to Looked-up value. A class test against the looked up value could also be executed. Row 26 in this logic table tests to see if the looked-up value at position 1 for a length of 30 on the LR 1262 matches the constant “K & N jone…”, thus a CFLC for Compare Field Looked up value to Constant.
  • Data from the looked up record can be moved into the extract record through a DTL function, such as row 38.

The following table shows these additional functions.

SimpleLook-upFunctions
Figure 103. Simple Look-up Functions
The Reference File Phase process prepares the in-memory reference file table, and is discussed in the next chapter.

Extract Phase

The following is the trace output for only those rows applicable to our new view.

LookupLogicTableTrace
Figure 104. Lookup Logic Table Trace
Note that we can see that the key values supplied by the event file for the first LKE is “522349999”. The LUSM was performed, and because the next executed logic table row is 26, the results of the LUSM were found. In other words, the reference file had a record with the key of 522349999. The CFLC function compares the constant of “K & N jones” to the value on the found looked-up record, which is also “K & N jones”.

On this first event record, two other lookups and compares are also performed, until we get to logic table row 34. At this point, the trace for view 3263 against record 1 stops, and we go on to event file record two (we have omitted the trace rows for view 3261 and 3262 in this printout which would have been rows 1-21). This tells us that the view did not meet the general selection criteria.

Here is the selection criteria:

If ({EVENT_JOURNAL_TO_LE_DESC.LEGAL_ENTITY_TITLE}= "K & N jones family" 
  And {EVENT_JOURNAL_TO_CC_DESC.COST_CENTRE_TITLE} = "DAD"
  And {EVENT_JOURNAL_TO_ACC_TITLES.ACCOUNT_TITLES} = "checking account")Then
    SKIP
EndIf

The “Skip” statement is what causes the record to be skipped. The lookups resulted in values of “K & N jones family”, “DAD” and “checking account”. The skip statement causes the GOTO True and GOTO False rows to be swapped on the CFLC function for the Account Title. Typically the true row is the next logic table row, and the false row is the a few rows later. Because of the skip statement, they are reversed.

The following are the output records for this view. The values in these output records can be seen in the logic table trace for DTL and DTE functions.

LookupExtractOnlyViewOutput
Figure 105. Lookup Extract Only View Output

Multi-step Look-up

The test I was performing that day which Doug found fairly complex was a multiple step lookup. That means that the values for a lookup can come from a prior lookup, rather than all having to come from the event file.

ComplexLook-upFunctions
Figure 106. Complex Look-up Functions

LKE function becomes a LKL function: Build Lookup Key from Looked-up value. CFEL functions can similarly become CFLL to compare two looked up values.

Date-effective Joins

You’ll remember we considered the needs for date-effective joins when using the SAFR method1. Let’s examine how date effective joins are implemented in the SAFR process.

The date field on the target reference table is technically not part of the key because the values passed in for the search do not have to be matched byte for byte. So the effective date field on the target logical record could be thought as being between the key and the data areas of the record.

Although technically not part of the key, the logic table contains LK functions, specifically Lookup Key Date or LKD functions, to place the desired effective date in the lookup key buffer. And, predictably, these are the last LK functions for a particular join.2

The dates can come from constants, LKDC. The date can come from the event file, LKDE, or from a looked-up value on another reference table, LKDL.

The constant value on the LKDC can come from multiple places. For example, in a view definition someone may place a hard-coded date, like December 31 of some particular year. Alternatively, the developer might also use a keyword in the workbench, such as run-date, when specifying what date to use. This keyword is translated into a specific date constant during the logic table phase of processing. By the time the logic table arrives at the extract process, it is indistinguishable from a hard coded date. The translated constant date value is shown in both the logic table print and the logic table trace. Use of the key words allows for dates to be passed in as part of the batch process; thus the View logic remains the same day after day, but the date used in the view can vary.

When the extract engine performs a search on a date effective table, it will “fall back” to the record with a matching key, whose effective date is less than or equal to the value provided by the LKD function. If there is no record with an effective date less than or equal to the effective date, the extract engine will execute the “not found” condition.

Join Optimization

I think the idea was probably Jay’s, but I don’t think I know for sure where it started. It was that we could add another step into the SAFR Scan engine process to analyze the logic table and remove certain joins which had been performed by a view earlier in the process. For example if view 3261 did a join to the account master table, and 3262 required the same join, there would be no reason to go build the look-up key again; the results of the join were already known and the results of the join could already be known.

It was such a simple idea, but the word “simplistic” might be a better description. It started a process of work that continued part time for probably a year. We kept running into technical problems every step of the way. When we finally got it to work, the results ended up being remarkable. As we put the new process into production after making the code permanent and more testing, we found that we could reduce the CPU time on some already very efficient, join intensive processes by 60 and 70%. It was nearly unbelievable.

The result is a process whereby the more views that are resolved in one SAFR execution, with greater numbers of lookups to shared table, the greater the efficiency is for each join.


Next, let’s consider how the data is prepared for joins by understanding the Reference File Phase.

Next:

Parent Topic:  Part 5. The Programmer

2 Note that if a date is part of a key and should be matched byte for byte, it would typically be moved to the key by an LKE function. Only effective dates use LKD functions. Because date effective joins are not matched byte for byte, placing the date at the end of the keys allows the comparison to be performed after matching the key.