Component Arrays - Caching

One Application Engine performance tip I found was to place the results of commonly used queries into Component level arrays that could be searched through on the Application Server instead of making a call to the database. Some code was triggered that many times the DB calls numberd in millions and all the round-trips were expensive.

The following creates component arrays that store the results for a common query, in this case PIN_NUM and PIN_NM from the GP_PIN table.

/* Create a set of global look-up arrays. 
   This will remove the need to call the db many, many times. 

  See CC_FUNCLIB_UOA.LOOKUP.FieldFormula for how to call/use. */

Component array of string &arrPinNumIndex;
Component array of number &arrPinNumVal;

Component array of number &arrPinNmIndex;
Component array of string &arrPinNmVal;

Local SQL &sql;
Local string &pinNm;
Local integer &pinNum, &i;

/* PIN_NUM Lookup: */
&arrPinNumIndex = CreateArrayRept("", 0);
&arrPinNumVal = CreateArrayRept(0, 0);

&sql = CreateSQL("SELECT PIN_NM, PIN_NUM FROM %Table(GP_PIN) ORDER BY 1");
&i = 1;
While &sql.Fetch(&pinNm, &pinNum)
   &arrPinNumIndex [&i] = &pinNm;
   &arrPinNumVal [&i] = &pinNum;
   &i = &i + 1;
End-While;


/* PIN_NM Lookup: */
&arrPinNmIndex = CreateArrayRept(0, 0);
&arrPinNmVal = CreateArrayRept("", 0);

&sql = CreateSQL("SELECT PIN_NUM, PIN_NM FROM %Table(GP_PIN) ORDER BY 1");
&i = 1;
While &sql.Fetch(&pinNum, &pinNm)
   &arrPinNmIndex [&i] = &pinNum;
   &arrPinNmVal [&i] = &pinNm;
   &i = &i + 1;
End-While;

There is a Function that can be called to interrogate the arrays for the value you are searching for:

<* Search the global array that stores cached lookup values from database.
   This removes (a lot of) roundtrips between app server and db server. 

   Example of use:

     /* previously populated arrays - index and value. */
     Component array of string &arrPinNumIndex;
     Component array of number &arrPinNumVal;

     Declare Function GetCached PeopleCode CC_FUNCLIB_UOA.LOOKUP FieldFormula;
     &x = GetCached("ZACC ALA", &arrPinNumIndex, &arrPinNumVal);

   i.e. Pass it a pin name and two, synchronised arrays.
   The first array contains the index, the second contains values that are returned.
   Obviously only works on "2 dimensional arrays"

*>



Function BinarySearch(&arr, &srchVal, &low As integer, &high As integer) Returns integer
   
   If (&high < &low) Then
      Return - 1; /* not found */
   End-If;
   
   &mid = &low + ((&high - &low) / 2);
   If &arr [&mid] > &srchVal Then
      Return BinarySearch(&arr, &srchVal, &low, &mid - 1);
   Else
      If &arr [&mid] < &srchVal Then
         Return BinarySearch(&arr, &srchVal, &mid + 1, &high);
      Else
         Return &mid; /* found */
      End-If;
   End-If;
   
End-Function;

Function GetCached(&srch, &arrIndex, &arrVal) Returns any
   
   &i = BinarySearch(&arrIndex, &srch, 1, &arrIndex.len);
   If &i = - 1 Then
      Return 0; /* not found */
   Else
      Return &arrVal [&i];
   End-If;
   
End-Function;

To use them try the following code:

/* Lookup array arrays: */
Component array of string &arrPinNumIndex;
Component array of number &arrPinNumVal;

&x = GetCached("BASE PAY", &arrPinNumIndex, &arrPinNumVal);