Hash a temp-table row

Question

Backstory

I work on the backend for Android clients. There are a lot of data to be displayed and some offline functionality needed, so I have to solve synchronization. Said data consists of temp-tables.

Our solution

Right now, to let the client know about possible changes and not to enforce complete client db wipe and creation (takes a lot of time), I make a hash of each of the temp-table rows. Said hash is created by taking the temp-table row, creating a JSON for it and then making a hash out of that JSON. It is working.

My problems with this solution

I think it is not the ideal solution. All those operations look demanding and from my testing, it shows:

For each for some data; the base line, no action = 90 ms
Population a temp-table inside of that for each with buffer-copy = 400 ms
Calculating hash with the above solution after the population inside the for each = 2 880 ms

What I want

I am interested if we are doing something wrong. Is there a better solution for this problem? Is there less demanding way for creating hash out of each individual temp-table record?

We are currently using OpenEdge 10.2B.

Procedure responsible for hash creation, create_hash create widget-pool.

define input parameter inp_hBuffer as handle no-undo.
define output parameter out_cHash as c no-undo.

define var ttDynamic as handle no-undo.
define var hBufferTT as handle no-undo.

define var lResult as l no-undo.
define var cDataTT as longchar no-undo.

define var itime as i.

do on error undo,return error:
   if not(valid-handle(inp_hBuffer) and inp_hBuffer:type = 'BUFFER':u) then
      return error substitute('Neplatny typ vstupniho parametru predaneho procedure "&1".','m_ghashb':u).

   create temp-table ttDynamic.
   ttDynamic:add-fields-from(inp_hBuffer).
   ttDynamic:temp-table-prepare(inp_hBuffer:table).

   hBufferTT = ttDynamic:default-buffer-handle.

   hBufferTT:buffer-copy(inp_hBuffer).
   ttDynamic:write-json('longchar':u,cDataTT).

   out_cHash = hex-encode(md5-digest(cDataTT)).
end.

return.

And its usage

for first lbUsrtab where
    lbUsrtab.ucje = GetUcje('m_usrtab':U) and
    lbUsrtab.login-name = LoginName no-lock,
each lbWcesta where
    lbWcesta.ucje = GetUcje('wcesta':U) and
    lbWcesta.kodu = lbUsrtab.kodu no-lock,
each lbWciorg where
    lbWciorg.ucje = GetUcje('wciorg':U) and
    lbWciorg.cest = lbWcesta.cest no-lock,
each lbKontaktr where
    lbKontaktr.ucje = GetUcje('kontaktr':U) and
    lbKontaktr.corg = lbWciorg.corg no-lock
    by lbKontaktr.zako descending
    on error undo, return error return-value:

    create ttKontaktr.
    buffer-copy lbKontaktr to ttKontaktr.
    run create_hash(input buffer ttKontaktr:handle, output ttKontaktr.hash).
end.

What I want

to know if we are doing something wrong, this solution does not look right to me for some reason and I feel confident that someone had to face this too (creating a hash of a temp-table)
it does take a lot of time and should be a problem in the future, so I would like to approach this sooner rather than later

What would make a solution "better"? Less time? Less memory? More maintainable code? It would also be helpful if you posted sample code showing your current solution. — Tom Bascom, Jul 14 '16 at 11:58
Hi Tom, thanks for a reaction. I have updated the question as requested. Less time is the biggest problem for me. — vsobotka, Jul 14 '16 at 12:19

score 4 · Accepted Answer · answered Jul 14 '16 at 17:23

This is an almost 20 times faster solution to your hash (for 1000 invokations it's 320 msec vs 17 msec):

PROCEDURE hash2:
    DEFINE INPUT  PARAMETER inp_hBuffer AS HANDLE    NO-UNDO .
    DEFINE OUTPUT PARAMETER out_cHash   AS CHARACTER NO-UNDO .

    inp_hBuffer:RAW-TRANSFER (TRUE, hRawField) .

    out_cHash = HEX-ENCODE (MD5-DIGEST (ttWithRaw.raw_field)).
END PROCEDURE .

You'll need a helper temp-table with a RAW field and a single record, as the buffer object handle's RAW-TRANSFER method only works on a buffer field object handle, so have something like this outside the hash2 procedure, you'll need to run it only once:

DEFINE VARIABLE hRawField AS HANDLE NO-UNDO.

DEFINE TEMP-TABLE ttWithRaw NO-UNDO
    FIELD raw_field AS RAW .

ASSIGN hRawField = BUFFER ttWithRaw:BUFFER-FIELD("raw_field") .

CREATE ttWithRaw .

I haven't tried yet to optimize your code.

I am interested if we are doing something wrong. Is there a better solution for this problem? Is there less demanding way for creating hash out of each individual temp-table record?

A good portion of the runtime will be from creating the dynamic temp-table for each record - and I doubt that you'll need to create that temp-table for every record over and over again.

Maybe, by moving this part

   create temp-table ttDynamic.
   ttDynamic:add-fields-from(inp_hBuffer).
   ttDynamic:temp-table-prepare(inp_hBuffer:table).

out of the hash procedure, your code will improve as well. I doubt however, that it will be faster than the version based on the RAW-TRANSFER method.

This looks great! I will try to implement and test that when I have a time to. — vsobotka, Jul 15 '16 at 08:13

score 0 · Answer 2 · answered Sep 10 '16 at 08:00

(Posted on behalf of the OP).

Solution

I implemented a solution suggested by Mike. For our structure, I am not able to move helper content outside of procedure as suggested. After implementation to my real world problem and measuring the time of the entire process, with 2450 invocations I get 2054ms for prior solution as opposed to Mike's solutions which takes only 240ms. Which means I am not able in the real world implementation to get the full benefit, but even 8.5 times faster works very well for me. I will keep all that in mind and come back to it to optimize further when I have the time.

Here is how my procedure looks like

DEFINE INPUT  PARAMETER inp_hBuffer AS HANDLE    NO-UNDO .
DEFINE OUTPUT PARAMETER out_cHash   AS CHARACTER NO-UNDO .

DEFINE VARIABLE hRawField AS HANDLE NO-UNDO.
DEFINE TEMP-TABLE ttWithRaw NO-UNDO
    FIELD raw_field AS RAW .

ASSIGN hRawField = BUFFER ttWithRaw:BUFFER-FIELD("raw_field") .
CREATE ttWithRaw .

inp_hBuffer:RAW-TRANSFER (TRUE, hRawField) .
out_cHash = HEX-ENCODE (MD5-DIGEST (ttWithRaw.raw_field)).

Problems we encountered

We have finally got the time to further optimize it according to Mike's suggestion (temp-table definition and creation invoked only once) and it is now even faster. I do not really get why the temp-table definition, but mostly creation of a record, is that "slow".

Also keep in mind that raw-transfer method can't work with fields bigger than 32kB.

Hash a temp-table row

2 Answers2

Solution

Problems we encountered