stored proc while loop,gradualy slows down execution

[BubikolRamios]

This is the peace of code that is slow. debug_insert is only there to analyse things. Basicaly I take one record from tree table and put it to tmp_NestedSetModel table.

this is the result of debug

http://www.shrani.si/?a/C2/j3vA7Y/tmp.jpg

You see how time needed to process 100 records is increasing.

any tip velcome.

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;

  /*debug & try to sort things (unsuccesfuly)*/ 
  IF (MOD(@counter, 100) = 0) then
    RESET QUERY CACHE;
    FLUSH TABLES tree,tmp_NestedSetModel;
    call debug_insert('convert_adjacent_to_nested','RESET QUERY CACHE');
  END IF;

END WHILE;