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