1 2 3 Previous Next 33 Replies Latest reply: Dec 12, 2012 1:10 AM by Ayham RSS

    Query performance when execute this  looping Query

    Ayham
      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
          <<deleted as its irrelevant>>

          Edited by: Manik on Dec 2, 2012 3:39 PM
          • 2. Re: SQLPLUS hanging when execute this  Query
            BrendanP
            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
              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
                Is there an idea?
                • 5. Re: SQLPLUS hanging when execute this  Query
                  Ayham
                  Still need help.
                  • 6. Re: SQLPLUS hanging when execute this  Query
                    Ayham
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    Still waiting.. Is another way in plsql?.
                                    1 2 3 Previous Next