Hello,
While researching a performance issue when using user defined
variables ( @variable ) I ran into a infinite loop.
Running the query below without a variable the query runs in a few
miliseconds. When running it with a user define variable the server
runs for several hours. The EXPLAIN command shows some weird results..
(database available at request)
set @SourceGroupType ='GR_MED';
explain
select stam.naam, stam.id from stam where stam.id in (
select rel.id2 as id /*IT_MED - GR_MED - GR_AT - IT_AT */
from relaties as rel
where rel.type2 = 'IT_AT2' and rel.id1 in (
select subrel.id2
from relaties as subrel
where subrel.type2 = 'GR_AT2' and subrel.id1 in (
select subsubrel.id1
from relaties as subsubrel
where subsubrel.ID2 = 402 and subsubrel.type1 = @SourceGroupType
)
)
)
SELECT
STAM.NAAM,
STAM.ID
FROM PUBLIC.STAM
/* PUBLIC.STAM_ID: ID IN(SELECT
REL.ID2 AS ID
FROM PUBLIC.RELATIES REL
/++ PUBLIC.RELATIES_ID1: ID1 IN(SELECT
SUBREL.ID2
FROM PUBLIC.RELATIES SUBREL
/++ PUBLIC.RELATIES_ID1: ID1 IN(SELECT
SUBSUBREL.ID1
FROM PUBLIC.RELATIES SUBSUBREL
/++ PUBLIC.RELATIES_ID2: ID2 = 402 ++/
WHERE (SUBSUBREL.ID2 = 402)
AND (SUBSUBREL.TYPE1 = @SOURCEGROUPTYPE))
++/
WHERE (SUBREL.TYPE2 = 'GR_AT2')
AND (SUBREL.ID1 IN(
SELECT
SUBSUBREL.ID1
FROM PUBLIC.RELATIES SUBSUBREL
/++ PUBLIC.RELATIES_ID2: ID2 = 402 ++/
WHERE (SUBSUBREL.ID2 = 402)
AND (SUBSUBREL.TYPE1 = @SOURCEGROUPTYPE))))
++/
WHERE (REL.TYPE2 = 'IT_AT2')
AND (REL.ID1 IN(
SELECT
SUBREL.ID2
FROM PUBLIC.RELATIES SUBREL
/++ PUBLIC.RELATIES_ID1: ID1 IN(SELECT
SUBSUBREL.ID1
FROM PUBLIC.RELATIES SUBSUBREL
/++ PUBLIC.RELATIES_ID2: ID2 = 402 ++/
WHERE (SUBSUBREL.ID2 = 402)
AND (SUBSUBREL.TYPE1 = @SOURCEGROUPTYPE))
++/
WHERE (SUBREL.TYPE2 = 'GR_AT2')
AND (SUBREL.ID1 IN(
SELECT
SUBSUBREL.ID1
FROM PUBLIC.RELATIES SUBSUBREL
/++ PUBLIC.RELATIES_ID2: ID2 = 402 ++/
WHERE (SUBSUBREL.ID2 = 402)
AND (SUBSUBREL.TYPE1 = @SOURCEGROUPTYPE))))))
*/
WHERE STAM.ID IN(
SELECT
REL.ID2 AS ID
FROM PUBLIC.RELATIES REL
/* PUBLIC.RELATIES_ID1: ID1 IN(SELECT
SUBREL.ID2
FROM PUBLIC.RELATIES SUBREL
/++ PUBLIC.RELATIES_ID1: ID1 IN(SELECT
SUBSUBREL.ID1
FROM PUBLIC.RELATIES SUBSUBREL
/++ PUBLIC.RELATIES_ID2: ID2 = 402 ++/
WHERE (SUBSUBREL.ID2 = 402)
AND (SUBSUBREL.TYPE1 = @SOURCEGROUPTYPE))
++/
WHERE (SUBREL.TYPE2 = 'GR_AT2')
AND (SUBREL.ID1 IN(
SELECT
SUBSUBREL.ID1
FROM PUBLIC.RELATIES SUBSUBREL
/++ PUBLIC.RELATIES_ID2: ID2 = 402 ++/
WHERE (SUBSUBREL.ID2 = 402)
AND (SUBSUBREL.TYPE1 = @SOURCEGROUPTYPE))))
*/
WHERE (REL.TYPE2 = 'IT_AT2')
AND (REL.ID1 IN(
SELECT
SUBREL.ID2
FROM PUBLIC.RELATIES SUBREL
/* PUBLIC.RELATIES_ID1: ID1 IN(SELECT
SUBSUBREL.ID1
FROM PUBLIC.RELATIES SUBSUBREL
/++ PUBLIC.RELATIES_ID2: ID2 = 402 ++/
WHERE (SUBSUBREL.ID2 = 402)
AND (SUBSUBREL.TYPE1 = @SOURCEGROUPTYPE))
*/
WHERE (SUBREL.TYPE2 = 'GR_AT2')
AND (SUBREL.ID1 IN(
SELECT
SUBSUBREL.ID1
FROM PUBLIC.RELATIES SUBSUBREL
/* PUBLIC.RELATIES_ID2: ID2 = 402 */
WHERE (SUBSUBREL.ID2 = 402)
AND (SUBSUBREL.TYPE1 = @SOURCEGROUPTYPE))))))
--
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.
