I have a table with 12 million rows that isn't very complex, though the
table has a primary compound index of three fields. This is a
representation of an OpenEdge DB that we have in production that does seem
to perform quite well for the same sort of query.
create table if not exists widget (
container_id integer not null default 0,
x integer default 0,
y integer default 0,
z integer default 0,
num integer default 0,
quantity integer default 0,
constraint pk_pocket_idx_prime primary key (container_id, x, y, z)
);
create index if not exists "ix_widget_1" on pocket (container_id, num, z);
Any query that doesn't access the fields will perform quickly, such as a
count(*).
Ultimately I'm joining through using ix_widget_1 and the query being
performed will approximately return 3 million rows. using count(*) the
query performs pretty quickly, but as soon as I access any of the data
items the query takes around 20 minutes to return.
Is there anything I can do to increase the performance of this table?
Basically the query I'm trying to perform looks as follows.
select br.ACCOUNT_NUMBER
r.CODE,
r.DESCRIPTION,
p.NUM,
p.Z
from ACCOUNT br
left join ACCOUNT_PLAN rc on rc.ACCOUNT_PLAN_ID = br.TEMP_PLAN_ID
left join ACCOUNT_PLAN rf on rf.ACCOUNT_PLAN_ID = br.PRIME_PLAN_ID
join CONTAINER r on r.ACCOUNT_PLAN_ID = nvl(rc.ACCOUNT_PLAN_ID,
rf.ACCOUNT_PLAN_ID)
join WIDGET p on p.CONTAINER_ID = r.CONTAINER_ID
where br.DIVISION = 2
and (rc.ACCOUNT_PLAN_ID is not null or rf.ACCOUNT_PLAN_ID is not null)
order by br.ACCOUNT_NUMBER,
r.CODE,
p.NUM,
p.Z
Executing the query with all tables except for WIDGET return without issue,
it's just when I add WIDGET I see the performance killed.
NOTE: I have renamed objects and fields for the purpose of this post.
--
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 https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
