This discussion is archived
1 2 3 Previous Next 33 Replies Latest reply: Dec 11, 2012 11:10 PM by Ayham RSS

Query performance when execute this  looping Query

Ayham Newbie
Currently Being Moderated
this query solved by chris227 and others . Many thanks for them.
Sorting and Grouping -Two months in this query

So, when i execute it on 20 rows work fine but when i execute it on 150 rows. the sql plus hanging no results
drop table temp_value; 
create table temp_value(id number(10),loc1 varchar2(20),loc2 varchar2(20), percentage number(9)); 
  
insert into temp_value values (1         , 0001                , 0010        ,30);
insert into temp_value values (2         , 0001                , 0011        ,30);
insert into temp_value values (3         , 0001                , 0012        ,20);
insert into temp_value values (4         , 0001                , 0013        ,30);
insert into temp_value values (5         , 0001                , 0014        ,40);
insert into temp_value values (6         , 0001                , 0017        ,50);
insert into temp_value values (7         , 0001                , 0018        ,40);
insert into temp_value values (8         , 0001                , 0002        ,80);
insert into temp_value values (9         , 0001                , 0020        ,90);
insert into temp_value values (10        , 0001                , 0004        ,0);
insert into temp_value values (11        , 0001                , 0005        ,0);
insert into temp_value values (12        , 0001                , 0006        ,0);
insert into temp_value values (13        , 0001                , 0009        ,20);
insert into temp_value values (14        , 0010                , 0011        ,30);
insert into temp_value values (15        , 0010                , 0012        ,40);
insert into temp_value values (16        , 0010                , 0013        ,50);
insert into temp_value values (17        , 0010                , 0014        ,20);
insert into temp_value values (18        , 0010                , 0015        ,0);
insert into temp_value values (19        , 0010                , 0016        ,0);
insert into temp_value values (20        , 0010                , 0017        ,0);
insert into temp_value values (21        , 0010                , 0018        ,40);
insert into temp_value values (22        , 0010                , 0002        ,20);
insert into temp_value values (23        , 0010                , 0020        ,10);
insert into temp_value values (24        , 0010                , 0021        ,40);
insert into temp_value values (25        , 0010                , 0004        ,50);
insert into temp_value values (26        , 0010                , 0005        ,70);
insert into temp_value values (27        , 0010                , 0006        ,80);
insert into temp_value values (28        , 0010                , 0008        ,90);
insert into temp_value values (29        , 0011                , 0012        ,30);
insert into temp_value values (30        , 0011                , 0013        ,40);
insert into temp_value values (31        , 0011                , 0014        ,20);
insert into temp_value values (32        , 0011                , 0015        ,40);
insert into temp_value values (33        , 0011                , 0016        ,20);
insert into temp_value values (34        , 0011                , 0017        ,40);
insert into temp_value values (35        , 0011                , 0018        ,60);
insert into temp_value values (36        , 0011                , 0019        ,70);
insert into temp_value values (37        , 0011                , 0002        ,60);
insert into temp_value values (38        , 0011                , 0020        ,0);
insert into temp_value values (39        , 0011                , 0021        ,0);
insert into temp_value values (40        , 0011                , 0003        ,0);
insert into temp_value values (41        , 0011                , 0004        ,0);
insert into temp_value values (42        , 0011                , 0005        ,0);
insert into temp_value values (43        , 0011                , 0006        ,30);
insert into temp_value values (44        , 0011                , 0008        ,20);
insert into temp_value values (45        , 0011                , 0009        ,40);
insert into temp_value values (46        , 0012                , 0013        ,20);
insert into temp_value values (47        , 0012                , 0014        ,0);
insert into temp_value values (48        , 0012                , 0015        ,0);
insert into temp_value values (49        , 0012                , 0016        ,0);
insert into temp_value values (50        , 0012                , 0017        ,30);
insert into temp_value values (51        , 0012                , 0018        ,40);
insert into temp_value values (52        , 0012                , 0002        ,20);
insert into temp_value values (53        , 0012                , 0020        ,0);
insert into temp_value values (54        , 0012                , 0021        ,0);
insert into temp_value values (55        , 0012                , 0004        ,0);
insert into temp_value values (56        , 0012                , 0005        ,0);
insert into temp_value values (57        , 0012                , 0006        ,30);
insert into temp_value values (58        , 0012                , 0007        ,20);
insert into temp_value values (59        , 0012                , 0008        ,0);
insert into temp_value values (60        , 0013                , 0014        ,0);
insert into temp_value values (61        , 0013                , 0015        ,0);
insert into temp_value values (62        , 0013                , 0016        ,3);
insert into temp_value values (63        , 0013                , 0017        ,0);
insert into temp_value values (64        , 0013                , 0018        ,0);
insert into temp_value values (65        , 0013                , 0019        ,0);
insert into temp_value values (66        , 0013                , 0002        ,20);
insert into temp_value values (67        , 0013                , 0020        ,20);
insert into temp_value values (68        , 0013                , 0003        ,0);
insert into temp_value values (69        , 0013                , 0004        ,30);
insert into temp_value values (70        , 0013                , 0005        ,40);
insert into temp_value values (71        , 0013                , 0006        ,50);
insert into temp_value values (72        , 0013                , 0008        ,0);
insert into temp_value values (73        , 0014                , 0016        ,0);
insert into temp_value values (74        , 0014                , 0017        ,0);
insert into temp_value values (75        , 0014                , 0018        ,0);
insert into temp_value values (76        , 0014                , 0021        ,0);
insert into temp_value values (77        , 0014                , 0004        ,0);
insert into temp_value values (78        , 0014                , 0005        ,0);
insert into temp_value values (79        , 0014                , 0006        ,0);
insert into temp_value values (80        , 0014                , 0008        ,0);
insert into temp_value values (81        , 0015                , 0016        ,0);
insert into temp_value values (82        , 0015                , 0017        ,0);
insert into temp_value values (83        , 0015                , 0018        ,0);
insert into temp_value values (84        , 0015                , 0019        ,20);
insert into temp_value values (85        , 0015                , 0002        ,20);
insert into temp_value values (86        , 0015                , 0020        ,60);
insert into temp_value values (87        , 0015                , 0021        ,70);
insert into temp_value values (88        , 0015                , 0003        ,80);
insert into temp_value values (89        , 0015                , 0004        ,60);
insert into temp_value values (90        , 0015                , 0006        ,0);
insert into temp_value values (91        , 0015                , 0007        ,0);
insert into temp_value values (92        , 0015                , 0008        ,0);
insert into temp_value values (93        , 0015                , 0009        ,0);
insert into temp_value values (94        , 0016                , 0017        ,0);
insert into temp_value values (95        , 0016                , 0018        ,0);
insert into temp_value values (96        , 0016                , 0002        ,0);
insert into temp_value values (97        , 0016                , 0020        ,0);
insert into temp_value values (98        , 0016                , 0021        ,70);
insert into temp_value values (99        , 0016                , 0003        ,60);
insert into temp_value values (100       , 0016                , 0004        ,50);
insert into temp_value values (101       , 0016                , 0006        ,40);
insert into temp_value values (102       , 0016                , 0008        ,60);
insert into temp_value values (103       , 0017                , 0018        ,70);
insert into temp_value values (104       , 0017                , 0002        ,40);
insert into temp_value values (105       , 0017                , 0020        ,0);
insert into temp_value values (106       , 0017                , 0021        ,0);
insert into temp_value values (107       , 0017                , 0003        ,0);
insert into temp_value values (108       , 0017                , 0004        ,0);
insert into temp_value values (109       , 0017                , 0005        ,0);
insert into temp_value values (110       , 0017                , 0006        ,0);
insert into temp_value values (111       , 0017                , 0008        ,0);
insert into temp_value values (112       , 0018                , 0019        ,0);
insert into temp_value values (113       , 0018                , 0002        ,60);
insert into temp_value values (114       , 0018                , 0020        ,70);
insert into temp_value values (115       , 0018                , 0021        ,80);
insert into temp_value values (116       , 0018                , 0004        ,20);
insert into temp_value values (117       , 0018                , 0005        ,10);
insert into temp_value values (118       , 0018                , 0006        ,0);
insert into temp_value values (119       , 0018                , 0008        ,0);
insert into temp_value values (120       , 0019                , 0020        ,0);
insert into temp_value values (121       , 0002                , 0020        ,0);
insert into temp_value values (122       , 0002                , 0021        ,0);
insert into temp_value values (123       , 0002                , 0003        ,0);
insert into temp_value values (124       , 0002                , 0004        ,0);
insert into temp_value values (125       , 0002                , 0005        ,0);
insert into temp_value values (126       , 0002                , 0006        ,0);
insert into temp_value values (127       , 0002                , 0007        ,20);
insert into temp_value values (128       , 0002                , 0008        ,20);
insert into temp_value values (129       , 0002                , 0009        ,10);
insert into temp_value values (130       , 0020                , 0021        ,0);
insert into temp_value values (131       , 0020                , 0004        ,0);
insert into temp_value values (132       , 0020                , 0005        ,0);
insert into temp_value values (133       , 0020                , 0006        ,0);
insert into temp_value values (134       , 0020                , 0008        ,0);
insert into temp_value values (135       , 0021                , 0003        ,0);
insert into temp_value values (136       , 0021                , 0004        ,0);
insert into temp_value values (137       , 0021                , 0006        ,0);
insert into temp_value values (138       , 0021                , 0008        ,10);
insert into temp_value values (139       , 0021                , 0009        ,10);
insert into temp_value values (140       , 0003                , 0006        ,20);
insert into temp_value values (141       , 0003                , 0008        ,10);
insert into temp_value values (142       , 0003                , 0009        ,20);
insert into temp_value values (143       , 0004                , 0005        ,0);
insert into temp_value values (144       , 0004                , 0006        ,0);
insert into temp_value values (145       , 0004                , 0008        ,0);
insert into temp_value values (146       , 0005                , 0006        ,0);
insert into temp_value values (147       , 0005                , 0009        ,0);
insert into temp_value values (148       , 0006                , 0007        ,0);
insert into temp_value values (149       , 0006                , 0008        ,0);
insert into temp_value values (150       , 0006                , 0009        ,0);
using this
with connects as (
select distinct
 loc1
,loc2
,connect_by_root(loc1) grp
,percentage per
from 
temp_value
--start with
--percentage != 0
connect by nocycle
(prior loc2 = loc1
or
prior loc1 = loc2
or
prior loc1 = loc1
or
prior loc2 = loc2)
and
percentage != 0
and
prior percentage != 0
)
 select
 loc
