Home page Home page Home page Home page
Pixel
Pixel Header R1 C1 Pixel
Pixel Header R2 C1 Pixel
Pixel Header R3 C1 Pixel
Pixel
By Sprezz | Tuesday 24 May 2011 17:16 | 0 Comments
One of our current mega-projects is converting a very large AREV system over to OpenInsight and of course like most large systems it relies heavily on batch processes that perform selects and refine these selects before presenting the results. AREV had the useful status line update facility to let you know how processing was going but regretfully OpenInsight omitted this - presumably because lacking a dedicated status line there was nowhere to put it. With hindsight a SEND_INFO every 1% would at least have allowed the developer to intercept and update the information from a select.

We tried the usual approach of looking the other way but when some of the queries were taking upwards of five minutes to return results to the user we decided that enough was enough.

The problem as we saw it was split into two areas - selects against entire non-indexed files and selects against indexes. This article deals with the former. The next article in this series will deal with the latter.

To start let's be frank. This blog isn't about giving away the family silver. It's about telling you what is possible using Rev products and giving you enough information to do it yourself if you want to. To this end we won't be publishing reams of code - we'll be publishing snippets and explanations of how to achieve the end result!

The steps involved
At its simplest the easiest way of providing the user with a progress indicator when reading through a file or a resolved active select list is as follows :- include within the select statement an additional column which makes a callback to a user defined routine to provide feedback to the user about where we are in the process. Like most consultancy houses, Sprezz has a generic progress window that we use to keep the user informed of progress through a process. This is called in one of four ways :-

  • To start the progress window
  • To update the progress window
  • To update the log portion of the progress window
  • To end the progress window
Thinking about this, this means that for our select statement to report back to us, we need to do the following :-

  • Initialise our counter variables and any labelled common
  • Start our progress window
  • Call our rlist select command
  • From within the rlist select command call back to a routine to update the progress window
  • On termination close down the progress window
Now that's all very well, but it's a faff to have to go in and change all of our existing calls to RLIST to do this set of actions, so it makes sense to create our own select program that encapsulates all of the above steps.

The Result
Having done this we can then just issue a call to our replacement routine passing it the select statement required, and Bob as they say, is the brother of your mother. So in our simple example we issue the command :-

Call gen_Select_With_Progress( "SELECT CLAIMS" )

and the result we see is :-


(Please excuse the background colour of the MDI Frame. Like most development shops we use three systems, development, testing and live and we colour the background vividly to remind the person working on the system just where they currently are!)

The Breakdown
So looking at the important parts, let's see how they work - firstly let's look at a simplified version of our gen_select_with_progress. Our full version has more flexibility built in but we've stripped this down to the bare bones to make it easier to understand.

The shell program

Function PList( object, method, param1, param2, param3, param4)
/*
   Author      AMcA

   Purpose     To do a select with a progress bar

*/


   equ version$   To "1.0.0"
   $uses CMG*APPROW**GEN_CONFIGURATION:GEN_RESOURCES

   Declare Function get.reccount, gen_progress, retStack, zzx_res2Str
   $insert gen_dict_callback_equates
   $insert gen_module_equates

   retVal = TRUE$
   atSelf = retStack()<1>

   if method = "" Or method = "SELECT" then
      call set_status( 0 )
       /*
         assume that object contains the select statement To execute
      */

      object = trim( object )
      table = field( object, " ", 2)
      open table To vTable then
         if @rec.count then
            rowCount = @rec.count
         end Else
            rowCount = get.recCount( vTable, "", "")
         end
         loopCounter@ = 0 ; * loop counter
         * start the progress window
         retVal = gen_progress( "STARTWIN", rowCount, caption)
         * log what is being done
         retVal = gen_Progress("LOG", "", object)
         * add our callback into the select - it will now be called once 
         * per row evaluation
         addOn = " WITH ZZX_CALLBACK = 1"
         if index( object , " WITH ", 1) then
            addOn = " AND " : addOn
         end
         call rlist( object : addOn, 5, "", "", "" ) ; * do the actual select 
         call gen_progress( "END" )
      end else
         call fsMsg()
      end
   end else

      locate method In "UPDATE" using "," setting pos then
         on pos goSub update
      end
   end

return retVal

That copes with list items 1, 2, 3 and 5 so how does the CALLBACK dictionary item work?

CALLBACK Dictionary Item
If you place a compiled dictionary item into the SYSDICT table it can be used in all of your SELECT statements. All that this has to do is to call your update program. Again a simplified version of our SYSDICT item...


   declare function gen_select_with_progress, isEventContext
   $insert gen_dict_callback_equates
   @ANS = ''

   if isEventContext() then

      dict   = @DICT
      id     = @ID
      mv     = @MV
      record = @RECORD
      rnc    = @RN.COUNTER
 
      @ans = gen_select_with_progress( "", "UPDATE")
 
      @DICT   = dict
      @ID     = id
      @MV     = mv
      @RECORD = record
      @RN.COUNTER = rnc

   end

