FWD » Core Development » Database

It seems to me that when processing certain temp-table copying use cases, there may be an opportunity for optimization. We currently use a multiplex id to separate out records for different instances. We know there are cases where we overwrite the contents of one table from another while then deleting the source table. For example, an output temp-table parameter will do this "overwrite and delete source".

I was wondering if we already handle this in an optimized fashion. For example, we could just change the multiplex id of the caller's temp-table to match the data that is already there.

In other cases, (e.g. an input-output parameter case) where it isn't a full overwrite, perhaps there is a possible optimization to just modify the source table's multiplex id to match the target table's id without otherwise copying/inserting records.

If we've already done such things and/or my ideas are naive, it is OK to say so. I'm coming at this without having checked the implementation.

• temp-table parameter to non-persistent external procedure (run "side.p"(input-output table tt).)
• temp-table parameter to persistent external procedure (run "side.p" persistent set hProc (input-output table tt).)
• temp-table parameter to persistent internal procedure (run "intern" in hProc (input-output table tt).)
• temp-table parameter to internal procedure (run "intern" input-output table tt).)
• check APPEND specification (run "side.p"(input-output table tt append).)
• check BY-REFERENCE specification (run "side.p"(input-output table tt by-reference).)
• check BIND specification (run "side.p"(input-output table tt bind).)
• the scenarios above with output instead of input-output
• at the end, check that COPY-TEMP-TABLE and EMPTY-TEMP-TABLE continue working as normally (nothing is deleted/appended unexpectedly)

I have created tests with the mentioned specifications and all passed (same results were obtained from the executed statements when comparing 4GL results of the same test). I am working on expanding the test cases to make sure that the correct statements are executed and that the multiplex values of the temp-tables are flagged correctly (empty / not empty).

• passing a table as an INPUT-OUTPUT/OUTPUT parameter between multiple persistent / non-persistent internal / external procedure, this includes the APPEND, BY-REFERENCE, BIND, EMPTY TEMP-TABLE, COPY-TEMP-TABLE tests.
• using APPEND BY-REFERENCE
• using both NO-UNDO (currently) and UNDO temp-tables
• using TABLE-HANDLE

Ovidiu: Please review.

Any news about the review?

Dănuț,

I am sorry for the delay, somehow the notification emails got lost at the bottom of the list and I only saw it now.

The changes are not extensive but I am not sure I fully understand all the aspects of it. For example, at the end of TemporaryBuffer.associate(), for the output parameters the OutputTableCopier is constructed with the additional parameter, shouldInsert. Particularly, you force all records from after tables to be inserted. I do not say this is incorrect but a bit surprising: I do not expect those to be different from SIMPLE buffers (that is those which do not have a pair BEFORE buffer, even if they are members of a dataset).

In the new method performOutputCopy(TableParameter outputParameter, boolean shouldInsert), I see that, based on the above parameter, the records are removed. I think the second removeRecords which does not enforce the records to be deleted could be call on else branch, since the matching records are already 'moved' to targetMultiplex table.

You mentioned that you "discovered a small issue regarding BY-REFERENCE", but I did not see anything to relate the changes to such parameters. Have you done tests without this option?

I think the update is a bit risky and I recommend putting it to test with a project which uses table parameters and especially sent BY-REFERENCE.

Eric,
which would be the most appropriate one? The old testsets from devsrv01?

Originally, my intention was to delete the records from a table based on the targetMultiplex when the outputParameter wasn't in append mode and then update the records by replacing the multiplex with targetMultiplex. I retest the patch and from a total of 108 tests that I used when working on this issue, a total of 12 failed. I looked a bit into the problem and it seems that the second removeRecords call returns because the table is empty which should not be.

Ovidiu Maxiniuc wrote:

You mentioned that you "discovered a small issue regarding BY-REFERENCE", but I did not see anything to relate the changes to such parameters. Have you done tests without this option?

I've done tests without this option and found no problems at the time. Only 8 tests from my test set turned out to have an issue with BY-REFERENCE. Please note that my changes did not cause this problem, I only happened to find it by chance while working on the current issue. I created #6997 and explained the issue with BY-REFERENCE, please take a look at it.

