August 16, 2015 at 11:44 pm
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.
August 17, 2015 at 4:14 am
any suggestions please?
August 17, 2015 at 4:33 am
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)
August 17, 2015 at 4:37 am
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
August 17, 2015 at 4:41 am
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