return @ans

As an aside, for efficiency we'd be better off using the transfer statement to move @RECORD and @DICT in the above code as all strings over 20 characters in length are held on the heap rather than in a descriptor, so a copy statement wastes resource.
The Update Routine
Finally the part of gen_select_with_progress that updates the progress meter.


update:

   loopCounter@ += 1
   if mod(20, loopCounter@) = 0 then
      call gen_select_with_progress("UPDATE",loopCounter@, |
                                    "{COUNT} of {MAX} - estimated time remaining|
                                    {ETR}")
   end

return 

The only thing to mention is that loopCounter@ is a labelled common variable. Because the update program and the calling dictionary item are on the program stack there is minimal overhead to this approach. It also allows for the user to press a cancel button and exit a protracted select. In our testing we've seen this add anywhere between 5 and 20% overhead. This might seem like a lot but when a user is staring at a blank screen waiting for a result time drags a lot more slowly. In addition the estimated end time allows the user to plan their time more effectively :).

In the next article we'll look at indexed selects.

Labels: ,

By APK | Monday 23 May 2011 13:16 | 0 Comments
Recently at Sprezz Towers, we had an interesting discussion on resolved selects, latent (or unresolved) selects, sizelocks and manipulating records while in a select list. Surprisingly, there was some confusion, even between ourselves. Once we hashed it all out, we thought it would be a good idea to post on the topic.

Types of Selects

As most of you know, there are two types of selects in the Revelation environment, latent and resolved. In resolved selects, the results are processed and stored in a temporary SYSLISTS (or LISTS) record. In latent selects, the record IDs are not returned until the READNEXT (or READNEXT BY) statement is executed. Resolved selects are initiated through the RLIST function or PERFORM statement. Latent selects are initiated through the Basic+ reduce subroutine and select statement.

What many people do not know is that inside each resolved select is a latent select waiting to get out. In order to generate the resolved select, the system must process the select through a latent list.

So, if you were to issue the following as a resolved select:
  SELECT SYSPROCS WITH TYPEID = 'STPROC' BY ENTITYID BY APPID
the system will basically execute code similar to this:

0001  * // Parse the sentence
0002  * // Assign some vars
0003  * // Open handles
0004  * // Other initialization based stuff 
0005  abort = FALSE$
0006  script = "WITH {TYPEID} EQ 'STPROC'"
0007  sortList = "APPID"
0008  mode = NEW.REDUCE$
0009  tableName = "SYSREPOS"
0010  cursor = 0
0011  flag = ""
0012  
0013  call fix_lh( tableName, UPDATE_SIZELOCK$, INCREMENT_SIZELOCK$ )
0014  
0015  call reduce( script, sortList, mode, tableName, cursor, successFlag )
0016  if successflag else
0017      * // set error
0018      abort = TRUE$
0019  end
0020  
0021  if abort else
0022      select tableName by sortList using cursor else
0023          * // set error
0024          abort = TRUE$
0025      end
0026  end
0027  
0028  if abort else
0029      listData = ""
0030      reccount = 0
0031      done = FALSE$
0032      loop
0033          readnext id using cursor by AT else
0034              done = TRUE$
0035          end
0036      until done
0037          listData := id : @FM
0038          reccount += 1
0039      repeat
0040      
0041      listData[ -1, 1 ] = ""
0042      call dostime( dateTime )
0043      listKey = "W*" : @STATION : "*" : @APPID : "*" : dateTime
0044      listHeader = @VM : dateTime : @VM : @APPID : @VM : reccount : @VM
0045      listRec = listHeader : @FM : listData
0046      write listRec on hLists, listKey else
0047          * // set error
0048      end
0049  end
0050  
0051  if abort else
0052      call make.list( cursor, listData, hDataHandle, hDictHandle )
0053  end
0054  
0055  call fix_lh( tableName, UPDATE_SIZELOCK$, DECREMENT_SIZELOCK$ )

More or less...it's not perfect, but it's a pretty rough idea of what happens.

The system parses your sentence, generates a reduce, performs a select/readnext, stores all the returned keys, writes them off to a LISTS record (and updates the save select list queue) and then does a make.list so they are active and available.

In short, resolved selects are a giant shell around reduce/select. When you work with a resolved select, you're processing the list twice; once to resolve it, and once to process it.

It's always quicker to skip the shell and work with reduce/select yourself. However, as with many shelled functions, the system also handles some maintenance work for you.

In the next article, we'll talk about the maintenance work required to handle all this correctly.

Labels: , , ,

Pixel
Pixel Footer R1 C1 Pixel
Pixel
Pixel
Pixel