|
Version 17 (build 2000) introduced the ability to load tables in parallel and create indexes in parallel (by using the new BEGIN PARALLEL WORK statement). |
|
Creating foreign keys means using a CREATE TABLE or ALTER TABLE statement. As you are dealing with load table, I assume the FKs are meant for tables that are immediately filled with existing data (like when doing a rebuild or importing external data). In such cases, you usually will declare foreign keys AFTER the data has been loaded for performance reasons, as has beend discussed here, so you are going to use ALTER TABLE to add foreign keys. In my understanding, several ALTER TABLE statements are serialized by design, because the docs tell:
If my understanding is correct, your second question will be moot as you won't be able to use two ALTER TABLE statements in parallel. However, as Ivan has explained in the mentioned FAQ, the RI check when declaring a foreign key is already using parallelized queries, so even if you probably cannot build foreign keys in parallel, the individual keys seem to be created using parallelism. Just to add a wild guess: SQL Anywhere automatically creates (logical) indexes for any primary or foreign key. As several logical indexes can be based on the same physical index (as long as the index column(s), column order and ordering are identical), you might use BEGIN PARALLEL WORK to create fitting physical indexes for your foreign keys in parallel, so ALTER TABLE does not have to build a physical index itself but can rely on the already built ones. And you could drop those "help indexes" lateron... Note, I have not tried that suggestion, and I'cant tell whether the overall performance will increase.
(27 Jun '22, 05:40)
Volker Barth
Replies hidden
Sorry for the delay in reply. "you usually will declare foreign keys AFTER the data has been loaded for performance reasons..." "A checkpoint is carried out at the beginning of the ALTER TABLE operation" "the individual keys seem to be created using parallelism" (*) ALTER TABLE "dba"."treat"
(04 Jul '22, 07:33)
Ilia63
Comment Text Removed
So have you tried to build the physical indexes in parallel that would be used by the logical FOREIGN KEY indexes? (As stated, I have never tested this myself, simply because I don't face such performance problems...)
(04 Jul '22, 08:10)
Volker Barth
That's a misunderstanding: My point was not that a checkpoint would be used INSTEAD OF locks but that ALTER TABLE does an initial checkpoint and prevents other statements to do a checkpoint until it finishes, so it's even more "exclusive" that an exclusive lock on one or two tables...
FWIW, have you used sa_conn_info() to notice intra-query parallelism?
(04 Jul '22, 08:12)
Volker Barth
Thanks,
(04 Jul '22, 08:16)
Ilia63
Again, AFAIK that's a misunderstanding: A checkpoint does not use locks. See this FAQ for the steps during a checkpoint. In my understanding, the locks will be due to the ALTER TABLE statement itself preventing other transactions from using using the "about to be altered table" (probably including the referenced tables)... - and additionally the ALTER TBALE itself will be blocked as long as other transactions have a shared schema lock on that table because they still access the table...
(04 Jul '22, 10:43)
Volker Barth
|
