Hi, Could you run SCRIPT NODATA and also post the result so that I can build a reproducible test case.
Regards, Thomas On Tue, Mar 3, 2009 at 10:45 PM, PatE <[email protected]> wrote: > > Hi, > Yes, we run ANALYZE each time the project file is opened. > > The query plans for the two database versions are as follows. > > The query: > SELECT * > FROM eventheader eh, (select * from Indication where indicationtype IN > (3,4,5,6,7,8) and not deleted) i,eventdetail ed,indicationtype > it,indicationsubtype ist > WHERE eh.sampleindex=ed.sampleindex AND ed.sampleindex=i.sampleindex > AND ed.eventtype=4 AND i.indicationtype=it.id AND > i.indicationsubtype=ist.id > > Plan on 1.1.107: > > SELECT * > FROM PUBLIC.INDICATIONSUBTYPE IST /* > PUBLIC.INDICATIONSUBTYPE_TABLE_SCAN */ > INNER JOIN (SELECT * > FROM PUBLIC.INDICATION /* PUBLIC.INDICATION_TABLE_SCAN */ /* WHERE > (INDICATIONTYPE IN(3, 4, 5, 6, 7, 8)) AND (DELETED = FALSE) */ > INNER JOIN TABLE_DISTINCT(_222 INTEGER=(3, 4, 5, 6, 7, 8)) _223 /* > PUBLIC."": _222 = INDICATIONTYPE */ ON 1=1 > WHERE (DELETED = FALSE) AND ((INDICATIONTYPE IN(3, 4, 5, 6, 7, 8)) AND > (INDICATIONTYPE = _223._222))) I /* SELECT * > FROM PUBLIC.INDICATION /++ PUBLIC.INDEX_F951: INDICATIONSUBTYPE = ?1 + > +/ /++ WHERE (INDICATIONTYPE IN(3, 4, 5, 6, 7, 8)) AND > ((INDICATION.INDICATIONSUBTYPE = ?1) AND (DELETED = FALSE)) ++/ > INNER JOIN TABLE_DISTINCT(_284 INTEGER=(3, 4, 5, 6, 7, 8)) _285 /++ > PUBLIC."": _284 = INDICATIONTYPE ++/ ON 1=1 /++ WHERE INDICATIONTYPE = > _285._284 ++/ > INNER JOIN TABLE_DISTINCT(_286 INTEGER=(3, 4, 5, 6, 7, 8)) _287 /++ > PUBLIC."": _286 = INDICATIONTYPE ++/ ON 1=1 > WHERE (INDICATION.INDICATIONSUBTYPE = ?1) AND ((DELETED = FALSE) AND > ((INDICATIONTYPE = _285._284) AND ((INDICATIONTYPE IN(3, 4, 5, 6, 7, > 8)) AND (INDICATIONTYPE = _287._286)))): INDICATIONSUBTYPE = IST.ID */ > ON 1=1 /* WHERE I.INDICATIONSUBTYPE = IST.ID */ > INNER JOIN PUBLIC.EVENTHEADER EH /* PUBLIC.INDEX_A: SAMPLEINDEX = > I.SAMPLEINDEX */ ON 1=1 /* WHERE EH.SAMPLEINDEX = I.SAMPLEINDEX */ > INNER JOIN PUBLIC.EVENTDETAIL ED /* PUBLIC.INDEX_A2: EVENTTYPE = 4 AND > SAMPLEINDEX = EH.SAMPLEINDEX AND SAMPLEINDEX = I.SAMPLEINDEX */ ON > 1=1 /* WHERE (ED.SAMPLEINDEX = I.SAMPLEINDEX) AND ((ED.EVENTTYPE = 4) > AND (EH.SAMPLEINDEX = ED.SAMPLEINDEX)) */ > INNER JOIN PUBLIC.INDICATIONTYPE IT /* PUBLIC.PRIMARY_KEY_D: ID = > I.INDICATIONTYPE */ ON 1=1 > WHERE (I.INDICATIONSUBTYPE = IST.ID) AND ((I.INDICATIONTYPE = IT.ID) > AND ((ED.EVENTTYPE = 4) AND (((EH.SAMPLEINDEX = ED.SAMPLEINDEX) AND > (ED.SAMPLEINDEX = I.SAMPLEINDEX)) AND (EH.SAMPLEINDEX = > I.SAMPLEINDEX)))) > (1 row, 157 ms) > > > Plan on 1.0.78: > > SELECT * > FROM PUBLIC.INDICATIONSUBTYPE IST /* > PUBLIC.INDICATIONSUBTYPE_TABLE_SCAN */ > INNER JOIN (SELECT * > FROM PUBLIC.INDICATION /* PUBLIC.INDICATION_TABLE_SCAN */ > WHERE (INDICATIONTYPE IN(3, 4, 5, 6, 7, 8)) AND (DELETED = FALSE)) I / > * SELECT * > FROM PUBLIC.INDICATION /++ PUBLIC.INDEX_F951: INDICATIONSUBTYPE = ?1 + > +/ > WHERE (INDICATION.INDICATIONSUBTYPE = ?1) AND ((INDICATIONTYPE IN(3, > 4, 5, 6, 7, 8)) AND (DELETED = FALSE)): INDICATIONSUBTYPE = IST.ID */ > ON 1=1 /* WHERE I.INDICATIONSUBTYPE = IST.ID */ > INNER JOIN PUBLIC.EVENTHEADER EH /* PUBLIC.INDEX_A: SAMPLEINDEX = > I.SAMPLEINDEX */ ON 1=1 /* WHERE EH.SAMPLEINDEX = I.SAMPLEINDEX */ > INNER JOIN PUBLIC.EVENTDETAIL ED /* PUBLIC.INDEX_A2: EVENTTYPE = 4 AND > SAMPLEINDEX = EH.SAMPLEINDEX AND SAMPLEINDEX = I.SAMPLEINDEX */ ON > 1=1 /* WHERE (ED.SAMPLEINDEX = I.SAMPLEINDEX) AND ((ED.EVENTTYPE = 4) > AND (EH.SAMPLEINDEX = ED.SAMPLEINDEX)) */ > INNER JOIN PUBLIC.INDICATIONTYPE IT /* PUBLIC.PRIMARY_KEY_D: ID = > I.INDICATIONTYPE */ ON 1=1 > WHERE (I.INDICATIONSUBTYPE = IST.ID) AND ((I.INDICATIONTYPE = IT.ID) > AND ((ED.EVENTTYPE = 4) AND (((EH.SAMPLEINDEX = ED.SAMPLEINDEX) AND > (ED.SAMPLEINDEX = I.SAMPLEINDEX)) AND (EH.SAMPLEINDEX = > I.SAMPLEINDEX)))) > (1 row, 125 ms) > > > > On Feb 24, 2:09 pm, Thomas Mueller <[email protected]> > wrote: >> Hi, >> >> Did you already run ANALYZE to update the index statistics? There was >> a change in version 1.1 regarding to how IN(.,.,.) is optimized, but >> usually it should get slower. See >> alsohttp://www.h2database.com/html/grammar.html#analyze >> >> If this doesn't help, could you run EXPLAIN SELECT ... and post the >> result? Also, could you run SCRIPT NODATA and also post the result so >> that I can build a reproducible test case. >> >> Regards, >> Thomas >> >> On Thu, Feb 19, 2009 at 11:54 PM, PatE <[email protected]> wrote: >> >> > Hello, >> > It seems a substantial change occurred between versions 1.0.78 and >> > 1.1.101 in the way that inline views (queries in the FROM clause) were >> > processed. This legacy query: >> >> > SELECT * FROM eventheader eh, (select * from Indication where >> > indicationtype in (3,4,5,6,7,8) and not deleted) i, eventdetail ed, >> > indicationtype it, indicationsubtype ist >> > WHERE eh.sampleindex=ed.sampleindex and ed.sampleindex=i.sampleindex >> > and ed.eventtype=4 and i.indicationtype=it.id and >> > i.indicationsubtype=ist.id >> >> > went from completing in a few hundred ms in v1.0.78 to just not >> > completing at all in v1.1.101 against an "Indication" table containing >> > ~54000 rows. We can eliminate the inline view and rewrite the query to >> > the following form: >> >> > SELECT * FROM eventheader eh, Indication i, eventdetail ed, >> > indicationtype it, indicationsubtype ist >> > WHERE eh.sampleindex=ed.sampleindex and ed.sampleindex=i.sampleindex >> > and ed.eventtype=4 and i.indicationtype=it.id and >> > i.indicationsubtype=ist.id and indicationtype in (3,4,5,6,7,8) and not >> > deleted >> >> > and it will complete in ~300ms once again. The query originally had >> > been written in that form to deal with some optimizer issues in our >> > previous database software and it took us awhile to notice that >> > something had significantly changed because the Indication is usually >> > not so large. On the larger tables it seems to max out it's memory and >> > then thrash indefinitely. >> >> > We have rewritten occurrences of that particular query, but wanted to >> > let you know that a significant issues with memory usage on views like >> > that seemed to appear in the 1.1 point release and still persist in >> > 1.1.107. If you would like any more information or test data to look >> > into, please let us know. >> >> > Pat >> > Enduro Pipeline Services >> >> > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/h2-database?hl=en -~----------~----~----~----~------~----~------~--~---
