We would appreciate your advice. In our application, we make intensive use of the construction involving temporary tables:
create memory local temporary table if not exists temp_report as select * from ( .... ) We use this, for instance, in reporting functionality. This allows calculated fields to behave like physical fields, enabling users to create filters based on the actual values found. We also use this approach to keep complex queries readable and to prevent the query optimizer from making incorrect choices regarding indexes. However, this construction has the drawback that the size of the database can grow very quickly, which has a dramatic effect on performance. A SHUTDOWN COMPACT resolves the problem completely — the database shrinks significantly in size, and performance improves again. Unfortunately, SHUTDOWN COMPACT is disruptive in production and is not stable in the current version, see GitHub Issue #4247 <https://github.com/h2database/h2database/issues/4247>, which can even lead to data loss. Ideally, we would like to move the use of these temporary tables completely outside of the regular database — for example, to a temporary database. Moving everything entirely to RAM is not feasible due to the number of users we need to support. Does anyone have advice? -- 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 view this discussion visit https://groups.google.com/d/msgid/h2-database/332adbe1-69a0-410a-a665-033f04c69d6cn%40googlegroups.com.
