FWD » Core Development » Database

The COPY-TEMP-TABLE statement sometimes uses a "fast" approach if possible. It analyses table signatures (field types, indexes, etc.) and if they match, a INSERT INTO [...] SELECT FROM [...] statement is emitted. This works as a bulk select and insert.

The concern now is that such statement is very large, because it includes the field lists of the source and destination.

Please check if the wildcard syntax (tt.*) can be used instead generating the whole field list for source and destination. Test with large applications. The wildcard syntax is already used when "expanding alias" when generating query SQL.

• Fix the javadoc as there are many parameters which don't have the param javadoc tag. Some methods don't have the right multi-line javadoc format.
• Ensure code standard are respected. I see some inconsistencies like for (int i=0; i<from.size(); i++) with no proper spaces.
• Make sure that each "extra" table generated in the process (MASTER__PK__MAPPING, BEFORE__PK__MAPPING, LIST__INDEX__TABLE) have unique names across all fast-copy iterations. Therefore, I expect to have MASTER__PK__MAPPING_1, MASTER__PK__MAPPING_2, etc. I want to remove the recompiling of statements from H2 for good and the only tables which stand in the way are these. They are created and dropped, but share the same name. Even if cache may hit for these, the underlying prepare statement should be recompiled anyway as the physical table is brand anew.
• EDIT: also run testscases/uast/copy-temp-table. Do a master procedure to run all tests sequentially. The point is to run multiple copy-temp-table in the same session to check that the uid works properly.

For the second point, add a class member (lets say long uid) generated uniquely. Use a static AtomicLong sequence to ensure uniqueness of uid. Set the uid in constructor.
At this point, whenever you append the MASTER__PK__MAPPING, BEFORE__PK__MAPPING or LIST__INDEX__TABLE names, append the _{uid} identifier.

Danut, I think I have a better idea.

• INSERT INTO tt SELECT FROM tt2 JOIN MASTER__PK__MAPPING_1 is different from INSERT INTO tt SELECT FROM tt2 JOIN MASTER__PK__MAPPING_2, so these are two different prepared statements, which spoil the cache as both MASTER__PK__MAPPING_1 and MASTER__PK__MAPPING_2 are dropped right after.
• Even when we had INSERT INTO tt SELECT FROM tt2 JOIN MASTER__PK__MAPPING, this prepared statement was cached. Even if a cache hit happened, the statement was recompiled.

What if we don't drop MASTER__PK__MAPPING, but just clear it. There can't be two simultaneous fast copy-temp-table as the H2 sessions are independent from each other. Clearing the table should be enough so that the prepared statement can be re-executed without recompile. The only question I have is if clearing a table is slower than dropping it.

• compare on a dummy FWD-H2 the time difference between dropping a table and deleting all of its elements. Try on a big table (~100 columns) with several indexed (~5) and several rows (~1000)
• If results are OK, replace DROP tt with DELETE FROM tt. Make the creation with CREATE IF NOT EXISTS. Intensively test, even with multi-users.
• If everything is OK, I will add a neverRecompile flag and do some performance tests.

Alexandru Lungu wrote:

• compare on a dummy FWD-H2 the time difference between dropping a table and deleting all of its elements. Try on a big table (~100 columns) with several indexed (~5) and several rows (~1000)

Should #7351-5 still be considered in this case? And should I still work on unique names the extra generated tables if not?

Regarding the INSERT INTO [...] SELECT FROM [...] statement. Something like

insert into tt4 ( recid,_multiplex,_errorFlag,_originRowid,_datasourceRowid,_errorString,_peerRowid,_rowState,f1) direct sorted select next value for seq_3,?,_errorFlag,_originRowid,_datasourceRowid,_errorString,_peerRowid,_rowState,f1 from tt3 use index (idx_mpid__tt3__3) where _multiplex = ? order by _multiplex,recid {1: 2, 2: 1};

insert into tt4 direct sorted select next value for seq_3,?,tt3.* EXCEPT (tt3.recid, tt3._multiplex) from tt3 use index (idx_mpid__tt3__3) where _multiplex = ? order by _multiplex,recid {1: 2, 2: 1};

At the moment, I only modified FastCopyHelper.copyTable to generate such statement. I still need to test append and loose-mode and make the necessary modifications.

