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.
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.
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:
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.
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.
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.