Could you try updating to the latest H2 and see if it that helps?
Otherwise there must be a bug in the query optimiser where it isn't noticing
that the DATEADD expression can be
optimised to a constant.
Dani wrote:
> 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.
