Adding a conditional Join

  • Hello,

    I need to add a join in my select query depending upon a variable @LoggedUser. the Join is to be there if @loggedUser is 1 else i do not need it. Currently I am using two different queries one with join and one without it under If (@LoggedUser check).

    the join is like -

    JOIN (SELECT CAST(CONVERT(VARCHAR(8),Analyst_Effective_date , 1) AS DATETIME) Analyst_Effective_date

    FROM Users us (NOLOCK) JOIN Primary_Analysts (NOLOCK)

    ON User_Count_Id = Analyst_Id_fk

    WHERE User_Count_Id in ((SELECT VALUE FROM dbo.fParseString(@Analyst, ',')) )) Ana

    ON dep.Departure_Code = Ana.Primary_Analyst_Departure_Code_fk

    )

    Any way that the join can be added conditionally in the query so i do not have to write the whole code again for one join.

  • any suggestions please?

  • 1. You do do not want a conditional join as it will kill the query plan.

    You could look into creating a VIEW of the first query and then joining the VIEW for the second query.

    2. Do no use NOLOCK unless you are totally sure the tables will be READONLY while the query is running.

    3. Put an alias on all you column names. eg us.User_Count_Id

    4. Casting is expensive so replace:

    CAST(CONVERT(VARCHAR(8),Analyst_Effective_date , 1) AS DATETIME)

    with

    DATEADD(day, DATEDIFF(day, 0, YourAlias.Analyst_Effective_date), 0)

  • Ken McKelvey (8/17/2015)


    1. You do do not want a conditional join as it will kill the query plan.

    Yup. I'd say leave it as two queries, possibly even make it two procedures which only get called when appropriate.

    4. Casting is expensive so replace:

    CAST(CONVERT(VARCHAR(8),Analyst_Effective_date , 1) AS DATETIME)

    with

    DATEADD(day, DATEDIFF(day, 0, YourAlias.Analyst_Effective_date), 0)

    Yes, with the caveat that it may be better to remove the function entirely and compare date to date directly, with inequality if necessary.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Probably it can provided this join joins no more then one row with the same dep.Departure_Code from ana subquery (see below, move it to WHERE) but it may cost a perfomance penalty.

    Consider refactoring repeating part of the query as inline TVF instead.

    ...

    where isnull(@loggedUser,0)= 1 or (dep.Departure_Code in (

    select Primary_Analyst_Departure_Code_fk

    from (SELECT CAST(CONVERT(VARCHAR(8),Analyst_Effective_date , 1) AS DATETIME) Analyst_Effective_date

    FROM Users us (NOLOCK) JOIN Primary_Analysts (NOLOCK)

    ON User_Count_Id = Analyst_Id_fk

    WHERE User_Count_Id in ((SELECT VALUE FROM dbo.fParseString(@Analyst, ',')) )) --Ana

    ))

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

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