I found some more.

The main culprit seems to be the inner select in the select clause.

SELECT MIN(BA.START)
FROM EXODUS_VIEW.BROADCAST_ALL BA
WHERE
    BA.CID = C.CID
    AND BA.PNID = PF.PNID
    AND BA.START > F.TIMEPOINT_NOW()
) AS NEXT_BROADCAST

It works very fast when it's part of the outmost select.
But put inside another select it slows down the query 1000-fold (with my data).
From 4ms to 4150ms.

However without the subselect in the select clause the numbers are:
From 5ms (without the enclosing select) to 1000ms with enclosing select.
Still a considerable slowdown for logically the same query.

It seems that H2 somehow materializes the whole inner select and if it is big then even small queries become slow because the optimizer can not understand things behind the parentheses, or something.

I think this is quite an important issue because it affects potentially a large part of all the views.

- Rami

On 11.10.2014 18:43, Rami Ojares wrote:
I found something!

If I execute the query like this:
SELECT * FROM ( <THE_QUERY> )
I get the same slowdown as with the view.

So the reason is that enclosing the same query inside another query makes it slow.
Could something be done about this?

- Rami


On 11.10.2014 18:38, Rami Ojares wrote:
Hi,

What could be the cause of this?
I have a query that executes in few milliseconds.
I create a view out of it and execute the exact query through the view.
And all of a sudden it takes many seconds. It becomes 1000 times slower.

I check the query from INFORMATION_SCHEMA.VIEWS and execute it and it is again fast. Seems like the view is somehow executed differently than the query that defines it but I don't know how.

I can not give a simple test case because the query is complex and depends on complex datamodel. But I give you the query anyway if you could see the cause by simply glancing at the construct used.

- Rami

SELECT
    T.TRANSLATION_ID,
    T.TRANSLATION_ID IS NOT NULL AS ASSIGNED,
    S.SUBTITLE_ID,
    PF.PNID,
    C.CID,

    S.LANG_ID,
    T.TRANSLATOR_ID,
    TOR.NAME AS TRANSLATOR_NAME,

    PF.SERIES_NAME,
    PF.EPISODE_NUMBER,
    PF.NAME AS PROGRAM_NAME,

    T.SENT_TO_TRANSLATOR,
    T.SENT_TO_TRANSLATOR_DATE,

    CASE
        WHEN T.ARRIVED_USE_RULE = FALSE
        THEN T.ARRIVED_DUE_DATE

WHEN T.ARRIVED_USE_RULE = TRUE AND PF.FIRST_BROADCAST IS NOT NULL
        THEN F.DATEX_ADD_MILLIS(
            PF.FIRST_BROADCAST_DATE,
CAST(-1 AS BIGINT) * F.DAY * CAST(T.ARRIVED_DAYS_BEFORE_FIRST_BROADCAST AS BIGINT)
        )
        ELSE NULL
    END AS ARRIVED_DEADLINE_DATE,

    (
        SELECT MIN(BA.START)
        FROM EXODUS_VIEW.BROADCAST_ALL BA
        WHERE
            BA.CID = C.CID
            AND BA.PNID = PF.PNID
            AND BA.START > F.TIMEPOINT_NOW()
    ) AS NEXT_BROADCAST,

    T.ARRIVED,
    T.ARRIVED_DATE,

    T.VERIFIED,
    T.VERIFIED_DATE,

    S.ARCHIVED AS SUB_ARCHIVED,
    S.ARCHIVED_DATE AS SUB_ARCHIVED_DATE,

    PF.REC_ARRIVED,
    PF.REC_ARRIVED_DATE,
    PF.REC_ARCHIVED,
    PF.REC_ARCHIVED_DATE,

    S.NOTES AS SUB_NOTES,
    PF.NOTES AS PROGRAM_NOTES

FROM
    LINGUISTIC.SUBTITLE S
    JOIN EXODUS_VIEW.PROGRAM_FIXED PF ON S.PNID = PF.PNID
    CROSS JOIN METADATA.CHANNEL C
    LEFT JOIN LINGUISTIC.TRANSLATION T ON T.SUBTITLE_ID = S.SUBTITLE_ID
LEFT JOIN LINGUISTIC.TRANSLATOR TOR ON T.TRANSLATOR_ID = TOR.TRANSLATOR_ID


--
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.

Reply via email to