Indeed, it all boils down to data volumes. I had no issues when I loaded 100 rows in dm_liveorders_bmcsv_orderheader and dm_liveorders_wc_orders and 300 rows in dm_liveorders_wc_orders and dm_liveorders_bmcsv_orderlines.
However, I hit the problem when I loaded more realistic levels, namely 5,000 rows for liveorders_bmcsv_orderheader and dm_liveorders_wc_orders and 250,000 rows in dm_liveorders_wc_orders and dm_liveorders_bmcsv_orderlines. The original query does not complete (explain plan for will return quickly but of course explain analyze won't). The "re-factored" version that splits the query and uses temporary tables works with good performance. On Friday, 12 July 2013 15:54:50 UTC+1, Noel Grandin wrote: > > > On 2013-07-12 16:42, Seb N/A wrote: > > Sincere apologies, and point taken... > > I extracted the DDL from code and I cut the commas while removing > > double-quotes. > > I have now fixed the DDL and loaded in a sandbox: > > >>runscript from 'ddl.txt'; > > runscript from 'ddl.txt'; > > Update count: 25 > > (16 ms) > > > > DDL.txt re-attached. > > > > Once again, apologies for the concerns caused... > > Thanks. > I've just tried this out, and it's "working" for me. > Explain plan generates a reasonable result, and your problematic query > returns in 16ms. > > Now the reason the query "works" for me could be because I have no data > in the database, but that doesn't explain why EXPLAIN PLAN was failing > for you. > > What version are you running? > If it's an older version, we may have fixed the bug already. > > And what does your JDBC URL look like? > I'm interested in what extra options you are using. > > > -- 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 http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
