Not sure what's wrong with my query
The optimizer thinks that this branch will run 699 times, mostly for the same ID value. So it feels good about having a relatively expensive branch to get the results because they are reused and not re-evaluated.
In reality, the ID value changes on each of the 664 rows, so the effort of saving the results is wasted – and what's worse, the huge effort of producing them is repeated 664 times.
Your query text is not only quite complex, it is also littered with data type conversions. These make it incredibly hard for SQL Server to predict things like the above; this might well be the reason why the query is not performing well. If you don't need these conversions throw them out. If you do need them, take action to get your data model fixed.
Oh, and you should also reconsider your use of NOLOCK. Unless you don't care whether the results are correct or not.
• From that I can tell, you are doing a left outer join below, but not using data from the Address table?
Recommendation: Look at the entire query and eliminate joins on any table(s) that are not really needed.
LEFT JOIN [CPPRAFR].[uga].ADDRESS a
NOLOCK ) ON c.id = a.ADDRCUSTNR AND a.ADDREVENTNR=100
• There is another part of the query,
INNER JOIN [VSCSTAGING_AUDIT].[DBO].[DELTASTUBSUGA] AS CL
ON CAST(AB.new_ibscustomernumber AS VARCHAR) =
CAST(CL.ICCCUSTOMERNUMBER AS VARCHAR)
WHERE new_ibscustomernumber IS NOT NULL
I recommend you convert it to a "NOT EXISTS" in the filter? You are doing an INNER JOIN, then adding a "IS NOT NULL"? Here is a good reference for review: