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.