I mistyped -20 in my original Post, both dates are the same. So it is
-12.
However this has no effect on the query though because the data goes
back only to 2002!
It looks like the fast query is even faster with the new version.
Thanks Noel,
Dani
EXPLAIN ANALYZE...
Plan for the slow one:
SELECT
ANLKURSE.ANLDETAIL_WKN,
ANLKURSE.DATUM,
ANLKURSE.KURS,
A.MAXKURS,
A.MINKURS,
A.AVGKURS,
A.COUNTKURS
FROM PUBLIC.ANLKURSE
/* PUBLIC.PRIMARY_KEY_E: ANLDETAIL_WKN = '840400'
AND DATUM > DATEADD('YEAR', -12, CURRENT_DATE())
*/
/* WHERE (ANLKURSE.ANLDETAIL_WKN = '840400')
AND (ANLKURSE.DATUM > DATEADD('YEAR', -12, CURRENT_DATE()))
*/
/* scanCount: 2590 */
INNER JOIN (
SELECT
ANLKURSE.ANLDETAIL_WKN,
MAX(ANLKURSE.KURS) AS MAXKURS,
MIN(ANLKURSE.KURS) AS MINKURS,
AVG(ANLKURSE.KURS) AS AVGKURS,
COUNT(ANLKURSE.KURS) AS COUNTKURS
FROM PUBLIC.ANLKURSE
/* PUBLIC.PRIMARY_KEY_E: ANLDETAIL_WKN = '840400'
AND DATUM > DATEADD('YEAR', -12, CURRENT_DATE())
*/
WHERE (ANLKURSE.ANLDETAIL_WKN = '840400')
AND (ANLKURSE.DATUM > DATEADD('YEAR', -12, CURRENT_DATE()))
) A
/* SELECT
ANLKURSE.ANLDETAIL_WKN,
MAX(ANLKURSE.KURS) AS MAXKURS,
MIN(ANLKURSE.KURS) AS MINKURS,
AVG(ANLKURSE.KURS) AS AVGKURS,
COUNT(ANLKURSE.KURS) AS COUNTKURS
FROM PUBLIC.ANLKURSE
/++ PUBLIC.PRIMARY_KEY_E: ANLDETAIL_WKN = '840400'
AND DATUM > DATEADD('YEAR', -12, CURRENT_DATE())
++/
WHERE (ANLKURSE.ANLDETAIL_WKN = '840400')
AND (ANLKURSE.DATUM > DATEADD('YEAR', -12, CURRENT_DATE()))
HAVING ANLKURSE.ANLDETAIL_WKN IS ?1: ANLDETAIL_WKN =
ANLKURSE.ANLDETAIL_WKN
AND ANLDETAIL_WKN = ANLKURSE.ANLDETAIL_WKN
*/
ON 1=1
/* scanCount: 5178 */
WHERE (A.ANLDETAIL_WKN = ANLKURSE.ANLDETAIL_WKN)
AND ((ANLKURSE.ANLDETAIL_WKN = '840400')
AND (ANLKURSE.DATUM > DATEADD('YEAR', -12, CURRENT_DATE())))
ORDER BY 2
Plan for the fast one:
SELECT
ANLKURSE.ANLDETAIL_WKN,
ANLKURSE.DATUM,
ANLKURSE.KURS,
A.MAXKURS,
A.MINKURS,
A.AVGKURS,
A.COUNTKURS
FROM PUBLIC.ANLKURSE
/* PUBLIC.PRIMARY_KEY_E: ANLDETAIL_WKN = '840400'
AND DATUM > '2000-12-01'
*/
/* WHERE (ANLKURSE.ANLDETAIL_WKN = '840400')
AND (ANLKURSE.DATUM > '2000-12-01')
*/
/* scanCount: 2590 */
INNER JOIN (
SELECT
ANLKURSE.ANLDETAIL_WKN,
MAX(ANLKURSE.KURS) AS MAXKURS,
MIN(ANLKURSE.KURS) AS MINKURS,
AVG(ANLKURSE.KURS) AS AVGKURS,
COUNT(ANLKURSE.KURS) AS COUNTKURS
FROM PUBLIC.ANLKURSE
/* PUBLIC.PRIMARY_KEY_E: ANLDETAIL_WKN = '840400'
AND DATUM > '2000-12-01'
*/
WHERE (ANLKURSE.ANLDETAIL_WKN = '840400')
AND (ANLKURSE.DATUM > '2000-12-01')
) A
/* SELECT
ANLKURSE.ANLDETAIL_WKN,
MAX(ANLKURSE.KURS) AS MAXKURS,
MIN(ANLKURSE.KURS) AS MINKURS,
AVG(ANLKURSE.KURS) AS AVGKURS,
COUNT(ANLKURSE.KURS) AS COUNTKURS
FROM PUBLIC.ANLKURSE
/++ PUBLIC.PRIMARY_KEY_E: ANLDETAIL_WKN = '840400'
AND DATUM > '2000-12-01'
++/
WHERE (ANLKURSE.ANLDETAIL_WKN = '840400')
AND (ANLKURSE.DATUM > '2000-12-01')
HAVING ANLKURSE.ANLDETAIL_WKN IS ?1: ANLDETAIL_WKN =
ANLKURSE.ANLDETAIL_WKN
AND ANLDETAIL_WKN = ANLKURSE.ANLDETAIL_WKN
*/
ON 1=1
/* scanCount: 5178 */
WHERE (A.ANLDETAIL_WKN = ANLKURSE.ANLDETAIL_WKN)
AND ((ANLKURSE.ANLDETAIL_WKN = '840400')
AND (ANLKURSE.DATUM > '2000-12-01'))
ORDER BY 2
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
