Hi, Could you please check whether the "select" statement is slower, or the "insert" statement?
And would it be possible to check whether "create table newTable as select ..." is fast? I know this is without the indexes. To find out what the problem could be. And last, could you get a few (about 10) full thread dumps while the insert is running? To do that, use "jps -l" to get the process id, and then "jstack -l <pid> >> threadDumps.txt" about 10 times in a row. Regards, Thomas On Wednesday, August 27, 2014, Isaac Zhu <[email protected]> wrote: > Hi, > Our application is using H2 as the core database in embed mode. And we > have a view with about 100,000 rows. To accelerate the querying, we create > a table that has the same column with the view and create indexes on some > columns. > We update the table with the data in the view periodically, in version > 1.3.176, the following SQL statements: > *truncate table regression_gut_info_all*; > *insert into *regression_gut_info_all *( select * from * > regression_gut_info_view *)* > Can be finished around 74606 ms. > > While trying the latest 1.4.181, the same statement, takes more than 10 > minutes. > > Here is our table and view definition:(we use olderStyleOuterJoin=true) > > create or replace view regression_gut_info_view as > select > c.product_number, > b.release_number, > a.regression_id, > a.test_name, > b.platform, > a.owner, > g.manager, > b.report_location, > a.last_pass_date, > a.last_pass_id, > a.last_reg_id, > a.test_id, > a.fail_type, > a.sub_time, > b.tree_name, > b.client_name, > d.product_name, > c.display_name, > b.change_list, > b.uuid2, > a.nav_failure_id, > b.op_purpose, > decode( f.primary_platform is not null, 0, false, 1, decode( concat ( > ',', f.primary_platform, ',' ) like concat('%,', b.platform , ',%'), 0, > false, 1, true ) ) as is_primary_platform, > a.file_owner, > a.last_ownership_change_time, > b.p4_branch > from reg_failed a, regression_data b, release_list c, product_list d, > pe_branches f, employeea g > where a.regression_id = b.regression_id and b.release_number = > c.release_number > and c.product_number = d.product_number and a.fail_type in > ('fail', 'fatal') and b.p4_branch = f.branch_depot_path(+) and a.owner = > g.userid(+); > > > create table if not exists regression_gut_info_all ( > product_number int, > release_number varchar(100), > regression_id bigint, > test_name varchar(512), > platform varchar(32), > owner varchar(256), > manager varchar(256), > report_location varchar(5000), > last_pass_date timestamp, > last_pass_id bigint, > last_reg_id bigint, > test_id int, > fail_type varchar(32), > sub_time timestamp, > tree_name varchar(80), > client_name varchar(64), > product_name varchar(64), > display_name varchar(32), > change_list bigint, > uuid2 bigint, > nav_failure_id bigint, > op_purpose varchar_ignorecase(64), > is_primary_platform boolean, > file_owner varchar(256), > last_ownership_change_time timestamp, > p4_branch varchar(512) > ); > > create index if not exists regression_gut_info_index1 on > regression_gut_info_all ( release_number ); > create index if not exists regression_gut_info_index4 on > regression_gut_info_all ( regression_id ); > create index if not exists regression_gut_info_index5 on > regression_gut_info_all ( uuid2 ); > create index if not exists regression_gut_info_index6 on > regression_gut_info_all ( platform ); > create index if not exists regression_gut_info_index7 on > regression_gut_info_all ( product_number ); > create index if not exists regression_gut_info_index8 on > regression_gut_info_all ( op_purpose ); > create index if not exists regression_gut_info_index9 on > regression_gut_info_all ( change_list ); > create index if not exists regression_gut_info_index10 on > regression_gut_info_all ( owner ); > create index if not exists regression_gut_info_index11 on > regression_gut_info_all ( last_pass_id ); > create index if not exists regression_gut_info_index12 on > regression_gut_info_all ( last_reg_id ); > create index if not exists regression_gut_info_index13 on > regression_gut_info_all ( last_pass_date ); > create index if not exists regression_gut_info_index14 on > regression_gut_info_all ( fail_type ); > create index if not exists regression_gut_info_index15 on > regression_gut_info_all ( test_name ); > create index if not exists regression_gut_info_index16 on > regression_gut_info_all ( manager ); > create index if not exists regression_gut_info_max_uuid on > regression_gut_info_all ( uuid2, release_number, platform, op_purpose ); > > Regards, > Isaac > > -- > 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 [email protected] > <javascript:_e(%7B%7D,'cvml','h2-database%[email protected]');> > . > To post to this group, send email to [email protected] > <javascript:_e(%7B%7D,'cvml','[email protected]');>. > Visit this group at http://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/d/optout. > -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
