Slow select with 2 correlated subqueries.

Hi list,

I am using MaxDB 7.5.0.18 on Windows currently and ran into the problem when
my query running for minutes to return result set of 30 rows. First query
(not as slow) looks like:

select T1.DOWN_ID, T2.AN_ID
from RELATIONSHIP_N T1, ENTITY_N T2
where T1.UP_ID = 1
and T2.AN_ID in
(
    select VCT1.ID2
    from VALUE_N VCT1
    where VCT1.F_MAX = 3
    and VCT1.ID1 = T1.DOWN_ID
)

This query runs 7-9 seconds on my desktop (using 100% CPU and not too much
I/O). I should say that all tables involved in this query are very small
(less then 1000 rows each). It is clear that it's not easy to optimize such
a
query with correlated sub-select in condition, but other databases I tryed
(Ingres, for instance) execute this very same query on the same hardware in
only few milliseconds.

But the real problem starts when I add subselect column in select as
follows:

select T1.DOWN_ID, T2.AN_ID,
       (
           select SCT1.F_VAL
           from VALUE_N SCT1
           where T1.DOWN_ID = SCT1.ID_1
           and T2.AN_ID = SCT1.ID_2
       )
from RELATIONSHIP_N T1, ENTITY_N T2
where T1.UP_ID = 1
and T2.AN_ID in
(
    select VCT1.ID_2
    from VALUE_N VCT1
    where VCT1.F_MAX = 3
    and VCT1.ID_1 = T1.DOWN_ID
)

This one executes in 2-3 minutes, which I cannot explain. I thought it's
because subselect columns are slow in MaxDB but was wrong because if I
get rid of subselect in where-clause (modifying it to use joins instead)
it works really fast (several milliseconds). It looks like having both
correlated subqueries together causes such a slow down.

Does anyone has idea what might be wrong with it? Can it be optimizer
issue?

Best regards.
Alexei Novakov

PS Before running all queries and explains I updated statistics (estimate
90%).
Here are the results of explain for queries
1)

|OWNER   |TABLENAME       |COLUMN_OR_INDEX    |STRATEGY
|PAGECOUNT|
|--------|----------------|-------------------|-----------------------------
-----------|---------|
|        |T2              |ENTITY_IDX_TYPE    |INDEX SCAN
|        4|
|        |                |                   |ONLY INDEX ACCESSED
|         |
|        |T1              |RELATIONSHIP_IDX_UP|JOIN VIA INDEXED COLUMN
|        8|
|INTERNAL|TEMPORARY RESULT|                   |EQUAL CONDITION FOR KEY
COLUMN          |      500|
|        |                |SYSKEY             |     (USED KEY COLUMN)
|         |
|        |VCT1            |                   |NO STRATEGY NOW (ONLY AT
EXECUTION TIME)|         |
|        |SQLCURS_2       |                   |     RESULT IS COPIED   ,
COSTVALUE IS  |         |


2)

| OWNER  |TABLENAME       |COLUMN_OR_INDEX    |STRATEGY
|PAGECOUNT|
|------- |--------------- |------------------
|----------------------------------------|---------|
|        |T2              |ENTITY_IDX_TYPE    |INDEX SCAN
|        4|
|        |                |                   |ONLY INDEX ACCESSED
|         |
|        |T1              |RELATIONSHIP_IDX_UP|JOIN VIA INDEXED COLUMN
|        8|
|INTERNAL|TEMPORARY RESULT|                   |EQUAL CONDITION FOR KEY
COLUMN          |      500|
|        |                |SYSKEY             |     (USED KEY COLUMN)
|         |
|        |SCT1            |                   |NO STRATEGY NOW (ONLY AT
EXECUTION TIME)|         |
|        |VCT1            |                   |NO STRATEGY NOW (ONLY AT
EXECUTION TIME)|         |
|        |SQLCURS_2       |                   |     RESULT IS COPIED   ,
COSTVALUE IS  |  > 2 E10|

One weird thing here is use of ENTITY_IDX_TYPE index on T2 table. This index
is set on ID_TYPE column of ENTITY_N table, but this column is not used
in the queries.


-- 
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/maxdb?unsub=gcdm-maxdb@xxxxxxxxxxx