Hey everybody,
I have 2 queries here that basically do the same.
Except the first query takes about 0:03:51.262 (almost 4 minutes).
And the second one takes about 0:00:00.81 (less than a second)!
That's 260 times slower! I am just wondering how come?
Any mistake on my side?
The difference between the two is --> DATEADD('YEAR', -12,
CURRENT_DATE())
opposed to the actual date --> '2000-12-01'
Both do a selfejoin.
Both queries return 2588 records. H2 version 1.3.159 (2011-08-13) on
XP
1st the slow one:
SELECT ANLKURSE.ANLDETAIL_WKN, ANLKURSE.DATUM, ANLKURSE.KURS,
A.MAXKURS, A.MINKURS, A.AVGKURS, A.COUNTKURS
FROM ANLKURSE
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 ANLKURSE WHERE
ANLKURSE.ANLDETAIL_WKN = '840400' AND ANLKURSE.DATUM > DATEADD('YEAR',
-12, CURRENT_DATE())) AS A
ON A.ANLDETAIL_WKN = ANLKURSE.ANLDETAIL_WKN
WHERE ANLKURSE.ANLDETAIL_WKN = '840400' AND ANLKURSE.DATUM >
DATEADD('YEAR', -20, CURRENT_DATE())
ORDER BY ANLKURSE.DATUM ASC;
2nd the fast one:
SELECT ANLKURSE.ANLDETAIL_WKN, ANLKURSE.DATUM, ANLKURSE.KURS,
A.MAXKURS, A.MINKURS, A.AVGKURS, A.COUNTKURS
FROM ANLKURSE
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 ANLKURSE
WHERE ANLKURSE.ANLDETAIL_WKN = '840400' AND ANLKURSE.DATUM >
'2000-12-01') AS A
ON A.ANLDETAIL_WKN = ANLKURSE.ANLDETAIL_WKN
WHERE ANLKURSE.ANLDETAIL_WKN = '840400' AND ANLKURSE.DATUM >
'2000-12-01' ORDER BY ANLKURSE.DATUM ASC;
It would be great to get some thoughts on this one
Dani
--
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.
