Hi Weird. I can't reproduce it here on a simpler test-case.
Can you do an 'explain analyze' http://www.h2database.com/html/grammar.html#explain on both statements and post them here? Thanks, Noel. Dani wrote: > 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.
