Post reply

ISNULL use on joins

  • January 23, 2008 at 6:49 am

    #182284

    I have a question relating to a join that I am trying to do.

    I have a table (t1) with 2 foreign keys id_1 and id_2 and a join table (t2) with id as the pk. On t1, id_1 may be completed but id_2 is always completed. I have set up a my join on the following manner:

    FROM t2

    inner join t1 ON t2.id = ISNULL(t1.id_1, t1.id_2)

    There is a valid reason for using id_2 if id_1 is NULL by the way! 🙂

    I was wondering how efficient this was and what effects using the ISNULL on the join has to the performance of the query?

    Cheers,

  • January 23, 2008 at 7:26 am

    #770903

    Well - using the function in the JOIN will prevent using any indexes to do the work, so it's going to hurt performance. If the tables are small - it might not be the end of the world, but still - you can probably do better.

    You could try using a calculated column, which does the calculation above. Mark it as persisted and then you can build an index based on the calculated column (and whatever else you need to include), and voila - performance is back.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • January 23, 2008 at 7:35 am

    #770910

    Hi Matt,

    Thanks for the information. I will look into this calculated column concept!

    Thanks,

    Al.

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

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