Hi Hendrik,
I added the multiple column version due to a similar performance problem I
was facing four years ago.
I think you have two options:
1) patch the code that implements the multiple column version of ALTER
TABLE ADD COLUMN. It is in org.h2.command.ddl.AlterTableAlterColumn. If you
haven't done something like this before, I think you'll be surprised at how
straightforward it is, given that you have good knowledge of Java and SQL,
and some understanding of how parsers work.
2) manually perform the SQL statements similar to those that ALTER TABLE
ADD COLUMN would internally use. I forget the details, but effectively, it
internally executes a series of SQL statements similar to the following:
CREATE TABLE <temp_table_name> (...) AS SELECT ... FROM
<original_table_name>
for each index on original table {
CREATE INDEX <temp_idx_name> ON <temp_table_name>
}
DROP TABLE <original_table_name>
ALTER TABLE <temp_table_name> RENAME TO <original_table_name>
for each index on original table {
CREATE INDEX <temp_idx_name> ON <temp_table_name>
ALTER INDEX <temp_idx_name> RENAME TO <original_idx_name>
}
Plus some manual modifying any AUTO_NUMBER column.
On Friday, 9 September 2016 12:24:44 UTC+2, hendrik wrote:
>
> Hey there,
>
> I'd like to add multiple columns to a table. Caveat: I'd like to add them
> at a specified position and it should be quick.
>
> What I'm doing right now, is to add them one by one like this:
>
> ALTER TABLE Songs ADD custom1 varchar(255) AFTER someStartColumn;
> ALTER TABLE Songs ADD custom2 varchar(255) AFTER custom1;
> ALTER TABLE Songs ADD custom3 varchar(255) AFTER custom2;
> ...
>
> Unfortunately, due to a high number of indices, adding columns like this
> is very slow. Around 10s each.
> This is using H2 1.3.175 on an SSD with a table size of roughly 7000.
> Without any indices on the table, this only takes a little more than 1s
> each. I know, I could drop indices before altering the table and later
> re-create them.
>
> Indices aside, adding the columns in one statement is a lot faster:
>
> ALTER TABLE Songs ADD (custom1 varchar(255), custom2 varchar(255), custom3
> varchar(255), ...);
>
> This takes about as long as adding a single column.
> But unfortunately this syntax does not let me specify the position of the
> added columns.
>
> Or does it?
>
> Is there a better way to add multiple columns at specific positions?
>
> Thanks for your advice,
>
> -hendrik
>
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
