I have a delete query that is running a bit slow. I think the reason for the poor performance is because the delete statement is creating a sort operator which has a cost of 16%. I've tried everything to get rid of the sort operator with no success. Does anyone have any additional ideas on how to get rid of the sort operator?
CMP_KEY IN ( SELECT
(CAST(B.ACCTAIOLD as varchar(7)) + (B.CODEAIOLD)) cmp_key
[bnodb].dbo.CFPAI B WITH (NOLOCK)
B.ACCTAI = 0
AND ACCTAIOLD IS NOT NULL
AND LEFT(B.sv_trans_timestamp,8) = '062909'
AND B.sv_manip_type = 'U'