Hi Mike, I would like to try to help you. So, can you provide schema/test data to reproduce the cited situation?
Regards, Fred 2015-03-09 10:24 GMT-03:00 Mike Goodwin <[email protected]>: > Hi, > > I have a query performance problem that has come, gone away and come back > again. > > The issue seems to be when using what are sometimes referred to as sub > types. A sub type is a table that share a primary key with the super type > table. This explains the scheme a bit better. > > > http://blogs.msdn.com/b/dfurman/archive/2009/08/31/disjoint-subtyping-in-sql.aspx > > I have a situation where I have a query that essentially involves a sub > type (B/enquiry line) and the super type (A/part) table. A 3rd table > (C/enquiry) is referenced by the sub type directly and there is a foreign > key/index between them. I want to aggregate over B/A and group by C.The > problem seems to be that the optimiser has chosen to use A as the main > table in the group by sub query and then because of this choice it does not > have access to the query when joining to C and so it becomes very slow > because of the nested table scan. > > I appreciate that probably reads like nonsense and may not be accurate. I > can provide the SCRIPT TO schema/test data, if anyone would like to take a > closer look. The query is and the EXPLAIN is at the bottom of this email. > > > I should say I really appreciate h2 and the performance it gives us (it's > a great little database). > > <minor SQL rant> Sometimes I find SQL to be a bit frustrating, because > when doing what I view as hierarchical operations with what should be a > clear execution plan, when they are expressed in SQL this clarity on how > they should be executed is lost because of its generality. 99% of the SQL I > write (or generate) usually amounts to hierarchical operations and this > generality increases the risk of performance issues - I think this maybe > true for many database users. Take In this example, some enquiries, some > lines, some parts which correspond to lines. There may be 1000s of > enquiries and 10000s of lines/parts. However each enquiry would only ever > have a few lines (probably <10 on average). The lines themselves are never > shared between enquires. An operation aggregating over the lines (e.g. > counting them, or summing their values) should not ever need to bigger than > O(lines) and I should be able to express it in away which this is clear and > without risk. It seems to be me (having used other database, e.g. oracle) > that the promise of optimisers removing this risk have never fully > delivered. I would dearly like to have a way to express hiercarchical > queries and bypass sql!! (I'd settle for a way to optionally prevent the > query optimiser from reordering joins). > > regards, > > Mike > > (on a related note, I think that it would be really good if the SCRIPT > function could take a query and then output just the necessary statements > to recreate enough of the database to run the query, it would make > producing isolated test cases many many times easier ...) > > ---------------- > > > > EXPLAIN SELECT > IFNULL(t_lines_filter0.vc0,0) AS value > FROM "enquiry" AS t > LEFT JOIN ( > SELECT > IFNULL(coalesce(sum(CASE WHEN > t_lines_filter0."include" > THEN > CASE WHEN t_lines_filter0_part_sub_job_costing.revision IS > NULL > THEN NULL > ELSE > IFNULL(t_lines_filter0_part_sub_job_costing_internal.vc0,0) > END > ELSE > 0 > END),0),0) AS vc0, > t_lines_filter0."enquiry" AS GROUPBY > FROM "enquiry_line" AS t_lines_filter0 > INNER JOIN "part_sub" AS t_lines_filter0_part_sub ON > t_lines_filter0."Id" IS t_lines_filter0_part_sub."Id" > INNER JOIN "job" AS t_lines_filter0_part_sub_job ON > t_lines_filter0_part_sub."Id" IS t_lines_filter0_part_sub_job."Id" > LEFT JOIN "costing" AS t_lines_filter0_part_sub_job_costing ON > t_lines_filter0_part_sub_job."costing" IS > t_lines_filter0_part_sub_job_costing."Id" > LEFT JOIN ( > SELECT > > IFNULL(coalesce(sum(t_lines_filter0_part_sub_job_costing_internal."total_time"),0),0) > AS vc0, > t_lines_filter0_part_sub_job_costing_internal."costing" AS > GROUPBY > FROM "costing_internal" AS > t_lines_filter0_part_sub_job_costing_internal > WHERE (true) > GROUP BY groupby > ) AS t_lines_filter0_part_sub_job_costing_internal ON > t_lines_filter0_part_sub_job_costing."Id" IS > t_lines_filter0_part_sub_job_costing_internal.groupby > WHERE (t_lines_filter0."include") > GROUP BY groupby > ) AS t_lines_filter0 ON t."Id" IS t_lines_filter0.groupby ; > > > ---------- > > > SELECT > IFNULL(T_LINES_FILTER0.VC0, 0) AS VALUE > FROM "enquiry" T > /* "enquiry".tableScan */ > LEFT OUTER JOIN ( > SELECT > IFNULL(COALESCE(SUM(CASE WHEN T_LINES_FILTER0."include" THEN CASE > WHEN (T_LINES_FILTER0_PART_SUB_JOB_COSTING.REVISION IS NULL) THEN NULL ELSE > IFNULL(T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL.VC0, 0) END ELSE 0 > END), 0), 0) AS VC0, > T_LINES_FILTER0."enquiry" AS GROUPBY > FROM "part_sub" T_LINES_FILTER0_PART_SUB > /* "part_sub".tableScan */ > INNER JOIN "job" T_LINES_FILTER0_PART_SUB_JOB > /* PRIMARY_KEY_1A: "Id" IS T_LINES_FILTER0_PART_SUB."Id" */ > ON 1=1 > /* WHERE T_LINES_FILTER0_PART_SUB."Id" IS > T_LINES_FILTER0_PART_SUB_JOB."Id" > */ > LEFT OUTER JOIN "costing" T_LINES_FILTER0_PART_SUB_JOB_COSTING > /* PRIMARY_KEY_C03: "Id" IS T_LINES_FILTER0_PART_SUB_JOB."costing" > */ > ON T_LINES_FILTER0_PART_SUB_JOB."costing" IS > T_LINES_FILTER0_PART_SUB_JOB_COSTING."Id" > LEFT OUTER JOIN ( > SELECT > > IFNULL(COALESCE(SUM(T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL."total_time"), > 0), 0) AS VC0, > T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL."costing" AS > GROUPBY > FROM "costing_internal" > T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL > /* FK_COSTING_INTERNAL_COSTING_INDEX_F */ > WHERE TRUE > GROUP BY T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL."costing" > /* group sorted */ > ) T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL > /* SELECT > > IFNULL(COALESCE(SUM(T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL."total_time"), > 0), 0) AS VC0, > T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL."costing" AS > GROUPBY > FROM "costing_internal" > T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL > /++ FK_COSTING_INTERNAL_COSTING_INDEX_F: "costing" IS ?1 ++/ > WHERE T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL."costing" IS ?1 > GROUP BY T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL."costing" > /++ group sorted ++/: GROUPBY IS > T_LINES_FILTER0_PART_SUB_JOB_COSTING."Id" > */ > ON T_LINES_FILTER0_PART_SUB_JOB_COSTING."Id" IS > T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL.GROUPBY > INNER JOIN "enquiry_line" T_LINES_FILTER0 > /* PRIMARY_KEY_B95: "Id" IS T_LINES_FILTER0_PART_SUB."Id" */ > ON 1=1 > WHERE T_LINES_FILTER0."include" > AND ((T_LINES_FILTER0_PART_SUB."Id" IS > T_LINES_FILTER0_PART_SUB_JOB."Id") > AND (T_LINES_FILTER0."Id" IS T_LINES_FILTER0_PART_SUB."Id")) > GROUP BY T_LINES_FILTER0."enquiry" > ) T_LINES_FILTER0 > /* SELECT > IFNULL(COALESCE(SUM(CASE WHEN T_LINES_FILTER0."include" THEN CASE > WHEN (T_LINES_FILTER0_PART_SUB_JOB_COSTING.REVISION IS NULL) THEN NULL ELSE > IFNULL(T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL.VC0, 0) END ELSE 0 > END), 0), 0) AS VC0, > T_LINES_FILTER0."enquiry" AS GROUPBY > FROM "part_sub" T_LINES_FILTER0_PART_SUB > /++ "part_sub".tableScan ++/ > INNER JOIN "job" T_LINES_FILTER0_PART_SUB_JOB > /++ PRIMARY_KEY_1A: "Id" IS T_LINES_FILTER0_PART_SUB."Id" ++/ > ON 1=1 > /++ WHERE T_LINES_FILTER0_PART_SUB."Id" IS > T_LINES_FILTER0_PART_SUB_JOB."Id" > ++/ > LEFT OUTER JOIN "costing" T_LINES_FILTER0_PART_SUB_JOB_COSTING > /++ PRIMARY_KEY_C03: "Id" IS > T_LINES_FILTER0_PART_SUB_JOB."costing" ++/ > ON T_LINES_FILTER0_PART_SUB_JOB."costing" IS > T_LINES_FILTER0_PART_SUB_JOB_COSTING."Id" > LEFT OUTER JOIN ( > SELECT > > IFNULL(COALESCE(SUM(T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL."total_time"), > 0), 0) AS VC0, > T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL."costing" AS > GROUPBY > FROM "costing_internal" > T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL > /++ FK_COSTING_INTERNAL_COSTING_INDEX_F ++/ > WHERE TRUE > GROUP BY T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL."costing" > /++ group sorted ++/ > ) T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL > /++ SELECT > > IFNULL(COALESCE(SUM(T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL."total_time"), > 0), 0) AS VC0, > T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL."costing" AS > GROUPBY > FROM "costing_internal" > T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL > /++ FK_COSTING_INTERNAL_COSTING_INDEX_F: "costing" IS ?2 ++/ > WHERE T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL."costing" IS ?2 > GROUP BY T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL."costing" > /++ group sorted ++/: GROUPBY IS > T_LINES_FILTER0_PART_SUB_JOB_COSTING."Id" > ++/ > ON T_LINES_FILTER0_PART_SUB_JOB_COSTING."Id" IS > T_LINES_FILTER0_PART_SUB_JOB_COSTING_INTERNAL.GROUPBY > INNER JOIN "enquiry_line" T_LINES_FILTER0 > /++ PRIMARY_KEY_B95: "Id" IS T_LINES_FILTER0_PART_SUB."Id" ++/ > ON TRUE > WHERE (T_LINES_FILTER0."enquiry" IS ?1) > AND (T_LINES_FILTER0."include" > AND ((T_LINES_FILTER0_PART_SUB."Id" IS > T_LINES_FILTER0_PART_SUB_JOB."Id") > AND (T_LINES_FILTER0."Id" IS T_LINES_FILTER0_PART_SUB."Id"))) > GROUP BY T_LINES_FILTER0."enquiry": GROUPBY IS T."Id" > */ > ON T."Id" IS T_LINES_FILTER0.GROUPBY > > > > > -- > 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/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 http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
