Query runnning too long on 1.1.111

[GoogleCodeExporter]

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)
1. Database attached in zip file
2. Run query 
SELECT qRoot.ID col0
FROM bc_producer qRoot
WHERE qRoot.ID IN
  (SELECT qRoots0.ProducerID col0
  FROM bc_agencybillcycle qRoots0
  WHERE qRoots0.ID IN
    (SELECT qRoots0s0.AgencyBillCycleID col0
    FROM bc_agencycycledist qRoots0s0
    WHERE qRoots0s0.ID IN
      (SELECT qRoots0s0s0.AgencyCyclePaymentID col0
      FROM bc_writeoff qRoots0s0s0
      WHERE qRoots0s0s0.Subtype = 1
      AND qRoots0s0s0.ID       IN
        (SELECT qRoots0s0s0s1.OwnerID col0
        FROM bc_revwriteoff qRoots0s0s0s1
        INNER JOIN (bc_writeoff bc_writeoff_1
        INNER JOIN (bc_TAccountContainer bc_TAccountContainer_2)
        ON bc_TAccountContainer_2.ID
=bc_writeoff_1.TAccountContainerID
        AND bc_TAccountContainer_2.ID IN
          (SELECT bc_TAccountContainer_2s0.HiddenTAccountContainerID
col0
          FROM bc_policyproducercode bc_TAccountContainer_2s0
          INNER JOIN (bc_commissionsubplan bc_commissionsubplan_1)
          ON bc_commissionsubplan_1.ID
=bc_TAccountContainer_2s0.CommissionSubPlanID
          AND bc_commissionsubplan_1.ID IN
            (SELECT bc_commissionsubplan_1s0.CommissionSubPlanID col0
            FROM bc_commissionablechargeitem bc_commissionsubplan_1s0
            INNER JOIN (bc_chargepattern bc_chargepattern_1)
            ON bc_chargepattern_1.ID
=bc_commissionsubplan_1s0.ChargePatternID
            AND bc_chargepattern_1.TAccountOwnerPatternID = 1
            AND bc_chargepattern_1.Retired                = 0
            )
          AND bc_commissionsubplan_1.Retired     = 0
          WHERE bc_TAccountContainer_2s0.Retired = 0
          )
        AND bc_TAccountContainer_2.Retired = 0 ) ON
bc_writeoff_1.ID=qRoots0s0s0s1.ForeignEntityID
        AND bc_writeoff_1.Retired          = 0
        )
      AND qRoots0s0s0.Retired = 0
      )
    AND qRoots0s0.Retired = 0
    )
  AND qRoots0.Retired = 0
  )
AND qRoot.Retired = 0 
3.  Observe long run time.

What is the expected output? What do you see instead?
It runs in three seconds on 1.0.79.  Analyze doesn't help.

What version of the product are you using? On what operating system, file
system, and virtual machine?
1.1.111, Windows XP 64-bit

Do you know a workaround?
Run 1.0.79.
How important/urgent is the problem for you?

In your view, is this a defect or a feature request?

Please provide any additional information below.

Original issue reported on code.google.com by wesona...@gmail.com on 28 Apr 2009 at 6:31

[GoogleCodeExporter]

How important/urgent is the problem for you?
Not urgent.  We'd just like to go up from 1.0.79 with our next major release.
In your view, is this a defect or a feature request?
Defect.

Original comment by wesona...@gmail.com on 28 Apr 2009 at 8:54

[GoogleCodeExporter]

Using 1.0.79 on the same database with the same query returns a result in 15 ms 
(COL0
 (no rows, 15 ms)).  1.1.111 runs 10,000 times longer.

Original comment by wesona...@gmail.com on 29 Apr 2009 at 10:14

[GoogleCodeExporter]

Optimize IN(...) for SELECT, DELETE, and UPDATE is on the roadmap
as priority 1, just after the page store is ready. I can't promise
when it will be implemented however:

http://www.h2database.com/html/roadmap.html

Original comment by thomas.t...@gmail.com on 18 Jul 2009 at 10:57

• Changed state: InRoadmap

[GoogleCodeExporter]

Cool.  Thanks.  Version 1.1.111 has been working pretty well for us.

Original comment by wesona...@gmail.com on 18 Jul 2009 at 1:24