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 also http://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 -~----------~----~----~----~------~----~------~--~---
