I have a stored proc, which basicaly takes 1 record from source table (tree) and puts one into destination table (tmp_NestedSetModel). Both memory tables, created at start of proc. All ok until I putted like 130.000 records into source table, that is , then I noticed that problem exists.
Here is relevant part of proc:
/**********************************************************************/
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;
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;
/*debugging inf & attemp, with no effect, to do something about that */
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;
/**********************************************************************/
Here is pic of what can be pulled out of table filled by debug_insert proc,
you see how time needed to process 100 records increases.
http://www.shrani.si/?a/C2/j3vA7Y/tmp.jpg
Rightmost column, time diff to process 100 records.
The server is on very fast machine.
Edited 1 time(s). Last edit at 06/28/2011 06:17AM by Miran Cvenkel.
