Slow running query

Last Post 30 Jun 2009 09:40 AM by prospec. 2 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
prospec
New Member
New Member

--
29 Jun 2009 01:52 PM
Hi all,
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?

DELETE FROM
[ods2].dbo.CFPAI
WHERE
CMP_KEY IN ( SELECT
(CAST(B.ACCTAIOLD as varchar(7)) + (B.CODEAIOLD)) cmp_key
FROM
[bnodb].dbo.CFPAI B WITH (NOLOCK)
WHERE
B.ACCTAI = 0
AND ACCTAIOLD IS NOT NULL
AND LEFT(B.sv_trans_timestamp,8) = '062909'
AND B.sv_manip_type = 'U'
prospec
New Member
New Member

--
29 Jun 2009 05:34 PM
Thanks for the reply RM. yeah I thought of that too. created a temp table up front then inserted the sub query into it. then joined the delete statement to the temp table. even added an index on the temp table for the join. that plan still used a sort operator. I'm at a loss.
prospec
New Member
New Member

--
30 Jun 2009 09:40 AM
Thanks for the reply gunneyk. the sort is happening at the point the delete is testing the "IN" statement between a top and a nested loop inner join operator. I do have a clustered index on the cmp_key column in the CFPAI table. the sub query returns few rows say a few hundred or so. the time stamp column is varchar and is system generated. I did try using a temp table and join instead but that produced the same sort operator.


Acceptable Use Policy
---