Post reply

Left outer Query optimization

  • August 17, 2005 at 7:45 am

    #88761

    hi

    following is the query to find data present in one column and not in other table

    select distinct([Baseball65K24].[Col005]) from  [Baseball65K24] left outer join  [Baseball65K50] on [Baseball65K50].[Col007] =  [Baseball65K24].[Col005] where [Baseball65K50].[Col007] is null  order by[Baseball65K24].[Col005]

    is there a better way(performance wise) to implement it

     

    thanks

    Amrita

  • August 17, 2005 at 8:05 am

    #582751

    Can you post the full query. Posted query and the following would give the same result set.

    SELECT DISTINCT [Baseball65K24].[Col005])

    FROM

     [Baseball65K24]

    LEFT OUTER JOIN

     [Baseball65K50]

    ON

     [Baseball65K50].[Col007] =  [Baseball65K24].[Col005]

    WHERE

     [Baseball65K50].[Col007] IS NULL

    ORDER BY

     [Baseball65K24].[Col005]

    Also what is the purpose of [Baseball65K50].[Col007] IS NULL. This will make the comparison [Baseball65K50].[Col007] =  [Baseball65K24].[Col005] impossible the join.

    Regards,
    gova

  • August 17, 2005 at 8:11 am

    #582752

    Can you post your execution plan for your query

    Kindest Regards,

    Vasc

  • August 17, 2005 at 8:13 am

    #582753

    LEFT OUTER JOIN

     will make that join possible and will return those rows that are in first table and not in second table  [Baseball65K50].[Col007] IS NULL

    Kindest Regards,

    Vasc

  • August 17, 2005 at 8:25 am

    #582755

    Make sure you have indexes on : [Baseball65K50].[Col007]  and  [Baseball65K24].[Col005]

    By the way I hope you are just testing and that those are not final column names!!!

     


    * Noel

  • August 17, 2005 at 8:28 am

    #582757

    Vasc What I mean was that does't mean any thing. Synatax is correct and the join will not add any thing. It is as good as not having that join.

    Regards,
    gova

  • August 17, 2005 at 8:40 am

    #582763

    govinn,

    Vasc is correct!!

    When you perform a left outer join and  the table column where the join can't  the two tables you get a null  if you then specify the IS NULL condition on the where clause you are effectively retrieving the rows that do not exist on the right side table!!!

     


    * Noel

  • August 17, 2005 at 9:04 am

    #582773

    Yes yes yes

    I have been doing this for zillion times. I need a coffee. Sorry for the confusion.

    My mind was thinking like below even after Vasc mentioned

     [Baseball65K50].[Col007] =  [Baseball65K24].[Col005] AND

     [Baseball65K50].[Col007] IS NULL

    Regards,
    gova

  • August 17, 2005 at 9:08 am

    #582775

    Don't worry I have had my Lack of caffeine more than once too as a matter of fact I should be get a cup now!!

     


    * Noel

  • August 17, 2005 at 11:00 pm

    #582913

    thnx for the reply

    we are using the query to find data from lefttable thats not available on right side table.

    is there a better way of performing it

    amrita

  • August 18, 2005 at 4:28 am

    #582953

    how if we put:

    select col005 from [Baseball65K24] a where not exists( select 1 from [Baseball65K50] b where a.col005 = b.col007)

    would it run faster?

    thanks.

    Leo

  • August 18, 2005 at 6:38 am

    #582982

    thnx a lot

    that was really fast

    jus one thing , can somebody explain me the diff between 2 queries , mine was a join n this is a subquery so shuldnt join be faster but in this case subquery is faster

    Amrita

  • August 18, 2005 at 6:59 am

    #582988

    Exists is not a subquery, it only returns true or false, so no data ia actually fecthed from the clustered index (besides the minimum it takes to make sure you get a hit).

  • August 18, 2005 at 7:55 am

    #583024

    Just want to add that when the number of repeated keys on the inner table is large exists is always a superior choice but when the keys are not repeated too much you could get better performace from the Join

    Therefore this will always depend on the data and you should try both cases to determine what's  best!

     


    * Noel

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply