Sql-server – SELECT CASE WHEN – Not performing index seek

query-performancesql server

Im using SQL server 2017 and I have a query which does a select and joins from a few tables. When I look at the query plan I notice that the SELECT CASE WHEN part of the query is performing an index scan and its getting back one row but having to iterate through many more. Has anyone come across this scenario? Not been able to get it to perform an index seek. Essentially I look up an account but this account could be in many services and therefore the SELECT CASE WHEN is to go through this and get me back the right ones. Due to this its also granting more memory than it needs to. Planning to run this many times so it might not be an issue now but as I run it more it will become a problem.

Please can anyone shed some light on my errors / understanding?

Statistics are all up to date.

Example snip from my query. The myview is basically a select with a few of the columns from the table.

use DB_test
go

declare @acID int
set @acID = 28


select r.OT_ID as ID,
    aclID.aID as aID,
    r.fDName as DName,
    r.fDA as DA,
    r.fExp as Exp
from myviewDomaR r
 join (
    select
        CASE WHEN a.P_OAcc_ID = 9 then a.OAcc_ID
            ELSE a.P_OAcc_ID
        END as aID,
        a.OAcc_ID as actACID
    from myview acc
)
 aclID on aclID.actACID = r.Acc_OAcc_ID
 where exists (
    select 1
    where aclID.aID = @aclID
)

Query Exec Plan

Best Answer

SQL Server doesn't automatically transform non-sargable expressions to enable using an index.

You can try changing this:

CASE WHEN a.P_OAcc_ID = 9 then a.OAcc_ID ELSE a.P_OAcc_ID END = @aclID

to something like:

(a.P_OAcc_ID = 9 and a.OAcc_ID = @aclID)
or
a.P_OAcc_ID = @aclID