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].
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.
