Stored Procedure timing out

Question

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');

Regarding calling SP's in SqlFiddle, my understanding is that they should be called from within the schema pane. If I have this wrong, please advise. — mOrloff, Jun 02 '16 at 23:59
And BTW, I'm open to completely different approaches (such as temp tables, or ???) if there are benefits to doing so. — mOrloff, Jun 03 '16 at 00:29

score 1 · Accepted Answer · answered Jun 03 '16 at 20:46

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.

score 0 · Answer 2 · answered Jun 02 '16 at 23:46

0

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 )

answered Jun 02 '16 at 23:46

hivie7510

1,246
10
23

Yes, but more than your suggestion. We're trying to build and store a path string via a self-referencing join. Also, I'm wondering whether your suggestion is for a different flavor of SQL, because some of it doesn't look quite right (through my limited MySQL lenses) – mOrloff Jun 03 '16 at 00:26
Yeah, sorry that was for SQL Server, but the principal should hold true. – hivie7510 Jun 03 '16 at 01:12
Yeah, correct principle. The need for doing that **recursively** is where it's getting a little over my depth XP – mOrloff Jun 03 '16 at 15:15

Stored Procedure timing out

2 Answers2