Query in HSQLDB 2.4 much slower than in 1.8

[MTP]

I am looking at migrating a split, multi-user database from HSQLDB 1.8 to HSQLDB 2.4. I have quickly run into an issue, however: a commonly run query that takes a couple of seconds in 1.8 is taking about 3 minutes in 2.4.

The query for 1.8:

Code: Select all

SELECT "OrderNum" 
	FROM "ViewOfOrderStatusHist"
	INNER JOIN "Orders" ON "Orders"."OrderNumber" = "ViewOfOrderStatusHist"."OrderNum"
	INNER JOIN "FilterOrderHistory"  ON "FilterOrderHistory"."StartDate" <= "Orders"."Date" 
		AND CAST( YEAR( "FilterOrderHistory"."EndDate" ) || '-' || MONTH( "FilterOrderHistory"."EndDate" ) || '-' || ( DAY( "FilterOrderHistory"."EndDate" ) + 0.9 ) AS "DATE" ) >= "Orders"."Date"

WHERE "ViewOfOrderStatusHist"."Status" = 'Open' AND "FilterOrderHistory"."ID" = 0

And for 2.4:

Code: Select all

SELECT "OrderNum" 
	FROM "ViewOfOrderStatusHist"
	INNER JOIN "Orders" ON "Orders"."OrderNumber" = "ViewOfOrderStatusHist"."OrderNum"
	INNER JOIN "FilterOrderHistory"  ON "FilterOrderHistory"."StartDate" <= "Orders"."Date" 
		AND DATEADD( 'dd',1,"FilterOrderHistory"."EndDate" ) >= "Orders"."Date"

WHERE "ViewOfOrderStatusHist"."Status" = 'Open' AND "FilterOrderHistory"."ID" = 0

Removing the condition ["ViewOfOrderStatusHist"."Status" = 'Open'] makes the query take only a couple of seconds. Or, removing the join on the filter table makes it only take a couple of seconds. But if both the 'Open' condition and the join to the date filter table are in the query, it takes 3 minutes. I have tried adding indexes on the OrderStatusHist table's Status and Date columns with no improvement.

Any ideas how to get this query to run as fast in 2.4 as it does in the 1.8 engine?

[eremmel]

You need to obtain the execution plan of the query. Update the table index statistics.

[MTP]

Execution plan for the 2.4 query that takes 3 minutes:

Code: Select all

isDistintSelect=[false]
isGrouped=[false]
isAggregated=[false]
columns=[  COLUMN: PUBLIC."ViewOfOrderStatusHist"."OrderNum" nullable  ]
[range variable 1
  join type=INNER
  table=FilterOrderHistory
  cardinality=13
  access=INDEX PRED
  join condition = [index=SYS_IDX_SYS_PK_10254_10255
    start conditions=[
    EQUAL arg_left=[  COLUMN: PUBLIC."FilterOrderHistory"."ID"
] arg_right=[
     VALUE = 0, TYPE = INTEGER]]
  ]
  ][range variable 2
  join type=INNER
  table=Orders
  cardinality=10538
  access=FULL SCAN
  join condition = [index=SYS_idx_sys_pk_10188_10189
    other condition=[
    GREATER_EQUAL arg_left=[  COLUMN: PUBLIC."Orders"."Date"
] arg_right=[ COLUMN: PUBLIC."FilterOrderHistory"."StartDate"
]]
  ]
  ][range variable 3
  join type=INNER
  table=ViewOfOrderStatusHist
  cardinality=9
  access=INDEX PRED
  join condition = [index=SYS_IDX_T_14207
    start condition=[
    EQUAL arg_left=[  COLUMN: PUBLIC."ViewOfOrderStatusHist"."Status"
] arg_right=[
     VALUE = 'Open', TYPE = CHARACTER]
    EQUAL arg_left=[  COLUMN: PUBLIC."ViewOfOrderStatusHist"."OrderNum"
] arg_right=[  COLUMN: PUBLIC."Orders"."OrderNumber"
]]
  ]
  ]]
PARAMETERS=[]
SUBQUERIES[ Stuff for the View "ViewOfOrderStatusHist" ]

For the query without the Status='Open' condition - which runs in a couple of seconds - the plan is the same except for "range variable 3" has a cardinality of 0 without the EQUAL...CHARACTER line.

I am not familiar with execution plans and am not sure what to make of this. I'm also not sure how to update table index statistics.

Any thoughts?

[MTP]

Here is the execution plan for the 1.8 query that runs in a few seconds:

Code: Select all

