CODEWITHSUNDEEP
mysqlstored-proceduressqlfiddle
mOrloff
mOrloff

Reputation: 2617

Stored Procedure timing out

Trying to collect some heirarchical data to send out to a third party, and was directed to this post.

After attempting to tweak it to my use case on SQL Fiddle, the stored procedure keeps timing out.
So I tried it on locally twice (via PhpMyAdmin).
When I try to reload PMA in the browser after calling the stored procedure, I just get an eternal "waiting for response" spinner (more than 10 or 20 minutes).

I'm presuming there's something wrong with my SP code ???

CREATE TABLE foo
    (`id` int, `name` varchar(100), `parentId` int, `path` varchar(100))
//

INSERT INTO foo (`id`, `name`, `parentId`, `path`)
VALUES (1, 'discrete', 0, NULL),
       (2, 'res', 1, NULL),
       (3, 'smt', 2, NULL),
       (4, 'cap', 1, NULL),
       (5, 'ind', 1, NULL),
       (6, 'smt', 4, NULL),
       (7, 'tant', 6, NULL),
       (8, 'cer', 6, NULL)
//

CREATE PROCEDURE updatePath()
BEGIN
DECLARE cnt, n int;
    SELECT COUNT(*) INTO n FROM foo WHERE parentId = 0;
    UPDATE foo a, foo b SET a.path = b.name WHERE b.parentId IS NULL AND a.parentId = b.id;
    SELECT count(*) INTO cnt FROM foo WHERE path IS NULL;
    while cnt > n do
        UPDATE foo a, foo b SET a.path = concat(b.path, '|', b.id) WHERE b.path IS NOT NULL AND a.parentId = b.id;
        SELECT count(*) INTO  cnt FROM foo WHERE path IS NULL;
    end while;
END//

EDIT
Expected results:

VALUES (1, 'discrete', 0, '1'),
       (2, 'res', 1, '1|2'),
       (3, 'smt', 2, '1|2|3'),
       (4, 'cap', 1, '1|4'),
       (5, 'ind', 1, '1|5'),
       (6, 'smt', 4, '1|4|6'),
       (7, 'tant', 6, '1|4|6|7'),
       (8, 'cer', 6, '1|4|6|8');

Upvotes: 2

Views: 148

Answers (2)

mOrloff
mOrloff

Reputation: 2617

After a good nights sleep, I took @Drew's lead and I went through it one pc at a time.
Got it working. Here's where I'm leaving it:

CREATE TABLE foo
    (`id` int, `name` varchar(100), `parentId` int, `path` varchar(100))
//

INSERT INTO foo
    (`id`, `name`, `parentId`, `path`)
VALUES
    (1, 'dscr', 0, NULL),
    (2, 'res', 1, NULL),
    (3, 'smt', 2, NULL),
    (4, 'cap', 1, NULL),
    (5, 'ind', 1, NULL),
    (6, 'chp', 4, NULL),
    (7, 'tant', 6, NULL),
    (8, 'cer', 6, NULL)
//

CREATE PROCEDURE updatePath()
BEGIN
DECLARE cnt, n int;

    SELECT COUNT(*) INTO n FROM foo WHERE parentId = 0; -- n is now 1
    SELECT COUNT(*) INTO cnt FROM foo WHERE path IS NULL; -- cnt is now 8

    UPDATE foo child, foo parent        -- each child now has its parent and own ID's in the path
      SET child.path = CONCAT(parent.id, '|', child.id)
      WHERE parent.parentId = 0
      AND child.parentId = parent.id;

    WHILE cnt > n DO
        UPDATE foo child, foo parent    -- concat parent's path and own ID into each empty child's path
          SET child.path = concat( parent.path,'|',child.id ) 
          WHERE parent.path IS NOT NULL 
          AND child.parentId = parent.id;

        SELECT COUNT(*) INTO  cnt  -- decrement cnt
          FROM foo 
          WHERE path IS NULL;

    END WHILE;

    UPDATE foo  -- set path for any top-level categories
      SET path = id
      WHERE path IS NULL;

END//

call updatePath()//

Feel free to critique.
Hope this helps someone else some time.

Upvotes: 1

hivie7510
hivie7510

Reputation: 1246

Are you trying to do a self-referencing join to create the hierarchy?

select a.name, parentName = b.name from foo a , outer join foo b on ( a.id = b.parentId )

Upvotes: 0

Related Questions