Inefficient SELECT

[Former Member]

Hi all,

I've got a problem with a SELECT that takes way too much time and I can't figure out a workaround. The SELECT is

SELECT * FROM (SELECT a, COUNT(*) FROM (...) GROUP BY a) s1, (SELECT a, b, COUNT(*) FROM (...) GROUP BY a, b) s2 WHERE s1.a = s2.a

The subqueries s1 and s2, when run alone, yield about 60 rows and take a couple of milliseconds. When they are put together, it takes more than a minute! I understand that the planner tries to do its best and may be mislead by the structure of the subqueries, I've tried to explain and visualise the plan, but cannot really figure out how to help it, it seems to generate millions of rows internally. Is there a way to tell the planner to execute the two queries separately and only then to put them together? I'd prefer to not use a temporary table because this is inside an .xsjs call. Please don't respond with "we need to see the whole SELECT", I don't need to make it a perfect query nor to improve the planner, I only need to execute the two subqueries separately.

Cheers,

-- Micha

[Former Member]

I've found a workaround, although I'm not very happy with it. Instead of joining the two subqueries directly I first fire a subquery that returns all a's and join s1 and s2 with it, so that the planner does not try to 'optimize' them together:

SELECT * FROM (SELECT DISTINCT a FROM ...) all, (SELECT a, COUNT(*) FROM (...) GROUP BY a) s1, (SELECT a, b, COUNT(*) FROM (...) GROUP BY a, b) s2 WHERE s1.a = all.a AND s2.a = all.a

-- Micha

[lbreddemann]

Hi Micha,

have you tried common table expressions (CTEs, you know, the WITH clause) instead of subqueries?

- Lars

[Former Member]

Hi Lars,

no, I haven't, the SELECT is actually part of an INSERT and Hana doesn't like WITH clause in an INSERT subquery.

Cheers,

-- Micha