,dense_rank() over (order by decode(per,0,1,0), grp) grp
from (
select
 loc
,max(grp) keep (dense_rank first order by per desc, grp) grp
,max(per) keep (dense_rank last order by per nulls first) per
from (
select
 loc1 loc
,grp
,per
from connects
union
select
 loc2
,grp
,per
from connects
)
group by
loc )
order by 2,per desc,1
regards
Ayham

Edited by: Ayham on Dec 1, 2012 9:47 PM

Edited by: Ayham on 03-Dec-2012 01:50

Edited by: Ayham on 03-Dec-2012 01:50
  • 1. Re: SQLPLUS hanging when execute this  Query
    Manik Expert
    Currently Being Moderated
    <<deleted as its irrelevant>>

    Edited by: Manik on Dec 2, 2012 3:39 PM
  • 2. Re: SQLPLUS hanging when execute this  Query
    BrendanP Journeyer
    Currently Being Moderated
    I added your data apart from the zero-percent records and ran this query, which is just a simple tree-walk starting from a single node:
    WITH nod AS (
    SELECT DISTINCT loc1 n
      FROM temp_value
    ), rsf (n, id, n_root) AS (
    SELECT n, 0 id, n n_root
      FROM nod WHERE n = '0002'
     UNION ALL
    SELECT l.loc2, l.id, r.n_root
      FROM rsf r
      JOIN temp_value l
        ON l.loc1 = r.n
       AND l.id != r.id
    ) CYCLE n SET is_cycle TO '1' DEFAULT '0'
    SELECT *
      FROM rsf
     ORDER BY 1, 2
    It returns:
    N              ID N_ROOT I
    ------ ---------- ------ -
    0002            0 0002   0
    0007          127 0002   0
    0008          128 0002   0
    0009          129 0002   0
    
    Elapsed: 00:00:00.00
    My solution involves adding in the reverse pairs, but when I do that the simple query above hangs. Chris's query instead uses an OR condition. I believe that Oracle is not handling the loops correctly. Does anyone have any ideas on this?

    Edited by: BrendanP on 02-Dec-2012 12:09
    I tried restricting the records and found that it does return in smaller cases, so I do not think it's the loops in themselves, but an actual performance problem. It may be better to use an OR condition than my union, but it probably just isn't going to be scalable to tree-walk from every node. I tried also an approach using PL/SQL in which I select a single node at a time from a new nodes table, then use that as the root for a tree-walk and update a network id on the nodes table from the nodes returned, and exclude those from the select for the next root. Unfortunately, up to now I haven't got reasonable performance from that either - don't know why.

    One possible issue is that the test data may be more highly looped than realistic networks which tend to become sparser as they scale up.
  • 3. Re: SQLPLUS hanging when execute this  Query
    Ayham Newbie
    Currently Being Moderated
    Yes , and me i try by this data
    insert into temp_value values  (1,'1','2',10); 
    insert into  temp_value values (2, '1','3',0); 
    insert into  temp_value values (3,'1','4',0); 
    insert into  temp_value values (4,'1','5',0); 
    insert into  temp_value values (5,'1','6',0); 
    insert into  temp_value values (6,'2','3',0); 
    insert into  temp_value  values(7,'2','4',0); 
    insert into  temp_value values (8,'2','5',30); 
    insert into  temp_value values (9,'2','6',0); 
    insert into  temp_value values (10,'3','4',0); 
    insert into  temp_value values (11,'3','5',0); 
    insert into  temp_value values (12,'4','5',40); 
    insert into  temp_value values (13,'4','6',0); 
    insert into  temp_value values (14,'6','7',40);
    insert into  temp_value values (15,'7','2',0);
    insert into  temp_value values (16,'8','2',60);
    insert into  temp_value values (17,'8','3',0);
    insert into  temp_value values (18,'3','1',0);
    insert into  temp_value values (19,'9','6',30);
    insert into  temp_value values (20,'11','2',0);
    insert into  temp_value values (22,'12','3',10);
    insert into  temp_value values (23,'19','3',0);
    insert into  temp_value values (24,'17','3',0);
    insert into  temp_value values (25,'20','3',0);
    the output is fine
    SQL> /
    
    LOC                         GRP
    -------------------- ----------
    1                             1
    2                             1
    4                             1
    5                             1
    8                             1
    12                            2
    3                             2
    6                             3
    7                             3
    9                             3
    
    10 rows selected.
    
    Elapsed: 00:00:00.02
    SQL>
    But when i add more rows. as given above.
    the SQL Hangs.

    regards
    Ayham
  • 4. Re: SQLPLUS hanging when execute this  Query
    Ayham Newbie
    Currently Being Moderated
    Is there an idea?
  • 5. Re: SQLPLUS hanging when execute this  Query
    Ayham Newbie
    Currently Being Moderated
    Still need help.
  • 6. Re: SQLPLUS hanging when execute this  Query
    Ayham Newbie
    Currently Being Moderated
    Many thanks for you i also I think that inner loop continuous looping but I don't exactly where and why? Anyway. I am waiting help from others. they maybe face this problem before.
    regards
    Ayham

    Edited by: Ayham on 02-Dec-2012 22:54
  • 7. Re: SQLPLUS hanging when execute this  Query
    BrendanP Journeyer
    Currently Being Moderated
    No, I don't think that now - that was my first idea, but further analysis suggests that it is a standard performance problem, as noted above.
  • 8. Re: SQLPLUS hanging when execute this  Query
    Ayham Newbie
    Currently Being Moderated
    Ok. If the performance issue . How can I make it faster. Because this is only 150 rows hangs. How it will be if I have 4000 rows. I think there is something missing. Or not correct. The looping for150 it easy can be done in other programming languages.
    Regards
    Ayham
  • 9. Re: SQLPLUS hanging when execute this  Query
    jeneesh Guru
    Currently Being Moderated
    It is not hanging, it is taking time..

    See the number of records generated without OR condition..
    select count(*) c 
              from temp_value b
              start with  b.percentage != 0
              connect by nocycle (prior b.loc1=b.loc1 /*or 
                                  prior b.loc2=b.loc1 or 
                                  prior b.loc1=b.loc2 or 
                                  prior b.loc2 = b.loc2*/)
                 and prior b.percentage != 0
                 and b.percentage != 0;
    
    C
    -
    1090618 
    You can assume now, the number records getting generated with OR conditions...

    I think, there is a major issue in your data model.. You need to think of representing the information in a different manner..
  • 10. Re: SQLPLUS hanging when execute this  Query
    Ayham Newbie
    Currently Being Moderated
    Yes. It take time.Do you have any another idea for represent this data to get good performance.?
    many thanks for all.
    Regards Ayham.
  • 11. Re: SQLPLUS hanging when execute this  Query
    jeneesh Guru
    Currently Being Moderated
    My suggstion is to have a coulmn - BASE_VALUE - and populate it at the time when you are inserting the data..

    To suggest more, need to know from where you are getting the data..How are you inserting the data..What is the meaning of the data...
  • 12. Re: SQLPLUS hanging when execute this  Query
    Ayham Newbie
    Currently Being Moderated
    if I used column for date that mean I just get grouping for specfic time not all rows. The rows inserted by user key in. The loc1 represent location number and also loc2 represent location number and percentage represent the distance meters between location 1 and 2 . So, I want to group the nearest locations together. So, Chairs . Already solved it by for small numbers of rows but when I have more take more time.
    Is there anyway to solve it by plsql.
    regards
    Ayham

    Edited by: Ayham on 03-Dec-2012 00:09
  • 13. Re: SQLPLUS hanging when execute this  Query
    Ayham Newbie
    Currently Being Moderated
    if i represent data in form of martrix , if i insert it as the followoing form the first row is loc 1 and first column is loc
    LOC     1        2       3       4      5      6        7        8         9 
    1     100        20      60     0      0      40      30       0         0
    2     20         100    20     0      0      0        0         0        0
    3     60         20     100    50    0      0        0         0        0
    4     0           0       0       100  50     30      0         0        0
    5     0           0       0       50    100   0        0         0        0
    6     40         0       0       0      0      100     0         0        0 
    7     30         0       0       0      0      0        100      0        0
    8     0           0       0       0      0      0        10        100    40
    9     0           0       0       0      0      0        0          40      100  
    is that can make query performance better and how can i write sql query as the above solved query.

    Many thanks

    regards
    Ayham

    Edited by: Ayham on Dec 3, 2012 2:22 AM

    Edited by: Ayham on Dec 3, 2012 3:07 AM

    Edited by: Ayham on 03-Dec-2012 04:04

    Edited by: Ayham on Dec 3, 2012 8:21 AM
  • 14. Re: SQLPLUS hanging when execute this  Query
    Ayham Newbie
    Currently Being Moderated
    Still waiting.. Is another way in plsql?.
1 2 3 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points