Hi,
> - What version are you testing on?
I am using 1.3.171. I was on 1.3.168. I would say for some of my
slower queries 1.3.171 seems to be about 15-20% faster.
> Would be great if you could test with the nightly build because I made some
> index selection improvements recently.
I tried it out with h2-latest.jar from the website. I had to use
jre7-32bit instead of jre6-64bit to run the code because of the
compilation of h2-latest not being java 6 compatible. The explains
were identical to those of the previous run. The speed did change, but
on further investigation that seems to be the java version. The 32bit
jre7 was about 2.5x slower than the speed of the 64bit jre6
(presumably related to the 32/64 and not the java version)!
>
> - What does your DB URL look like?
jdbc:h2:C:\Users\Mike\Eclipse\emanate_customerb\webit.customerb-define\base\systems\installation/h2;DB_CLOSE_DELAY=-1
>
> - Can you do an EXPLAIN on both variants and post the results here?
Sure. To trigger the performance difference this time I had to change
4 left joins to inner joins. The query went from 12s -> 30s (it was
120s on one run, but subsequently went back down to 30s).
I do not have my original explain output, but it seems I was probably
wrong about my assertion that the explain was essentially the same.
This time around they are different, so it does appear to be a query
optimiser issue.
many thanks,
Mike
--------------- WITH INNER JOINS -------------------
EXPLAIN SELECT
t."Id" AS vc0_0,
t."name" AS vc0_1,
ifnull(vn0_1.vc0_0,empty_map()) AS vc0_2,
ifnull(vn0_1.vc0_1,0) AS vc0_3
FROM "product_category" AS t
LEFT JOIN (SELECT
MAPSEQ_SLICE(mapv_sum( (t."nhs_skewed_date",(t."sale_quantity" *
CASE WHEN false THEN 1 ELSE t."sale_price" END))
),DATEADD('month',-((9 * 1)),STARTOF_MONTH('2013-01-01')),12) AS
vc0_0,
IFNULL(coalesce(sum((t."sale_quantity" * CASE WHEN false THEN 1
ELSE t."sale_price" END)),0),0) AS vc0_1,
IFNULL(t_product_sub_category."category",0) AS GROUPBY
FROM "sales_transaction" AS t
INNER JOIN "customer_department" AS t_customer_department ON
t."customer_department"=t_customer_department."Id"
INNER JOIN "customer" AS t_customer_department_customer ON
t_customer_department."customer"=t_customer_department_customer."Id"
INNER JOIN "product" AS t_product ON t."product"=t_product."Id"
INNER JOIN "product_sub_category" AS t_product_sub_category ON
t_product."sub_category"=t_product_sub_category."Id"
WHERE (true) AND ((null) IS NULL OR (null = t."customer_department"))
AND ((null) IS NULL OR (null = t_customer_department."customer")) AND
((null) IS NULL OR (null =
t_customer_department_customer."sales_territory")) AND
((t."nhs_skewed_date" >= DATEADD('month',-((9 *
1)),STARTOF_MONTH('2013-01-01'))) AND (t."nhs_skewed_date" <
DATEADD('month',(3 * 1),STARTOF_MONTH('2013-01-01'))))
GROUP BY groupby) AS vn0_1 ON t."Id"=vn0_1.groupby
WHERE (true)
ORDER BY vc0_1 ASC,t."Id"
LIMIT 10000;
PLAN
SELECT
T."Id" AS VC0_0,
T."name" AS VC0_1,
IFNULL(VN0_1.VC0_0, PUBLIC.EMPTY_MAP()) AS VC0_2,
IFNULL(VN0_1.VC0_1, 0) AS VC0_3
FROM PUBLIC."product_category" T
/* PUBLIC."product_category".tableScan */
/* WHERE TRUE
*/
LEFT OUTER JOIN (
SELECT
PUBLIC.MAPSEQ_SLICE(MAPV_SUM((T."nhs_skewed_date",
(T."sale_quantity" * CASEWHEN(FALSE, 1, T."sale_price")))),
DATEADD('month', -9, PUBLIC.STARTOF_MONTH('2013-01-01')), 12) AS
VC0_0,
IFNULL(COALESCE(SUM(T."sale_quantity" * CASEWHEN(FALSE, 1,
T."sale_price")), 0), 0) AS VC0_1,
IFNULL(T_PRODUCT_SUB_CATEGORY."category", 0) AS GROUPBY
FROM PUBLIC."customer_department" T_CUSTOMER_DEPARTMENT
/* PUBLIC."customer_department".tableScan */
INNER JOIN PUBLIC."sales_transaction" T
/* PUBLIC.FK_SALES_TRANSACTION_CUSTOMER_DEPARTMENT_INDEX_E:
"customer_department" = T_CUSTOMER_DEPARTMENT."Id" */
ON 1=1
/* WHERE (T."nhs_skewed_date" < DATEADD('month', 3,
PUBLIC.STARTOF_MONTH('2013-01-01')))
AND ((T."customer_department" = T_CUSTOMER_DEPARTMENT."Id")
AND (T."nhs_skewed_date" >= DATEADD('month', -9,
PUBLIC.STARTOF_MONTH('2013-01-01'))))
*/
INNER JOIN PUBLIC."product" T_PRODUCT
/* PUBLIC.PRIMARY_KEY_37: "Id" = T."product" */
ON 1=1
/* WHERE T."product" = T_PRODUCT."Id"
*/
INNER JOIN PUBLIC."customer" T_CUSTOMER_DEPARTMENT_CUSTOMER
/* PUBLIC.PRIMARY_KEY_EB: "Id" = T_CUSTOMER_DEPARTMENT."customer" */
ON 1=1
/* WHERE T_CUSTOMER_DEPARTMENT."customer" =
T_CUSTOMER_DEPARTMENT_CUSTOMER."Id"
*/
INNER JOIN PUBLIC."product_sub_category" T_PRODUCT_SUB_CATEGORY
/* PUBLIC.PRIMARY_KEY_48C: "Id" = T_PRODUCT."sub_category" */
ON 1=1
WHERE ((T_PRODUCT."sub_category" = T_PRODUCT_SUB_CATEGORY."Id")
AND ((T."product" = T_PRODUCT."Id")
AND ((T_CUSTOMER_DEPARTMENT."customer" =
T_CUSTOMER_DEPARTMENT_CUSTOMER."Id")
AND (T."customer_department" = T_CUSTOMER_DEPARTMENT."Id"))))
AND ((T."nhs_skewed_date" >= DATEADD('month', -9,
PUBLIC.STARTOF_MONTH('2013-01-01')))
AND (T."nhs_skewed_date" < DATEADD('month', 3,
PUBLIC.STARTOF_MONTH('2013-01-01'))))
GROUP BY IFNULL(T_PRODUCT_SUB_CATEGORY."category", 0)
) VN0_1
/* SELECT
PUBLIC.MAPSEQ_SLICE(MAPV_SUM((T."nhs_skewed_date",
(T."sale_quantity" * CASEWHEN(FALSE, 1, T."sale_price")))),
DATEADD('month', -9, PUBLIC.STARTOF_MONTH('2013-01-01')), 12) AS
VC0_0,
IFNULL(COALESCE(SUM(T."sale_quantity" * CASEWHEN(FALSE, 1,
T."sale_price")), 0), 0) AS VC0_1,
IFNULL(T_PRODUCT_SUB_CATEGORY."category", 0) AS GROUPBY
FROM PUBLIC."customer_department" T_CUSTOMER_DEPARTMENT
/++ PUBLIC."customer_department".tableScan ++/
INNER JOIN PUBLIC."sales_transaction" T
/++ PUBLIC.FK_SALES_TRANSACTION_CUSTOMER_DEPARTMENT_INDEX_E:
"customer_department" = T_CUSTOMER_DEPARTMENT."Id" ++/
ON 1=1
/++ WHERE (T."nhs_skewed_date" < DATEADD('month', 3,
PUBLIC.STARTOF_MONTH('2013-01-01')))
AND ((T."customer_department" = T_CUSTOMER_DEPARTMENT."Id")
AND (T."nhs_skewed_date" >= DATEADD('month', -9,
PUBLIC.STARTOF_MONTH('2013-01-01'))))
++/
INNER JOIN PUBLIC."product" T_PRODUCT
/++ PUBLIC.PRIMARY_KEY_37: "Id" = T."product" ++/
ON 1=1
/++ WHERE T."product" = T_PRODUCT."Id"
++/
INNER JOIN PUBLIC."customer" T_CUSTOMER_DEPARTMENT_CUSTOMER
/++ PUBLIC.PRIMARY_KEY_EB: "Id" = T_CUSTOMER_DEPARTMENT."customer" ++/
ON 1=1
/++ WHERE T_CUSTOMER_DEPARTMENT."customer" =
T_CUSTOMER_DEPARTMENT_CUSTOMER."Id"
++/
INNER JOIN PUBLIC."product_sub_category" T_PRODUCT_SUB_CATEGORY
/++ PUBLIC.PRIMARY_KEY_48C: "Id" = T_PRODUCT."sub_category" ++/
ON 1=1
WHERE (IFNULL(T_PRODUCT_SUB_CATEGORY."category", 0) IS ?1)
AND (((T_PRODUCT."sub_category" = T_PRODUCT_SUB_CATEGORY."Id")
AND ((T."product" = T_PRODUCT."Id")
AND ((T_CUSTOMER_DEPARTMENT."customer" =
T_CUSTOMER_DEPARTMENT_CUSTOMER."Id")
AND (T."customer_department" = T_CUSTOMER_DEPARTMENT."Id"))))
AND ((T."nhs_skewed_date" >= DATEADD('month', -9,
PUBLIC.STARTOF_MONTH('2013-01-01')))
AND (T."nhs_skewed_date" < DATEADD('month', 3,
PUBLIC.STARTOF_MONTH('2013-01-01')))))
GROUP BY IFNULL(T_PRODUCT_SUB_CATEGORY."category", 0): GROUPBY = T."Id"
*/
ON T."Id" = VN0_1.GROUPBY
WHERE TRUE
ORDER BY 2, 1
LIMIT 10000
--------------- WITH OUTER JOINS -------------------
EXPLAIN SELECT
t."Id" AS vc0_0,
t."name" AS vc0_1,
ifnull(vn0_1.vc0_0,empty_map()) AS vc0_2,
ifnull(vn0_1.vc0_1,0) AS vc0_3
FROM "product_category" AS t
LEFT JOIN (SELECT
MAPSEQ_SLICE(mapv_sum( (t."nhs_skewed_date",(t."sale_quantity" *
CASE WHEN false THEN 1 ELSE t."sale_price" END))
),DATEADD('month',-((9 * 1)),STARTOF_MONTH('2013-01-01')),12) AS
vc0_0,
IFNULL(coalesce(sum((t."sale_quantity" * CASE WHEN false THEN 1
ELSE t."sale_price" END)),0),0) AS vc0_1,
IFNULL(t_product_sub_category."category",0) AS GROUPBY
FROM "sales_transaction" AS t
LEFT JOIN "customer_department" AS t_customer_department ON
t."customer_department"=t_customer_department."Id"
LEFT JOIN "customer" AS t_customer_department_customer ON
t_customer_department."customer"=t_customer_department_customer."Id"
LEFT JOIN "product" AS t_product ON t."product"=t_product."Id"
LEFT JOIN "product_sub_category" AS t_product_sub_category ON
t_product."sub_category"=t_product_sub_category."Id"
WHERE (true) AND ((null) IS NULL OR (null = t."customer_department"))
AND ((null) IS NULL OR (null = t_customer_department."customer")) AND
((null) IS NULL OR (null =
t_customer_department_customer."sales_territory")) AND
((t."nhs_skewed_date" >= DATEADD('month',-((9 *
1)),STARTOF_MONTH('2013-01-01'))) AND (t."nhs_skewed_date" <
DATEADD('month',(3 * 1),STARTOF_MONTH('2013-01-01'))))
GROUP BY groupby) AS vn0_1 ON t."Id"=vn0_1.groupby
WHERE (true)
ORDER BY vc0_1 ASC,t."Id"
LIMIT 10000;
PLAN
SELECT
T."Id" AS VC0_0,
T."name" AS VC0_1,
IFNULL(VN0_1.VC0_0, PUBLIC.EMPTY_MAP()) AS VC0_2,
IFNULL(VN0_1.VC0_1, 0) AS VC0_3
FROM PUBLIC."product_category" T
/* PUBLIC."product_category".tableScan */
/* WHERE TRUE
*/
LEFT OUTER JOIN (
SELECT
PUBLIC.MAPSEQ_SLICE(MAPV_SUM((T."nhs_skewed_date",
(T."sale_quantity" * CASEWHEN(FALSE, 1, T."sale_price")))),
DATEADD('month', -9, PUBLIC.STARTOF_MONTH('2013-01-01')), 12) AS
VC0_0,
IFNULL(COALESCE(SUM(T."sale_quantity" * CASEWHEN(FALSE, 1,
T."sale_price")), 0), 0) AS VC0_1,
IFNULL(T_PRODUCT_SUB_CATEGORY."category", 0) AS GROUPBY
FROM PUBLIC."sales_transaction" T
/* PUBLIC.COLINDEX_SALES_TRANSACTION_NHS_SKEWED_DATE:
"nhs_skewed_date" >= DATEADD('month', -9,
PUBLIC.STARTOF_MONTH('2013-01-01'))
AND "nhs_skewed_date" < DATEADD('month', 3,
PUBLIC.STARTOF_MONTH('2013-01-01'))
*/
/* WHERE (T."nhs_skewed_date" >= DATEADD('month', -9,
PUBLIC.STARTOF_MONTH('2013-01-01')))
AND (T."nhs_skewed_date" < DATEADD('month', 3,
PUBLIC.STARTOF_MONTH('2013-01-01')))
*/
LEFT OUTER JOIN PUBLIC."customer_department" T_CUSTOMER_DEPARTMENT
/* PUBLIC.PRIMARY_KEY_71: "Id" = T."customer_department" */
ON T."customer_department" = T_CUSTOMER_DEPARTMENT."Id"
LEFT OUTER JOIN PUBLIC."customer" T_CUSTOMER_DEPARTMENT_CUSTOMER
/* PUBLIC.PRIMARY_KEY_EB: "Id" = T_CUSTOMER_DEPARTMENT."customer" */
ON T_CUSTOMER_DEPARTMENT."customer" =
T_CUSTOMER_DEPARTMENT_CUSTOMER."Id"
LEFT OUTER JOIN PUBLIC."product" T_PRODUCT
/* PUBLIC.PRIMARY_KEY_37: "Id" = T."product" */
ON T."product" = T_PRODUCT."Id"
LEFT OUTER JOIN PUBLIC."product_sub_category" T_PRODUCT_SUB_CATEGORY
/* PUBLIC.PRIMARY_KEY_48C: "Id" = T_PRODUCT."sub_category" */
ON T_PRODUCT."sub_category" = T_PRODUCT_SUB_CATEGORY."Id"
WHERE (T."nhs_skewed_date" >= DATEADD('month', -9,
PUBLIC.STARTOF_MONTH('2013-01-01')))
AND (T."nhs_skewed_date" < DATEADD('month', 3,
PUBLIC.STARTOF_MONTH('2013-01-01')))
GROUP BY IFNULL(T_PRODUCT_SUB_CATEGORY."category", 0)
) VN0_1
/* SELECT
PUBLIC.MAPSEQ_SLICE(MAPV_SUM((T."nhs_skewed_date",
(T."sale_quantity" * CASEWHEN(FALSE, 1, T."sale_price")))),
DATEADD('month', -9, PUBLIC.STARTOF_MONTH('2013-01-01')), 12) AS
VC0_0,
IFNULL(COALESCE(SUM(T."sale_quantity" * CASEWHEN(FALSE, 1,
T."sale_price")), 0), 0) AS VC0_1,
IFNULL(T_PRODUCT_SUB_CATEGORY."category", 0) AS GROUPBY
FROM PUBLIC."sales_transaction" T
/++ PUBLIC.COLINDEX_SALES_TRANSACTION_NHS_SKEWED_DATE:
"nhs_skewed_date" >= DATEADD('month', -9,
PUBLIC.STARTOF_MONTH('2013-01-01'))
AND "nhs_skewed_date" < DATEADD('month', 3,
PUBLIC.STARTOF_MONTH('2013-01-01'))
++/
/++ WHERE (T."nhs_skewed_date" >= DATEADD('month', -9,
PUBLIC.STARTOF_MONTH('2013-01-01')))
AND (T."nhs_skewed_date" < DATEADD('month', 3,
PUBLIC.STARTOF_MONTH('2013-01-01')))
++/
LEFT OUTER JOIN PUBLIC."customer_department" T_CUSTOMER_DEPARTMENT
/++ PUBLIC.PRIMARY_KEY_71: "Id" = T."customer_department" ++/
ON T."customer_department" = T_CUSTOMER_DEPARTMENT."Id"
LEFT OUTER JOIN PUBLIC."customer" T_CUSTOMER_DEPARTMENT_CUSTOMER
/++ PUBLIC.PRIMARY_KEY_EB: "Id" = T_CUSTOMER_DEPARTMENT."customer" ++/
ON T_CUSTOMER_DEPARTMENT."customer" =
T_CUSTOMER_DEPARTMENT_CUSTOMER."Id"
LEFT OUTER JOIN PUBLIC."product" T_PRODUCT
/++ PUBLIC.PRIMARY_KEY_37: "Id" = T."product" ++/
ON T."product" = T_PRODUCT."Id"
LEFT OUTER JOIN PUBLIC."product_sub_category" T_PRODUCT_SUB_CATEGORY
/++ PUBLIC.PRIMARY_KEY_48C: "Id" = T_PRODUCT."sub_category" ++/
ON T_PRODUCT."sub_category" = T_PRODUCT_SUB_CATEGORY."Id"
WHERE (IFNULL(T_PRODUCT_SUB_CATEGORY."category", 0) IS ?1)
AND ((T."nhs_skewed_date" >= DATEADD('month', -9,
PUBLIC.STARTOF_MONTH('2013-01-01')))
AND (T."nhs_skewed_date" < DATEADD('month', 3,
PUBLIC.STARTOF_MONTH('2013-01-01'))))
GROUP BY IFNULL(T_PRODUCT_SUB_CATEGORY."category", 0): GROUPBY = T."Id"
*/
ON T."Id" = VN0_1.GROUPBY
WHERE TRUE
ORDER BY 2, 1
LIMIT 10000
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
