* Do you need 3 million rows returned? Or, say, just the first 10 or 100 or 1000 rows? Then use SELECT TOP 100 at the beginning of your query. This should give you an enormous gain in query speed. * Can you somehow get rid of the OR in your where clause? The OR is going to cause H2 to scan every row with division = 2.
On Sunday, 24 April 2016 18:39:15 UTC+2, Brett Ryan wrote: > > 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 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.
