Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2006
    Posts
    47

    Unanswered: distinct on normalized data?

    The title probably isn't very descriptive. Lets say I have two tables:

    Category
    id|name|status

    So we might have:
    1 | Deserts | Active
    2 | Entree | Active

    etc....

    and then we'd have something like

    Recipes
    id|name|status|category|submitter

    which might have
    234| Pasta | 2 | John Doe
    5123| Apple Pie |1 | Granny Doe

    What I want to get is a list of Distinct() Categories, iif they have active children in the Recipes table.

    Ok, knowing that, let's pretend there are 12 million records in the Recipes table, and there are 15 or so in the category table. What I would normally do is something like:

    SELECT Distinct(Name) FROM Recipes where status='A'

    That will eventually return the correct answer, but I can't help but think there is a more efficient way to achieve that.

    Some type of join? Will that actually return the data faster?


    Thanks!

    --james

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    I think you're asking for
    Code:
    select id, name
    from category c
    where status = 'Active' and
          exists (select 1
                  from  recipes r
                  where c.id = r.category);
    This should be quicker than scanning all of the recipes table, since each Active category only needs to find one match. Should go even faster if r.category is indexed.

    ---=cf

  3. #3
    Join Date
    Mar 2006
    Posts
    47

    that seemed to do it

    Chuck,
    Thanks! that seemed to do it. Query time dropped by about a factor of ~7.5.

    EDIT: Make that an increase of a factor of 140 or so (I left some things in my query I shouldnt have)

    I thought there must be an easier way.


    --james
    Last edited by jholder; 06-11-07 at 18:33.

Posting Permissions

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