Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts

    stored proc slow while ...

    Code:
    CREATE TABLE tree (CHILD_ID INTEGER NOT NULL,PARENT_ID INTEGER,TOP_PRIORITY_ORDER INTEGER DEFAULT NULL, INDEX (CHILD_ID,PARENT_ID)) ENGINE=MYISAM;
    CREATE INDEX myIndex ON tree (parent_id);
    Code:
    CREATE TABLE tmp_NestedSetModel
                (NestedSetModel_top INTEGER NOT NULL,
                 CHILD_ID INTEGER NOT NULL,
                 lft INTEGER,
                 rgt INTEGER,
                 PARENT_ID INTEGER,
                 ORDER_BY INTEGER,
                 INDEX  (CHILD_ID,NestedSetModel_top)) ENGINE=MYISAM;
    Slow part of proc.

    Basicaly takes one record from tree tab and puts one into
    tmp_NestedSetModel table.

    It was all ok until I putted like 130.000 records into tree table.
    It takes like 1-2 or more sec per one while loop.




    Code:
    WHILE @counter <= (@max_counter) DO
    
    	SELECT count(*) into v1
      FROM tmp_NestedSetModel AS S1, tree AS T1
      WHERE S1.CHILD_ID = T1.PARENT_ID
      AND S1.NestedSetModel_top = @current_top;
    
      /*call debug_insert('t',@current_top);*/
    
    
    
      IF (v1 > 0) THEN
    
        INSERT INTO tmp_NestedSetModel
                      SELECT (@current_top + 1),
                             MIN(T1.CHILD_ID),
                             @counter,
                             NULL,
                             NULL,
                             t1.top_priority_order
                             FROM tmp_NestedSetModel AS S1, tree AS T1
                             WHERE S1.CHILD_ID = T1.PARENT_ID
                             AND S1.NestedSetModel_top = @current_top;
    
    
    
    
    		DELETE FROM tree WHERE CHILD_ID = (SELECT CHILD_ID FROM tmp_NestedSetModel WHERE NestedSetModel_top = @current_top + 1);
    
    
    		SET @counter = @counter + 1;
    		SET @current_top = @current_top + 1;
    
    	ELSE
    
    
    
    
        UPDATE tmp_NestedSetModel
        SET rgt = @counter, NestedSetModel_top = -NestedSetModel_top
        WHERE NestedSetModel_top = @current_top;
    
    
    
        SET @counter = @counter + 1;
        SET @current_top = @current_top - 1;
    	END IF;
    END WHILE;
    Any thought ?
    Last edited by BubikolRamios; 06-26-2011 at 02:06 AM.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #2
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    Even vorse. After various indexs trial/error/esplain/sucess, I noticed that this processes like 100 records in a couple of seconds, then it slows down exponentialy. So 1000 records comes up like in 2 -3 hours !!!
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,519
    Thanks
    77
    Thanked 4,381 Times in 4,346 Posts
    Maybe if you told us what the purpose of this is?

  • #4
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    converting adjacent to nested set hirearchical model
    like here:
    http://data.bangtech.com/sql/nested_set_treeview.htm

    doh I doubt that info will make any difference.I fill like mysql is eating up memory predefined/not defined/default in various variables in my.cnf, with repeated delete, insert,update ...
    Last edited by BubikolRamios; 06-26-2011 at 11:41 PM.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #5
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,963
    Thanks
    120
    Thanked 76 Times in 76 Posts
    so I added this into upper while, trying to do something on every 100 records
    processed. No effect.
    Code:
      IF (MOD(@counter, 100) = 0) then
        RESET QUERY CACHE;
        FLUSH TABLES tree,tmp_NestedSetModel;
        // adds some info into debug table
        call debug_insert('convert_adjacent_to_nested','RESET QUERY CACHE');
      END IF;
    this is the result of upper call debug ....

    You see how time needed to process 100 records is increasing.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •