problem query … if not exists (…) update

RichardBrianSmith 2015-10-12 10:18:23

Our most cpu intensive query by far is …
IF NOT EXISTS ( SELECT * FROM dbo.tblFinanceAllocation a INNER JOIN dbo.tblFinancePostAllocation pa ON pa.AllocationID = a.AllocationIDWHERE a.AllocationJobID = @AllocationJobID AND pa.Posted IS NULL) EXEC dbo.spFinancePostAllocationJobEnd @AllocationJobID

… which calls a proc that contains 3 update statements.

I'm not sure where to start, I looked at 'select 1…', then 'update … where not exists' then noticed unusual plans ie: 4 of them every day (we have mdw). However, if the answer is not going to be an index I am stuck :). advise please.

Aaron Bertrand 2015-11-06 15:00:53
So where is the delay / CPU usage? In the SELECT or in one of the three procedures? Can't really advise without seeing the plan(s) to focus on and the code of the procedures if relevant…