Performance question on program

Discussion in 'OT Technology' started by Coottie, Aug 16, 2007.

  1. Coottie

    Coottie BOOMER......SOONER OT Supporter

    Joined:
    Jun 6, 2006
    Messages:
    32,407
    Likes Received:
    0
    Location:
    OKC
    Had a question about performance today and I'm not sure what the best way to attack the problem is.

    We have 2 tables where we can perform a left outer join to get the data set we need. Is it better to do this left outer join or is it better to do get 2 different data sets then do a nested loop to find the matching entries?

    I know nested, linear search loops are big O (n^2) but doesn't a left outer join have to do the same thing?? So which one would be faster and why??
     
  2. Nocera

    Nocera ...

    Joined:
    Aug 9, 2000
    Messages:
    1,307
    Likes Received:
    0
    Location:
    Long Island, NY
    It depends on the join condition. Say that you have:

    Code:
    SELECT *
    FROM users u
    LEFT OUTER JOIN addresses a ON a.id = u.address_id
    
    with a clustered index on the "id" column in the addresses tables. The lookup of the address that matches the user would then be constant time and the total search would be O(n) where n = number of rows in the user table.

    If you use a join condition that does not have a good index cover, the lookup could result in something as bad as a full table scan which would be equivalent to a nested loop.
     
  3. Coottie

    Coottie BOOMER......SOONER OT Supporter

    Joined:
    Jun 6, 2006
    Messages:
    32,407
    Likes Received:
    0
    Location:
    OKC
    Hmmm interesting...thanks!

    I've never heard of a clustered index though. I'll google tomorrow when I'm more coherent.
     

Share This Page