SQL server alternative of OR in where condition

Question

SELECT DISTINCT c.ID FROM tbl_Case c INNER JOIN
tbl_RequestBaseRequest b ON CaseId = c.ID
WHERE AreCalculationsCompleted = 0
AND b.IsApplicantRequest = 1
and c.IsArchived=0
AND (b.ID IN (SELECT DISTINCT ClientRequestId FROM tbl_Response)
OR b.OldClientRequestId IN (SELECT DISTINCT ClientRequestId FROM tbl_Response))

What should be the alternative of OR, this OR is making this query really slow.

Answer 1

SELECT DISTINCT c.ID FROM tbl_Case c 
INNER JOIN tbl_RequestBaseRequest b ON CaseId = c.ID
WHERE AreCalculationsCompleted = 0
AND b.IsApplicantRequest = 1
and c.IsArchived=0
AND exists (SELECT 1 FROM tbl_Response t
  WHERE t.ClientRequestId = b.ID OR t.ClientRequestId = b.OldClientRequestId
)

Answer 2

You might try removing the distinct and being sure you have an index on tbl_Response(ClientRequestId):

SELECT DISTINCT c.ID
FROM tbl_Case c INNER JOIN
     tbl_RequestBaseRequest b
     ON CaseId = c.ID
WHERE AreCalculationsCompleted = 0 AND
      b.IsApplicantRequest = 1 and
      c.IsArchived = 0 AND
      (b.ID IN (SELECT ClientRequestId FROM tbl_Response) OR
       b.OldClientRequestId IN (SELECT ClientRequestId FROM tbl_Response)
      );

Other indexes might help. Also, removing the outer DISTINCT (if it is not necessary will also boost performance). Other indexes might help, but it is not possible to specify because you haven't qualified AreCalculationsCompleted.