The INSERT INTO SELECT from changes are good to go in any form. Having shorter SQL is better all the way.

Should #7351-5 still be considered in this case? And should I still work on unique names the extra generated tables if not?

This is a really big gap between DROP and DELETE. I wonder if we can take this into account for our delete use-cases (which use multiplex). Anyway, continue with single uid. Even if I don't like having unusable cached prepared statements, this is something that already happens, so uid won't bring any harm.

However, we need to think of a better mean of caching these copy-temp-table operations. AFAIK, these intermediate tables are used only with normalized extent, APPEND or BEFORE modes - so we have still have the common-case operations properly cached.

Danut, I created 7351a and 7351a_h2. Please create a neverRecompile connection option (much like always recompile) in 7351a_h2 and test it. We will leave your implementation of dependency checks (in needsRecompile for persistent H2 cases. This flag should be used only for in-memory tables for now! Feel free to update H2Helper.setCommonInMemoryProperties to include neverRecompile.

Mark #7351 your top priority (both SQL compression and uid / neverRecompile features).

Committed 7351a/rev.14576. Reduced sql size of the INSERT INTO [...] SELECT FROM [...] statement by using wildcard when possible.

• Added AtomicLong uid value used to generate unique names for MASTER__PK__MAPPING, BEFORE__PK__MAPPING, LIST__INDEX__TABLE, the unique name is generated using assembleUidMapping method.
• Added boolean ALLOW_WILDCARD used to toggle between the old and new creation of insert based on select statements. Note that even if this value is set to true, it will not be used when the copy is in loose mode because of the possible mismatch in number of parameters or order of the columns.
• Created getSrcTempWildcard and getSrcMappedWildcard that builds the source table wildcard string. The columns recid and _multiplex are excepted from the wildcard selection.
• The insert statement can't use wildcard when in loose mode so executeCopy(), copyBeforeTable(), copyMasterTable(), copyTable() and safeCopyTable() receive an additional looseCopy parameter and it is used in copyTable and safeCopyTable methods.
• Added missing javadoc (@param, @throws PersistenceException), fixed formatting.

Please review.

Committed 7351a_h2/rev.18. Added NEVER_RECOMPILE property.
Committed 7351a/rev.14577. Fixed uid usage, replaced ALLOW_WILDCARD, added back .append(ReservedProperty._ROWSTATE.column) since I deleted it by mistake and added NEVER_RECOMPILE to H2 connection for single-user databases.

I can't test any large customer application due to logging issues. Once the latest version is released, I will be able to do so.

Danut, can you port this changes to 7026d and do the tests with it? 7026d isn't rebased yet to use the latest logging changes, so you may have a good run with it on a large customer application. I think is for the best to keep this change to 7026d anyway.
Thank you.

In fact, please commit everything there except the NEVER_RECOMPILE flag in FWD for temp tables. You will commit that after we build and release a new FWD-H2 version. We will bump the version to 7026d and afterwards you can add the connection option. Otherwise, 7026d will break.

Committed 7026d/rev.14576. Added the changes from 7351a rev. 14576 & 14577 without the NEVER_RECOMPILE property.

I had no problems testing a customer application for ~20 minutes with 7026d/rev.14576 and NEVER_RECOMPILE property.

Dănuț Filimon wrote:

I had no problems testing a customer application for ~20 minutes with 7026d/rev.14576 and NEVER_RECOMPILE property.

This is great. Having a stable 7026d right now is important.

Review of 7351a_h2/rev.18

I am OK with the changes.
Rebased and merged to FWD-H2 trunk as rev. 20. 7351a_h2 was archived.

Committed 7026d/rev.14580. Added a flag that enables uid, it's usage is now disabled by default.

After doing a memory test on psCache and analyzing the heap dump obtained with VisualVM with and without modifications from the current issue, I remarked that there are a lot more statements stored that use intermediate table with uid, which increases the memory used by psCache (~2-3x). That's why I added a flag to enable the uid in the future. The NEVER_RECOMPILE flag will also remain, but will also not be added to the H2 connection url yet.

Danut, note that 7026d was rebased with latest trunk. Please update.

There are two INSERT INTO [...] SELECT FROM [...] and eight select statements.

Note that even if I mention #7351, the tests were done with 7026d.