Post reply

Or statements in a Select join

  • May 31, 2005 at 6:51 am

    #186204

    Does anyone know how to get an inner join which uses or blocks to work efficently as at present it will only look at the clustered index because the or blocks do not have common fields.

    This is the join i am trying to achive

     (NEW1.Application_key  <> isNull(EXISTING1.Application_key,-1)

    AND (( New1.Postcode=  Existing1.Postcode

       AND New1.House_Number=  Existing1.House_Number )

      OR  (New1.Postcode=  Existing1.Postcode

       AND New1.House_Name=  Existing1.House_Name )

      OR  (New1.House_Number=  Existing1.House_Number

       AND New1.Street=  Existing1.Street

       AND New1.Town=  Existing1.Town )

      OR  (New1.House_Name=  Existing1.House_Name

       AND New1.Street=  Existing1.Street

       AND New1.Town=  Existing1.Town)) )

  • May 31, 2005 at 7:58 am

    #561880

    I don't know if it's what you're meaning, but you can define which index the query would use, on the from/join statements...

    SELECT a,b,c FROM TableOne T1 ( INDEX(MyFirstIndex) )

    INNER JOIN TableTwo T2 ( INDEX(MySecondIndex) )

    ON T1.a = T2.a

    Nicolas 

     

     

  • May 31, 2005 at 8:09 am

    #561884

    Queries like this often end up as clustered index scan because there's no index that covers all columns.

    Can we see the rest of the query and the execution plan?

    Also is the speed too slow for your current requirements or you just wanna see if you can make it faster?

  • May 31, 2005 at 8:22 am

    #561888

    usually I have found that an scoring system is the way to approach all those conditions.

    It is something that has to be developed with great deal of knowledge on your data but once you get that figured out you will be happy with performance.

    Good Luck

     


    * Noel

  • May 31, 2005 at 8:23 am

    #561889

    Do you have an exemple of that??

  • May 31, 2005 at 9:21 am

    #561921

    It is purly for speed I was looking at.  I have found the only way arround it to be to split the query into 2 doing the

    AND (( New1.Postcode=  Existing1.Postcode

       AND New1.House_Number=  Existing1.House_Number )

      OR  (New1.Postcode=  Existing1.Postcode

    part first so the postcode index can be used then doing the address matching in the second query so an address index can be used.  Then use a union all to compile the results. 

     

    This has reduced the running time from 45 mins down to 12 seconds

Viewing 6 posts - 1 through 6 (of 6 total)

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