Transactions during READ-JSON for NO-UNDO temp-table.

Posted by dbeavon on 10-Oct-2019 16:41

I have a question about using the READ-JSON on a temp table handle.  I am seeing a ton of transaction activity during READ-JSON on a temp table.

      TEMP-TABLE LL_pro_item_query_output:READ-JSON ("LONGCHAR", v_Long, "APPEND").


... generates logging ...

[19/10/10@12:28:04.514-0400] P-014480 T-065852 3 4GL 4GLTRANS       BEGIN TRANS 2450 [FillLocationForQuery gen.Inventory.PerpetualInventorySession @ 347]
[19/10/10@12:28:04.514-0400] P-014480 T-065852 3 4GL 4GLTRANS       END TRANS 2450 [FillLocationForQuery gen.Inventory.PerpetualInventorySession @ 347]
[19/10/10@12:28:04.514-0400] P-014480 T-065852 3 4GL 4GLTRANS       BEGIN TRANS 2451 [FillLocationForQuery gen.Inventory.PerpetualInventorySession @ 347]
[19/10/10@12:28:04.514-0400] P-014480 T-065852 3 4GL 4GLTRANS       END TRANS 2451 [FillLocationForQuery gen.Inventory.PerpetualInventorySession @ 347]
[19/10/10@12:28:04.514-0400] P-014480 T-065852 3 4GL 4GLTRANS       BEGIN TRANS 2452 [FillLocationForQuery gen.Inventory.PerpetualInventorySession @ 347]
[19/10/10@12:28:04.514-0400] P-014480 T-065852 3 4GL 4GLTRANS       END TRANS 2452 [FillLocationForQuery gen.Inventory.PerpetualInventorySession @ 347]
[19/10/10@12:28:04.514-0400] P-014480 T-065852 3 4GL 4GLTRANS       BEGIN TRANS 2453 [FillLocationForQuery gen.Inventory.PerpetualInventorySession @ 347]
[19/10/10@12:28:04.514-0400] P-014480 T-065852 3 4GL 4GLTRANS       END TRANS 2453 [FillLocationForQuery gen.Inventory.PerpetualInventorySession @ 347]
[19/10/10@12:28:04.514-0400] P-014480 T-065852 3 4GL 4GLTRANS       BEGIN TRANS 2454 [FillLocationForQuery gen.Inventory.PerpetualInventorySession @ 347]
[19/10/10@12:28:04.514-0400] P-014480 T-065852 3 4GL 4GLTRANS       END TRANS 2454 [FillLocationForQuery gen.Inventory.PerpetualInventorySession @ 347]
[19/10/10@12:28:04.514-0400] P-014480 T-065852 3 4GL 4GLTRANS       BEGIN TRANS 2455 [FillLocationForQuery gen.Inventory.PerpetualInventorySession @ 347]
[19/10/10@12:28:04.514-0400] P-014480 T-065852 3 4GL 4GLTRANS       END TRANS 2455 [FillLocationForQuery gen.Inventory.PerpetualInventorySession @ 347]
[19/10/10@12:28:04.514-0400] P-014480 T-065852 3 4GL 4GLTRANS       BEGIN TRANS 2456 [FillLocationForQuery gen.Inventory.PerpetualInventorySession @ 347]
[19/10/10@12:28:04.514-0400] P-014480 T-065852 3 4GL 4GLTRANS       END TRANS 2456 [FillLocationForQuery gen.Inventory.PerpetualInventorySession @ 347]
[19/10/10@12:28:04.514-0400] P-014480 T-065852 3 4GL 4GLTRANS       BEGIN TRANS 2457 [FillLocationForQuery gen.Inventory.PerpetualInventorySession @ 347]
[19/10/10@12:28:04.514-0400] P-014480 T-065852 3 4GL 4GLTRANS       END TRANS 2457 [FillLocationForQuery gen.Inventory.PerpetualInventorySession @ 347]
[19/10/10@12:28:04.514-0400] P-014480 T-065852 3 4GL 4GLTRANS       BEGIN TRANS 2458 [FillLocationForQuery gen.Inventory.PerpetualInventorySession @ 347]
[19/10/10@12:28:04.514-0400] P-014480 T-065852 3 4GL 4GLTRANS       END TRANS 2458 [FillLocationForQuery gen.Inventory.PerpetualInventorySession @ 347]

.... many thousands of times ...

The temp-table is NO-UNDO.  See below.

