CODEWITHSUNDEEP
sqlsql-server
Sachin Prasad
Sachin Prasad

Reputation: 5411

SQL server alternative of OR in where condition

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.

Upvotes: 4

Views: 9753

Answers (6)

Vasily
Vasily

Reputation: 5782

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

Upvotes: 2

Martin Smith
Martin Smith

Reputation: 453908

Often rewriting OR as UNION helps.

SELECT 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)
UNION
SELECT 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.OldClientRequestId IN (SELECT ClientRequestId
                                    FROM   tbl_Response)

You could tidy this up somewhat by encapsulating the join of c and b into a CTE and referencing that in both branches of the UNION instead of repeating it - or materialising into a temp table if that initial join is itself expensive.

Upvotes: 2

mikeagg
mikeagg

Reputation: 751

SELECT DISTINCT c.ID 
FROM tbl_Case c 
INNER JOIN tbl_RequestBaseRequest b ON CaseId = c.ID
INNER JOIN tbl_Response r ON (b.ID = r.ClientRequestId OR b.OldClientRequestId = r.ClientRequestId)

Upvotes: 3

Devart
Devart

Reputation: 122032

SELECT DISTINCT c.id
FROM tbl_Case c
JOIN tbl_RequestBaseRequest b ON CaseId = c.id
WHERE AreCalculationsCompleted = 0
    AND b.IsApplicantRequest = 1
    AND c.IsArchived = 0
    AND EXISTS(
        SELECT *
        FROM tbl_Response r
        WHERE r.ClientRequestId IN (b.id, b.OldClientRequestId)
    )

Upvotes: 3

IVNSTN
IVNSTN

Reputation: 9324

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
)

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1271051

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.

Upvotes: 3

Related Questions