Yes it is an obfuscated real database as I mentionned it when I attached the files.
The database is designed to trace all actions performed on an application. The database design is what it is and is mostly optimized to store/restore particular data fastly. The use case of this query is that the application provides an API to query "everything". The querying API provides classes / methods to filter returned elements. So the query is mostly generated to represent what was asked through the API. I know the query is complex and one solution whould be to change the query itself (but as I said, it is generated). If other databases had similar problems, I would be looking into that but MySql, PostgreSql, Oracle handle it very fast. I would be expecting H2 to have better performances then it does, especially when the database is empty ! (I am not expecting the same performance from H2 that I get on Oracle but like to get something acceptable). 2016-12-08 13:30 GMT+01:00 Christian MICHON <[email protected]>: > I beg to differ: I ran the DDL into DBVisualizer (references mode, > circular view) and from the look of it this is a real database with > obfuscated names. > > Redesigning the database is not an option if it's not from the original > author: the query itself has to be redesigned and this leads to the > validity of the use case. > > @Itineric: can you share the application type and the use case of your > query? > > > On Thursday, December 8, 2016 at 9:22:46 AM UTC+1, Steve McLeod wrote: >> >> Your query is extreme. Hundreds of joins, dozens of nested selects. Way >> too big, and way too complicated. You'll never get any decent performance >> with a query like that, nor will you ever be able to analyse and understand >> the reasons for the performance problems. >> >> The solution to your problem is to redesign your database. Read up on >> database normalisation. >> >> If you need ad hoc, complicated queries, first load your data from your >> normal schema into a star schema as described in database warehouse >> textbooks. >> >> -- > You received this message because you are subscribed to a topic in the > Google Groups "H2 Database" group. > To unsubscribe from this topic, visit https://groups.google.com/d/ > topic/h2-database/6B5Sla2PkG8/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > [email protected]. > To post to this group, send email to [email protected]. > Visit this group at https://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/d/optout. > -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