I think the update is a bit risky and I recommend putting it to test with a project which uses table parameters and especially sent BY-REFERENCE.

I agree. Since the patch is not working anymore, I will continue working on it.

I've made a mistake and used the wrong patch when testing previously. The second removeRecords call didn't execute because the flags were set incorrectly in the patch that I had. I can confirm that the attached patch (6714.patch) still works correctly as it used to. We can proceed with testing the changes.

I've made a comparison between 3821c/rev.14467 and the patched version from #6714-5 using different patched versions of h2 (revision 6 - current, 7 and 8). I changed my tests to only check their execution time (this included create statements) and continued with a single test. The test was modified further and since measuring sql statements time was introduced recently, it was possible to filter out the create statements. I wrote a small script to calculate the time executed by each statement type (update, delete, insert) from the data collected.

• temp-table parameter to non-persistent external procedure (using INPUT-OUTPUT) which is then passed to another non-persistent external procedure.
• 10000 initial create statement in each file (start.p and external procedures)
• 100 iterations of the test

Danut, can you add a counter JMX for your modification? I think we can benefit from finding out how many times this scenario is reached. Consider re-posting the patch over 6129b integrating the JMX.

• of course, this works only if the same SQL table is at both the src and dest
• Alexandru notes in #7026-12 that:

However, it is restricted only to the cases where the output parameter is part of a non-persistent external procedure, which is going to empty the multiplex (if there are any concerns on this, we can continue discussion on #6714). Anyways, I used the output table copier JMX and the POC tests trigger less than 10 such operations that are resolved in less than 1ms in total. Therefore, I won't go ahead with profiling and integrating it with 6129b effort.

This is a problem: you can't assume that a non-persistent procedure exists only once on the stack. You can have multiple internal procedures/functions for the same non-persistent external program active on the stack, at the same time.

Constantin Asofiei wrote:

Some concerns on moving the source records to the destination table just by updating the multiplex ID to the destination table:

• of course, this works only if the same SQL table is at both the src and dest
• Alexandru notes in #7026-12 that:

However, it is restricted only to the cases where the output parameter is part of a non-persistent external procedure, which is going to empty the multiplex (if there are any concerns on this, we can continue discussion on #6714). Anyways, I used the output table copier JMX and the POC tests trigger less than 10 such operations that are resolved in less than 1ms in total. Therefore, I won't go ahead with profiling and integrating it with 6129b effort.

This is a problem: you can't assume that a non-persistent procedure exists only once on the stack. You can have multiple internal procedures/functions for the same non-persistent external program active on the stack, at the same time.

AFAIK, only the OutputTableCopier associated with an external block of a non-persistent program triggers an "update-multiplex" type of copy. How does internal procedures/functions affect this process? I suspect that once the external block finishes, there can't be any internal procedures/functions of that exact program instance still on the stack.

Can this be closed? Is there anything to be reviewed and used from this task, or has it been absorbed/superseded by similar tasks?

Eric Faulhaber wrote:

Can this be closed? Is there anything to be reviewed and used from this task, or has it been absorbed/superseded by similar tasks?

The changes were tested but it handled a very particular case. Over the time, multiple changes were made to H2 and it is possible that the changes might not be useful. I'll retest the changes using POC and return with an appropriate answer.

• The usage of the ProcedureManager where the OutputTableCopier is instantiated.
• The multiplex argument which is now an argument for a prepared statement and not added as it is.

I ran the POC performance tests and obtained an improvement but after introducing the JMXs to get a few counters and timers, I found out that OutputTableCopier is not used at all so the POC results are invalid.

There were a total of 5553 calls to performOutputCopy where 5510 were with the default logic, and only 43 were using delete-update (some calls were already processed and short-circuited: 148). Only 0.774% of the total performOutputCopy call used this optimization, while the average call time can't be used because of the low number of calls. In my opinion, unless we can expand the area where delete-update is used, we should not proceed with the changes and close/reject this issue.