FWD » Core Development » Database

Fast copy statements (INSERT INTO [...] SELECT FROM [...]) are quite often, especially as we have multiplex on tables and not separate tables for each DEFINE TEMP-TABLE.

However, in certain cases (when append option is used or normalized extent fields are used), an intermediate table is created named MASTER__PK__MAPPING. This allows mapping the old recid with the newly generated recid. Note that in append mode, only the records which don't violate any unique constraint are copied!

With MASTER__PK__MAPPING, the master table row copies and extent table row copies are done by joining this intermediate table. Similar semantics are for BEFORE__PK__MAPPING and LIST__INDEX__TABLE.

• MASTER__PK__MAPPING is created before this process and dropped after. This forces the prepared statement to be reparsed each time re-used. psCache has several such statements, but even if they are cached, a reparsing is done when the cache is hit (because we are talking about another MASTER__PK__MAPPING table object each time).
• Forcing out reparsing may increase performance as it avoids checking each time if a specific statement should be reparsed or not. For statements over tables with >100 columns, this checking is significant.
• We've tried in #7351 to make unique MASTER__PK__MAPPING by appending a uid at the end. This way, we can force out reparsing as database objects will always have different names. However, this really flooded the cache with INSERT INTO SELECT FROM statements, which are large by nature and can't be ever reused.

One option is to avoid caching such statements altogether, but it feels quite hacky and not optimal.

The best to identify which is the main trigger for such MASTER__PK__MAPPING: append mode or normalized extent fields, so we can focus on the right scenario. I have a feeling that normalized extent fields are causing trouble here, but I will provide an update from a large customer application here.

• Fast insert - generated by FastCopyHelper.getFastTableCopySql
• Extent insert - generated by FastCopyHelper.getExtentInsertSql
• Simple extent insert - generated by FastCopyHelper.getSimpleExtentInsertSql
• Shadow insert - generated by FastCopyHelper.getShadowCopySql

From the inserts mentioned above, only fast insert statements are the ones that don't use MASTER_PK_MAPPING/BEFORE_PK_MAPPING/LIST__INDEX__TABLE, so this means that ~24.25% from the total use this mapping. Other interesting findings are that ~81.75% don't use any mode, while ~18.25% use append mode and 0% use loose mode.

I have the following quite nice test-case. Please make sure the final answer is still 1 2. I am a bit surprised that this actually works now with fast-copy helper ... congrats to me :) Never-mind, this doesn't use fast-copy.

def temp-table tt field f1 as INTEGER 
                  field f2 as INTEGER
                  index idx1 f1 f2 asc.

def temp-table tt2 field f1 as INTEGER 
                  field f2 as INTEGER 
                  index idx1 is unique f1.

do transaction:                 
    create tt.
    tt.f1 = 1.
    tt.f2 = 2.

    create tt.
    tt.f1 = 1.
    tt.f2 = 3.
end.

temp-table tt2:handle:copy-temp-table(temp-table tt:handle, true).

for each tt2:
    message tt2.f1 tt2.f2.
end.

The idea is that the second record 1 3 is not inserted, as 1 2 already matches the unique constraint. I think the idea in #7389-4 is still OK, as the unique constraints checks (made as subselects) are not depending on what was inserted / what was there before insert.

Committed 7389a.rev14593. Added FastCopyHelper.safeTableCopyNoMapping that doesn't use mapping.
At the moment, the copy statement is executed multiple times and I am investigating this issue. It may be related to the multiplex or sorting, but I am not sure.

Please review.

Committed 7389a.rev14594. Fixed the problem mentioned in #7389-6. It was caused by missing records that were not copied in tables that use the same buffer but have different multiplex values. The recid clause should use the source buffer primary key and not the one of the destination buffer.

Alexandru Lungu wrote:

Please also consider srcRecId when ruling our pk mappings. If this parameter is set, you will need a mapping.

Done.

Now I am going to do the same test I did in #7389-3 and see how many times FastCopyHelper.safeTableCopyNoMapping produces the new statement.

The new insert method replaces almost all shadow inserts.

The changes are good. However, I think this is quite related to #7404. Danut, please move 7389a changes to 7404a - I will like to profile them together. Back there, all replace-mode are converted into append-mode if the target is empty. I have cases where there are "tons" of replace modes, so it will make sense to have append faster (as the replace will be converted into appends).

Alexandru Lungu wrote:

The changes are good. However, I think this is quite related to #7404. Danut, please move 7389a changes to 7404a - I will like to profile them together. ...

Committed 7404a/rev.14630. I moved 7389a/rev.14593 and 14594 to the specified revision.

Committed 7404a to trunk as rev. 14730.

This can be closed.