Hi,
I'm not sure if the 'infinite loop' is just a long loop. I can't reproduce
the problem. Could you provide a complete, self contained test case
(including create table and index statements, data - so I can repeat the
problem)? Please try to use the example below as a starting point.
drop all objects;
create table stam(id int primary key, naam int);
create table relaties(id1 int primary key, id2 int,
type1 varchar, type2 varchar);
insert into stam select x, x
from system_range(1, 100);
insert into relaties
select x, x, x / 100, mod(x, 100)
from system_range(1, 10000);
set @SourceGroupType ='0';
select stam.naam, stam.id from stam where stam.id in (
select rel.id2 as id
from relaties as rel
where rel.type2 = '4' and rel.id1 in (
select subrel.id2
from relaties as subrel
where subrel.type2 = '4' and subrel.id1 in (
select subsubrel.id1
from relaties as subsubrel
where subsubrel.ID2 = 4 and subsubrel.type1 = @SourceGroupType
)
)
);
select stam.naam, stam.id from stam where stam.id in (
select rel.id2 as id
from relaties as rel
where rel.type2 = '4' and rel.id1 in (
select subrel.id2
from relaties as subrel
where subrel.type2 = '4' and subrel.id1 in (
select subsubrel.id1
from relaties as subsubrel
where subsubrel.ID2 = 4 and subsubrel.type1 = '0'
)
)
);
Regards
On Tuesday, September 27, 2011, Rinse Lemstra wrote:
> 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]<javascript:;>
> .
> To unsubscribe from this group, send email to
> [email protected] <javascript:;>.
> For more options, visit this group at
> http://groups.google.com/group/h2-database?hl=en.
>
>
--
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.
