Thanks for your reply, ok updated to 1.3.163 (2011-12-30) How would I check if besides the driver the database file is really of that version?
Still, the above statement takes around 4 min! I was hoping to let H2 do most of the work to reduce code on my application's side. Also I am using DATEADD() quite a bit on smaller timespans so the delay was not obviuos yet. Maybe someone with a little more knowledge ;-) could do some testing if this really is a bug!? For now I will run a sole 'SELECT DATEADD...;' in my app and then run the actual query. Dani On 9 Jan., 09:06, Noel Grandin <[email protected]> wrote: > 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.
