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