Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    6

    Unanswered: Trouble with large query

    Hello,

    Here is the issue. I'll try to keep this as simple as possible. I currently have a table that stores information about a person, and also information about their parents. There are fields for the 'child' and parent1 and parent2 in this table. Sometimes there are no parents, sometimes 1, sometimes 2. Obviously a terrible way to go about storing data, I know. So I am in the process of splitting up that one table into 3 tables. I have the original table with what we'll call the 'child's' data. I created a parent table where each parent has their own record. There is one more table that links the parent and child tables because it is a many-to-many relationship (note: a lot of the 'children' in the child table are siblings, but I don't really care about that now).

    So, my problem is this: To make this new database structure work with the existing application we use to administer this data, I need to query the parents like there are 2 separate parent tables; parent1 and parent2. I have been able to get it to work, however it is somewhat slow and I can't have it be so slow. I'm wondering if someone knows a better way of doing this than the way I did it. The fastest way so far is using inline views, and I have included those below. I tried making them into views, but you can't index views that have subqueries or use min/max so those were REALLY slow. So there lies my problem. I hope I explained it enough so people can understand what I'm trying to do.

    The 'child' table is called 'RegMember' and the child id field is 'idMember'.
    The 'parent' table is called 'RegParent'.
    The 'parent/child' table is called RegParentChild.

    LEFT JOIN
    (
    SELECT
    regParent.idParent AS idParent1,
    regParentChild.idMember AS idMember_Parent1,
    regParentType.parentType AS parent1Type,
    regParent.parentApt AS parent1Apt,
    regParent.parentCity AS parent1City,
    regParent.parentCountry AS parent1Country,
    regParent.parentEmail AS parent1Email,
    regParent.parentFirstName AS parent1FirstName,
    regParent.parentLastName AS parent1LastName,
    regParent.parentMiddleName AS parent1MiddleName,
    regParent.parentPhone AS parent1Phone,
    regParent.parentPoBox AS parent1PoBox,
    regParent.parentState AS parent1State,
    regParent.parentStr1 AS parent1Str1,
    regParent.parentStr2 AS parent1Str2,
    regParent.parentTitle AS parent1Title,
    regParent.parentZip AS parent1Zip,
    regParent.parentZipP4 AS parent1ZipP4
    FROM
    regParent
    INNER JOIN
    regParentChild ON regParent.idParent = regParentChild.idParent
    INNER JOIN
    regParentType ON regParentChild.parentType = regParentType.parentTypeID
    INNER JOIN
    (
    SELECT
    MIN(regParentChild.idParent) as idParent,
    regParentChild.idMember
    FROM
    regParentChild
    GROUP BY
    regParentChild.idMember
    ) minRegParent ON
    (
    regParent.idParent = minRegParent.idParent
    AND
    regParentChild.idMember = minRegParent.idMember
    )
    ) regParent1 ON regMember.idmember = regParent1.idMember_Parent1


    LEFT JOIN
    (
    SELECT
    regParent.idParent AS idParent2,
    regParentChild.idMember AS idMember_Parent2,
    regParentType.parentType AS parent2Type,
    regParent.parentApt AS parent2Apt,
    regParent.parentCity AS parent2City,
    regParent.parentCountry AS parent2Country,
    regParent.parentEmail AS parent2Email,
    regParent.parentFirstName AS parent2FirstName,
    regParent.parentLastName AS parent2LastName,
    regParent.parentMiddleName AS parent2MiddleName,
    regParent.parentPhone AS parent2Phone,
    regParent.parentPoBox AS parent2PoBox,
    regParent.parentState AS parent2State,
    regParent.parentStr1 AS parent2Str1,
    regParent.parentStr2 AS parent2Str2,
    regParent.parentTitle AS parent2Title,
    regParent.parentZip AS parent2Zip,
    regParent.parentZipP4 AS parent2ZipP4
    FROM
    regParent
    INNER JOIN
    regParentChild ON regParent.idParent = regParentChild.idParent
    INNER JOIN
    regParentType ON regParentChild.parentType = regParentType.parentTypeID
    INNER JOIN
    (
    SELECT
    MAX(regParentChild.idParent) as idParent,
    regParentChild.idMember
    FROM
    regParentChild
    WHERE
    (
    SELECT
    COUNT(RPC.idParent)
    FROM
    regParentChild RPC
    WHERE
    RPC.idMember = regParentChild.idMember
    ) > 1
    GROUP BY
    regParentChild.idMember
    ) maxRegParent ON
    (
    regParent.idParent = maxRegParent.idParent
    AND
    regParentChild.idMember = maxRegParent.idMember
    )
    ) regParent2 ON regMember.idmember = regParent2.idMember_Parent2

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Edit to suit:
    Code:
    SELECT *
       FROM RegMember AS c
       LEFT JOIN RegParentChild AS u1
          ON (u1.idMember = c.idMember
          AND u1.idParent = (SELECT MIN(z1.idParent)
             FROM regParentChild AS z1
             WHERE z1.idMember = c.idMember))
       LEFT JOIN RegParent AS p1
          ON (p1.idParent = u1.idParent)
       LEFT JOIN regParentChild AS u2
          ON (u2.idMember = c.idMember
          AND u1.idParent < u2.idParent)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Sep 2003
    Posts
    6
    Hi Pat,

    Thanks for the reply. That runs at about the same speed as the query I posted. Any other ideas that I could try?

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Why bother with the min? How about just saying they not equal?

    Code:
    SELECT *
       FROM RegMember AS c
       LEFT JOIN RegParentChild AS u1
           INNER JOIN RegParent AS p1
                ON p1.idParent = u1.idParent
          ON  u1.idMember = c.idMember
       LEFT JOIN regParentChild AS u2
           INNER JOIN RegParent AS p2
                ON p1.idParent = u2.idParent
          ON u2.idMember = c.idMember
          AND u1.idParent <> u2.idParent
    Also, ensure you have indexes on the idMember and the idParent columns in the tables and idMember, idParent in the regParentChild

    Dave Nance

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You do have indicies on these tables, correct? One index on the ID column for each table, and one on (idMember, idParent) for RegParentChild. Otherwise this demotes to a bunch of table scans, and you'll get old watching them run.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by dav1mo View Post
    Why bother with the min? How about just saying they not equal?
    Most queries like this need some sort of "anchor" parent, I picked one that made the rest of the query easier. Using "not equal" results in a cartesian join for all of the parents for a given child. For one or two parents this is livable, for six or eight parents, it gets crazy. Social service organizations see some kids with more than a dozen "parents" via both fostering and multiple marriages.

    Quote Originally Posted by dav1mo View Post
    Also, ensure you have indexes on the idMember and the idParent columns in the tables and idMember, idParent in the regParentChild
    Yes, indicies are critical to performance of queries like this!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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