Hi,
On a number of occasions I have found that replacing LEFT JOIN with
INNER JOIN dramatically improved poorly performing queries. Generally
the queries I run are generated so it was more convenient to use LEFT
JOIN but depending on various criteria I have been able to use inner
joins where it is ok to do so.
It seemed to effect both of the main cases where I do joins. Broadly
speaking most joins I do are either
A) A simple join to get a shared piece of information. e.g. An
employee table joins to a company table to get their company name. (of
course performance issues are only noticeable in complex queries,
containing such a simple join).
B) Reporting. For example a query showing the total number of
employees a company has could be something like:-
SELECT
company.name,
company.id,
employee_report.count
FROM company
LEFT JOIN(
SELECT
company,
count(*)
FROM employee
GROUP BY company
) as employee_report ON employee_report.company=company.id;
Here the LEFT JOIN is only necessary if there are employees who do not
have a company. Having LEFT JOIN will produce a single additional row.
Sometimes the cost of this row is several times worse query
performance (i.e. much worse than doing two queries to get all the
information). So in my mind there is certainly something that could be
improved here.
So far so good, but curiously, recently I have found a counter example
where the reverse is true. It certainly seems like INNER JOIN should
always be as quick or quicker than a LEFT JOIN. I have looked at the
EXPLAIN and it does not show a difference in the query structure, so
presumably it is not a fickle optimiser issue. It seems to me that
significant unnecessary work is being performed.
Due to the nature of this problem it seems unlikely that I can easily
produce a simplified test case. However if it will get looked into I
am quite prepared to anonymise the data and produce a test case (with
custom functions loaded) which will demonstrate the difference. For
what it is worth, the query below is the problematic one. Replacing
INNER JOIN "product"
with
LEFT JOIN "product"
results in a speed up of about 2x. (In my case 12->25 seconds or so).
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"
INNER 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
All help appreciated,
Mike
--
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.