org.hsqldb.Select@17eb767[
isDistintSelect=[false]
isGrouped=[false]
isAggregated=[false]
columns=[
COLUMN ViewOfOrderStatusHist.OrderNum
]
tableFilters=[
[
org.hsqldb.TableFilter@3b014c
table=[SYSTEM_SUBQUERY]
alias=[ViewOfOrderStatusHist]
access=[INDEX PRED]
index=[SYS_IDX_937]
isOuterJoin=[false]
eStart=[
EQUAL  arg1=[
 COLUMN ViewOfOrderStatusHist.Status] arg2=[
 VALUE = Open, TYPE = VARCHAR]]
eEnd=[
EQUAL  arg1=[
 COLUMN ViewOfOrderStatusHist.Status] arg2=[
 VALUE = oPEN, TYPE = VARCHAR]]
eAnd=[null]
][
org.hsqldb.TableFilter@1d26552
table=[Orders]
alias=[Orders]
access=[INDEX PRED]
index=[SYS_IDX_409]
isOuterJoin=[false]
eStart=[
EQUAL  arg1=[
 COLUMN Orders.OrderNumber] arg2=[
 COLUMN ViewOfOrderStatusHist.OrderNum]]
eEnd=[
EQUAL  arg1=[
 COLUMN Orders.OrderNumber] arg2=[
 COLUMN ViewOfOrderStatusHist.OrderNum]]
eAnd=[null]
][
org.hsqldb.TableFilter@1fa157c
table=[FilterOrderHistory]
alias=[FilterOrderHistory]
access=[INDEX PRED]
index=[SYS_IDX_441]
isOuterJoin=[false]
eStart=[
EQUAL  arg1=[
 COLUMN FilterOrderHistory.ID] arg2=[
 VALUE = 0, TYPE = INTEGER]]
eEnd=[
EQUAL  arg1=[
 COLUMN FilterOrderHistory.ID] arg2=[
 VALUE = 0, TYPE = INTEGER]]
eAnd=[
SMALLER_EQUAL  arg1=[
 COLUMN FilterOrderHistory.StartDate] arg2=[
 COLUMN Orders.Date]]
]]
eCondition=[
AND  arg1=[
 AND  arg1=[
  SET TRUE, WAS: EQUAL  arg1=[
   COLUMN Orders.OrderNumber] arg2=[
   COLUMN ViewOfOrderStatusHist.OrderNum]] arg2=[
  AND  arg1=[
   SET TRUE, WAS: SMALLER_EQUAL  arg1=[
    COLUMN FilterOrderHistory.StartDate] arg2=[
    COLUMN Orders.Date]] arg2=[
   SMALLER_EQUAL  arg1=[
    COLUMN Orders.Date] arg2=[
    CONVERT DATE  arg1=[
     CONCAT  arg1=[
      CONCAT  arg1=[
       CONCAT  arg1=[
        CONCAT  arg1=[
         FUNCTION [stuff with concatenating the date]
]]]]]] arg2=[
 AND  arg1=[
  SET TRUE, WAS: EQUAL  arg1=[
   COLUMN ViewOfOrderStatusHist.Status] arg2=[
   VALUE = Open, TYPE = VARCHAR]] arg2=[
  SET TRUE, WAS: EQUAL  arg1=[
   COLUMN FilterOrderHistory.ID] arg2=[
   VALUE = 0, TYPE = INTEGER]]]]
havingCondition=[null]
groupColumns=[null]
PARAMETERS=[]
SUBQUERIES[
[level=2
hasParams=false
org.hsqldb.Select@9f662e]
[level=1
hasParams=false
org.hsqldb.Select@ed7f5c]]

It jumps out at me that all the "access" types are "INDEX PRED" and nothing is "FULL SCAN". Why, though, is not jumping out.

[eremmel]

With 1.8 the query does:
1. Taken open orders from VOOSH.
2. Find corresponding order from O.
3. Checks the dates from FOH.
I gess the query will take also long time when you change condition into Status <> OPEN

With 2.4 the RDBMS tries to be smart and starts with the smallest table (FOH). It has then to scan all rows fron O to find matches on date. You might try to fool the engine by changing the INNER JOIN FOH into LEFT JOIN FOH. Hope fully it will start with VOOSH again.

Adding an index on O.date might help as well when you search small date ranges.

[MTP]

Thank you very much for explaining that! You are absolutely correct about the 1.8 query taking a long time if Closed orders are included.

The LEFT JOIN didn't help, but putting the filter for 'Open' status into a subquery sped things up to the expected runtime:

Code: Select all

SELECT "OrderNum" 
   FROM (SELECT * FROM "ViewOfOrderStatusHist" WHERE "ViewOfOrderStatusHist"."Status" = 'Open' ) VOSH
   INNER JOIN "Orders" ON "Orders"."OrderNumber" = VOSH."OrderNum"
   INNER JOIN "FilterOrderHistory"  ON "FilterOrderHistory"."StartDate" <= "Orders"."Date" 
      AND DATEADD( 'dd',1,"FilterOrderHistory"."EndDate" ) >= "Orders"."Date"

WHERE "FilterOrderHistory"."ID" = 0