Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2008
    Posts
    2

    Unanswered: Need help with slow recursive SQL

    Hi

    I have a problem, that I seem unable to solve. I need to extract a customer and all those who are in some way associated with him.

    The table looks like this:
    KUN_ASSOCID, - unique id for the row
    CPRNR, - the customers id
    ASS_CPRNR, - id of someone associated with the customer
    ASS_TYPE, - na
    LEVELNR -na

    and has aprox. 1000000 rows

    So for instance, if I were to search for cust. 1, a result could be.

    1-2
    2-3
    3-4
    3-5
    ...
    and so on.

    I thought of this a graph problem, and wrote the follow sql.


    WITH GRP(KUN_ASSOCID, CPRNR, ASS_CPRNR,
    ASS_TYPE, LEVELNR, PATH, COUNTER) AS
    (
    SELECT
    STARTNODE.KUN_ASSOCID,
    STARTNODE.CPRNR,
    STARTNODE.ASS_CPRNR,
    STARTNODE.ASS_TYPE,
    STARTNODE.LEVELNR,
    VARCHAR(VARCHAR(STARTNODE.CPRNR)||VARCHAR(STARTNOD E.ASS_CPRNR)||'Z', 1200),
    1
    FROM UPD.JB8C_KUN_ASSOC_S STARTNODE
    WHERE ( STARTNODE.CPRNR = 15950714
    OR STARTNODE.ASS_CPRNR = 15950714
    )
    UNION ALL
    SELECT
    CHILDNODE.KUN_ASSOCID,
    CHILDNODE.CPRNR,
    CHILDNODE.ASS_CPRNR,
    CHILDNODE.ASS_TYPE,
    CHILDNODE.LEVELNR,
    CASE
    WHEN
    LOCATE(VARCHAR(CHILDNODE.CPRNR)||VARCHAR(CHILDNODE .ASS_CPRNR)||'Z', PARENTNODE.PATH) > 0
    THEN
    'C'
    ELSE
    PARENTNODE.PATH||VARCHAR(CHILDNODE.CPRNR)||VARCHAR (CHILDNODE.ASS_CPRNR)||'Z'

    END,
    PARENTNODE.COUNTER+1
    FROM GRP PARENTNODE, UPD.JB8C_KUN_ASSOC_S CHILDNODE
    WHERE PARENTNODE.PATH ^= 'C'
    AND
    (
    (PARENTNODE.ASS_CPRNR =CHILDNODE.CPRNR)
    OR
    (PARENTNODE.CPRNR =CHILDNODE.ASS_CPRNR)
    OR
    (PARENTNODE.CPRNR = CHILDNODE.CPRNR AND PARENTNODE.ASS_CPRNR ^= CHILDNODE.ASS_CPRNR)
    )
    and
    PARENTNODE.COUNTER < 10
    )
    SELECT DISTINCT G.CPRNR, G.ASS_CPRNR, G.ASS_TYPE,
    G.LEVELNR, D.EJER_PCT, G.KUN_ASSOCID
    FROM GRP G

    In an effort to weed out cycles in the graph, I introduced a path string, which is checked to see if an edge has already been traversed.

    Unfortunately, this does not work very well. I think the problem is this: The path which is build during the recursion is lost when a recursion branch ends. Thus, when another recursion branch is running, there is no information on the what parts of the graph has been visited.

    Can anyone help solve this?

    If it somehow was possible to build an edge list or something similar, the problem could be solved I think.

    I am developing this for DB2 ver.8 on Z/OS.

    Any comments would be very welcome (I should have been done by now )

    Regards
    Karsten

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    The "locate" & concat part of your query the "PATH" column) will be too heavy, I'm afraid.
    Couldn't it be replaced by a "NOT EXISTS" condition such that you never add nodes that were visited before?
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Dec 2008
    Posts
    2
    Hi Peter

    It is true that the creation of the path is rather expensive, however, compared to a traversing a loop, it is cheap.

    I will try to see if I can get the NOT EXISTS to do what I want.

    Regards,
    Karsten

Posting Permissions

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