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.
