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 -~----------~----~----~----~------~----~------~--~---
