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 )