Skip to Content
avatar image
Former Member

Inefficient SELECT

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • avatar image
    Former Member
    Jan 08, 2015 at 09:23 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Lars Breddemann

      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