CODEWITHSUNDEEP
sql-servert-sql
user1081081
user1081081

Reputation: 1

using OR in sql join is slow

i have a scenario as below.

Employee table

EmpId X_Id  Flag
--------------------------------
1      123     0

Dept table

DeptId D.Name X_Id  Flag
----------------------------------------------
1        a     123    0
2        b     123    1

i need to write a query that fetches the rows based on the flag. ie., if flag is 0 in EmpTable, then get only rows which have flag as 0 and if flag is set to 1 in EmpTable, get all rows (with flag as 0 and 1). To achieve this, i have written the below query

SELECT E.EmpId, E.X_Id, D.Did, D.Dname
FROM EmpTable E INNER JOIN DeptTable D
ON E.X_Id = D.X_Id
AND (D.Flag = 0 or D.Flag = E.Flag)

the above query returns correct rows, but as I see it, using OR is not a good idea. the query is slow. Any alternatives to achieve the same with optimal performance??

Upvotes: 0

Views: 145

Answers (2)

paparazzo
paparazzo

Reputation: 45096

A reach but maybe worth a try.
Sometimes a join settles on one index.
If you split it up then sometimes it will use separate indexes.
Look at the query plan. 

SELECT E.EmpId, E.X_Id, D.Did, D.Dname
FROM EmpTable E 
JOIN DeptTable D1
  ON E.X_Id = D1.X_Id 
JOIN DeptTable D2
  ON D2.Flag = E.Flag
  OR D2.Flag = 0

SELECT E.EmpId, E.X_Id, D.Did, D.Dname
FROM EmpTable E 
JOIN DeptTable D1
  ON E.X_Id = D1.X_Id 
WHERE D2.Flag = E.Flag
   OR D2.Flag = 0

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453608

The only obvious alternative is

SELECT E.EmpId,
       E.X_Id,
       D.Did,
       D.Dname
FROM   EmpTable E
       INNER JOIN DeptTable D
         ON E.X_Id = D.X_Id
            AND D.Flag = E.Flag
UNION
SELECT E.EmpId,
       E.X_Id,
       D.Did,
       D.Dname
FROM   EmpTable E
       INNER JOIN DeptTable D
         ON E.X_Id = D.X_Id
            AND D.Flag = 0

Sometimes rewriting OR as UNION can help performance. If that doesn't help you would need to look at the execution plan to see if you are missing any required indexes for example.

Upvotes: 2

Related Questions