FWD » Core Development » Database

In FWD, copy-temp-table append mode is faster as it can be resolved using fast-copy (INSERT INTO SELECT FROM).

There are several customer applications that use replace mode (some that use it more often than append mode). I've also seen that a consistent number of such replace copies are over an empty target table. This means that the replace-mode should transform into an append-mode (as there is nothing to replace).

However, this is a bit tricky as 4GL actually replaces the already copied records! Fast copy however works only with matching signature, so it is guaranteed that the source table has the same unique constraints as the target table. Therefore, we can still do the conversion from replace-mode to append-mode. This constraint is something to think about when we will allows copies with non-matching signature.

The task here should be of testing this approach with large customer applications, profilers, etc.

Ștefan, I will put you up-to-date with the FWD implementation of copy-temp-table:

• no option: clear the destination and copy all from source
• append: don't clear the destination and append from source. If a unique index is violated, the copied row is skipped.
• replace: don't clear the destination and replace records with the same values on the primary unique index. Rows that don't replace anything are just appended
• loose-copy-mode: don't do an eager match of the schema; just copy what you can based on field names. Every field that exists in the source and target and have the same data type will be copied, the other will be set on the default value in the destination.

FWD logic
We do this in TemporaryBuffer.copyAllRows. In fact, we use this method for many other operations like input-output table parameters. The "classic" and slow way of doing the copy is by extracting all rows from the source and inserting them one-by-one in the destination. This was the initial implementation and works for any options (append / replace / loose-copy-mode etc.). However, there was some effort to optimize using "fast-copy", which makes use of INSERT INTO [...] SELECT FROM [...] SQL syntax. Of course, this doesn't work in all cases, but most of them are handled this way. To trigger a "fast-copy" you need:

• there is no option, so this is quite a simple copy
• append is used, so this is a bit complex, but still can work for schema matching tables, including unique indexes. You can do fast-copy only if the destination is "more restrictive" than the source.
• replace mode is not supported (mainly because it is more complex than a simple INSERT INTO SELECT FROM + the FWD cached DMO should be invalidated)
• loose-copy-mode is supported
• denormalized and normalized extents are supported
• before tables are supported

• extent fields (normalized or denormalized)
• before tables
• unique constraint validation
• note that triggers are not available for temp-tables, so not need to worry about that
• in replace-mode, we need to update the cached DMO which were replaced
• loose-copy-mode is clearly not trivial

This is why you will find quite a consistent code in copyAllRows - make sure you get a good grip on it before attempting to change.

Multiplex
Temp-tables which share the same name and schema in 4GL are grouped inside the same physical table in FWD. That is, if you have 3 tt tables across the converted application, H2 will create only one tt table with a _multiplex column that will discriminate between the legacy tt tables. There is no logic that requires queries cross-multiplex. Therefore, all queries / dml should have something like _multiplex = ? in their WHERE clause. Also, note that the first field in all temporary table indexes start with _multiplex out of obvious reasons.

You will also notice other fields that start with _. These are reserved columns used by FWD and do not exist in 4GL. Some of these should be changed after copy-temp-table.

Extents
For a while, FWD modeled the extent fields outside the "master" table, in tables named for instance tt__5 (for extents of size 5). tt__5 has 3 columns: peer (fk to master table), index (the extent index), value (the value at the index inside the extent). This technique is named "normalization". At copy-temp-table, you need to copy the records from source__5 to destination__5 as well, based on the peer column.

At some point, FWD supports de-normalized extent fields, so that the extent fields are embedded in the master row. Basically, you will expect f1, f2, f3, f4 and f5 fields to represent the values of a f extent 5 field. For this technique, it is easier to do the copy-temp-table as you don't have a separate tt__5 table to deal with.

Before
4GL allows the definition of before tables for temp-tables. Basically, they store a state before certain operations in the "master" table. On copy-temp-table, the before table should be copied as well. This is quite easy to manage, as the copy process is usually identical to before tables (including extents). The only complication appears when you need to remap the _peerRowid between the master table and before table, so that the newly copied records reference each other (master - before).

Created 7404a.

Committed to 7404a revision 14626.

Fixed an error and committed again on 7404a revision 14627.

I added the changes and committed again on 7404a revision 14628.

I added the first change, and I will start working/testing the second change.

Committed to 7404a revision 14629.

After letting simpleCopy be true when the explicit signatures matched, some custom small tests looked fine, but after running a large application the client crashed, and the error was thrown by TemporaryBuffer.copyAllRows, so I think we should try a different approach.

Please do some forensics on that issue you have with the customer application. Do some debugging - extract the tables that don't match with fast copy-helper. Report back why isn't this working. My best guess is that we can adapt or improve FastCopyHelper to work with that failing case too. At worse, we can rule it out from canFastCopy.

As your testing environment is a customer application, please try to recreate the issue in a separate test-case and post it here.

Stefan, please focus exclusively on this task. I am planning to merge 7404a in trunk asap; but the regression should be fixed first. Also, there may be some hidden performance drawbacks even after the regression is fixed, so there might be some extra work to be done after.

