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.