How to ensure use of an index?

Question

I have the following query:

SELECT cp.ID_Case
FROM dbo.CaseParty cp (NOLOCK)
JOIN dbo.Client cli (NOLOCK) ON CASE
     WHEN cli.ClientType = 'atty' AND cp.ID_ClientAttorney = cli.ID_Client THEN 1
     WHEN cli.ClientType = 'adj' AND cp.ID_ClientAdjuster = cli.ID_Client THEN 1
     ELSE 0
     END = 1
WHERE cli.ID_Client = @ID_Client

When I run it, the execution plan does a clustered index scan on the primary key index for table CaseParty. There are about 700k rows in that table, so the query takes a significant amount of time. I acknowledge that the join, requiring a CASE expression, is less than ideal, but that is what I have to work with.

I've tried creating all sorts of indexes, but it just won't use them:

CREATE INDEX IX_CaseParty_ClientAttorneyAdjuster ON dbo.CaseParty (ID_ClientAttorney, ID_ClientAdjuster);
CREATE INDEX IX_CaseParty_ClientAttorney ON dbo.CaseParty (ID_ClientAttorney);
CREATE INDEX IX_CaseParty_ClientAdjuster ON dbo.CaseParty (ID_ClientAdjuster);

I've also tried changing the CASE join to OR (example below) but it changes nothing.

SELECT cp.ID_Case
FROM dbo.CaseParty cp (NOLOCK)
JOIN dbo.Client cli (NOLOCK) 
    ON (cli.ClientType = 'atty' AND cp.ID_ClientAttorney = cli.ID_Client)
    OR (cli.ClientType = 'adj' AND cp.ID_ClientAdjuster = cli.ID_Client)
WHERE cli.ID_Client = @ID_Client

What am I doing wrong?

Answer 1

The seek on dbo.CaseParty needs to be either on the ID_ClientAttorney column or the ID_ClientAdjuster column depending on the ClientType value.

Assuming you have appropriate indexes for both these seeks then you could do

SELECT ISNULL(cp1.ID_Case, cp2.ID_Case) AS ID_Case
FROM   dbo.Client cli
       LEFT JOIN dbo.CaseParty cp1
              ON cp1.ID_ClientAttorney = cli.ID_Client
                 AND cli.ClientType = 'atty'
       LEFT JOIN dbo.CaseParty cp2
              ON cp2.ID_ClientAdjuster = cli.ID_Client
                 AND cli.ClientType = 'adj'
WHERE  cli.ID_Client = @ID_Client
       AND ( cp1.ID_ClientAttorney IS NOT NULL
              OR cp2.ID_ClientAdjuster IS NOT NULL ) 

Or

SELECT cp.ID_Case
FROM   dbo.Client cli
       CROSS APPLY (SELECT cp1.ID_Case
                    FROM   dbo.CaseParty cp1
                    WHERE  cp1.ID_ClientAttorney = cli.ID_Client
                           AND cli.ClientType = 'atty'
                    UNION ALL
                    SELECT cp2.ID_Case
                    FROM   dbo.CaseParty cp2
                    WHERE  cp2.ID_ClientAdjuster = cli.ID_Client
                           AND cli.ClientType = 'adj') cp
WHERE  cli.ID_Client = @ID_Client