The problem was that the tables created dynamically had changed field names in SQL (field1, field2, ...) and the explicitSignature does not contain the names in SQL. I added a new signature, sqlSignature, that contains all the informations from explicitSignature + the fields names in SQL, and now simpleCopy will be true if the sqlSignature match. I tested with the customer application and worked fine.

Committed on 7404a, revision 14631.

I done some testing and everything works fine. I only notices a slight improvement:

• The usual case is to match the DMO interface, so the signature check is not always needed. Thus, retrieve the srcBuf.getDmoInfo().getSignature().getSqlSignature() only when simpleCopy is false, giving it "a second chance". If it is already true due to srcBuf.getDMOImplementationClass().equals(dstBuf.getDMOImplementationClass()), avoid the signature retrieval. These calls are light, but as long as we can do it better fast, we shall do it immediately.
• EDIT: this is not related to this task, but I saw that the signature is eagerly computed on DMO meta creation. I think it is better to initialize it lazy. This way, we avoid computing the quickSignature, explicitSignature and schemaSignature to all DMO - we shall compute it only when necessary. Stefan, please check how other members of DmoMeta are retrieved and implement that for DMO signature. Also consider computing each signature lazily inside DmoSignature (compute it only when needed and cache).

I tested with a large application and I got some interesting insights:

Of course, there are way more fast-copy operations happening after optimizations. I am not aware of the time spent out-side the fast-copy (so the one without optimization) - it may be interesting to find out the degree at which it was worth switching to fast-copy. Anyway, I still think there are some "hard nuts to crack": the scenarios that use before tables in fast-copy. These have times of 1/2 orders of magnitude higher, so we shall look into this topic next.

I think #7404 is already running out of its main course here (moving to fast-copy even if in replace mode). I will do my standard profiling routine and open a task on optimizing fast-copy of before-tables separately.

Stefan, please address the review of #7404 asap. If you get it in time, I may be able to catch it in my first profiling round - ideally.

I added the first change, where I retrieve sqlSignature only if simpleCopy is false, I will wait for another profiling.
Committed on 7404a, revision 14632

Created #7448 and #7449 to further optimize the temp-table (fast-)copy process.

The current performance degraded even more to +0.6%. This may be due to the slow fast-copy we do. I guess most of the copies actually move only some records (1/2) and thus the overhead of doing INSERT INTO SELECT FROM is higher. The statistic in #7404-19 is self-explanatory.

However, I think we moved the things in the right direction here. This way, we can optimize before table copies per-se and get a visible improvement. Before the changes here, optimizing replace-mode was mostly impossible as it required doing a one-by-one kind of job. Right now, we should just take care of how we actually implement fast-copy for before.

In this state, 7404a can't be merged. Stefan, please continue the work on #7489 and #7488 and dump the changes there in 7404a branch.

Stefan, please add these two sub-tasks (#7489 and #7488) to the H2 performance Wiki asap.

Rebased 7404a to latest trunk. 7404a is now at rev. 14651.

I added a break-down analysis of each operation done by fast-copy in #7404-19. Therefore, note that the cumulative times may mismatch. I quite lost the intermediate configuration I had for the middle part, so I skipped recomputing the break-down there. The format is: Time / Avg

While testing something for #7488, I noticed a regression on a test with copy-temp-table with REPLACE, introduced in 7404a, rev. 14650.
The test consists of a copy-temp-table in replace mode, the desire being to actually replace a record in the destination table.

define temp-table tt no-undo field f1 as int field f2 as int index idx1 is unique f1.
define temp-table tt2 no-undo field f1 as int field f2 as int index idx2 is unique f1.

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

create tt2. tt2.f1 = 2. tt2.f2 = 2.

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

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

If this won't be solved by the time I finish #7488 and there are no objections on this, I'll investigate this matter myself (since I'm working on something related). In addition to that, there have been added quite a lot of changes to 7404a and it could be difficult to integrate some changes.

Radu Apetrii wrote:

While testing something for #7488, I noticed a regression on a test with copy-temp-table with REPLACE.

The problem was that when the source record was copied into the destination record, its primary key (a.k.a. BaseRecord.id) was also copied. This lead to a mismatch of primary keys when attempting to store the record in the corresponding table. The commit that fixes this issue is on 7404a, rev. 14657.

I'll do a round of ETF tests.

In the mean time, please fix the history numbers - one per file, for the very first log message added.

Alexandru, do you have a round of performance testing with 7156b + 7404a? I ask because #7404-28 notes a performance degradation.

Fixed the history numbers. I tested today the performance and I had 7 runs: some with 0.9% - 1.5% perf. improvement and some runs with 0.8% - 0.9% perf. decrease. However, I get an average of -0.2% so I am quite content with that. Finally, the average is in the right direction.

Constantin Asofiei wrote:

I'll do a round of ETF tests.

ETF tests is OK. Actually, it fixes a test which previously failed.

You can merge to trunk now, after rebase.

Merged is postponed for tomorrow - please coordinate with Greg before queuing for merge.

OK.

If you are ready, you can go ahead with the merge now.

Starting the rebase; I will notify you when ready.

This can be closed.