DEFINE TEMP-TABLE LL_pro_item_query_output NO-UNDO 

   /* Access to pro_item itself */
   FIELD  pro_item_rowid as CHARACTER
   FIELD  pro_item_rowid_hex as CHARACTER
   
   /* Access to pro_costing */
   FIELD  pro_costing_rowid as CHARACTER
   FIELD  pro_costing_rowid_hex as CHARACTER

   /* Access to pro_duct */  
   FIELD  pro_duct_rowid as CHARACTER
   FIELD  pro_duct_rowid_hex as CHARACTER

   /* Access to lum_duct  */
   FIELD  lum_duct_rowid as CHARACTER
   FIELD  lum_duct_rowid_hex as CHARACTER

   /* Set to true after pro_item is retrieved into dataset */
   FIELD  pro_item_record_shadowing AS LOGICAL INIT FALSE  
   
   
   /* pro_item identification values */   
   FIELD branch AS CHARACTER   
   FIELD loc AS CHARACTER 
   FIELD pro-type AS CHARACTER 
   FIELD product AS CHARACTER.

There are no outer transaction scopes in my example.  The READ-JSON seems like it wants a transaction scope or it will otherwise create one itself!

If I create records from ABL the old-fashioned way, I do not see any BEGIN TRANS or END TRANS in my 4GLTRANS logging output.  Eg. the following doesn't seem to generate any 4GLTRANS activity of any kind...

                  CREATE LL_pro_item_query_output.
                  LL_pro_item_query_output.loc = "aaa".
                  
                  CREATE LL_pro_item_query_output.
                  LL_pro_item_query_output.loc = "xxx".
                  
                  CREATE LL_pro_item_query_output.
                  LL_pro_item_query_output.loc = "yyy".

For now I plan on wrapping the READ-JSON() with an explicit transaction to avoid thousands of mini-transactions from being started and stopped.  It seems odd, and any developer who reads this code afterwards may wonder what I'm doing.

Is this proper behavior for READ-JSON?  Is there a better way of influencing the behavior of transactions while reading data into the NO-UNDO temp-table?

Thanks in advance.

Posted by Fernando Souza on 10-Oct-2019 18:36

READ-JSON uses the trans/sub-trans to backout a record that fails to be inserted/updated in the temp-table. There is nothing you can do currently to avoid that behavior. That would be an enhancement request.

All Replies

Posted by dbeavon on 10-Oct-2019 17:02

I wrapped the READ-JSON() with an explicit transaction and it changed the behavior slightly.  The difference is that now the 4GLTRANS output says "BEGIN SUB-TRANS" and "END SUB-TRANS" for all these records.  The performance improvement is not that great (5 to 10 %).

Ideally there would be a version of READ-JSON() that worked the same way as creating the TT records from ABL statements.  

Please let me know if anyone has a way to avoid the performance overhead of transactions during READ-JSON().  I haven't specifically tested but I don't think an "IMPORT" statement for the same TT would have this overhead.  Here is the KB:

knowledgebase.progress.com/.../P4129

Posted by Brian K. Maher on 10-Oct-2019 17:04

Hi David,
 
Please open a case with support so we can log a bug or enhancement request (whichever it ends up being).
 
Thanks, Brian

Posted by dbeavon on 10-Oct-2019 17:39

No problem.  I'm guessing it will not be a bug (or at least not one that would be fixed in OE 11.7).  Its probably not new behavior either, and has always worked this way.  I just happened to notice the issue for the first time, and wondered if anyone else was already aware of it.  

Other companies probably use the READ-JSON method a lot more than we do.  I was hoping someone knew the answer off the top of their heads.

Posted by Brian K. Maher on 10-Oct-2019 17:44

I think we should give an option to the command to give folks the choice of how they want it done.

Posted by Fernando Souza on 10-Oct-2019 18:36

READ-JSON uses the trans/sub-trans to backout a record that fails to be inserted/updated in the temp-table. There is nothing you can do currently to avoid that behavior. That would be an enhancement request.

Posted by dbeavon on 10-Oct-2019 21:55

OK thanks for the info.  I guess I was thinking that the NO-UNDO property of the temp-table might help avoid the performance overhead (by changing the behavior of READ-JSON).  In certain situations we use NO-UNDO temp-tables for the sake of performance .

I will wrap READ-JSON in a single transaction to avoid the large number of mini-transactions.  In any case, if READ-JSON ever failed I wouldn't really need partial results.  

This thread is closed