There are a few different ways that you can try to speed up things - everything from deleteing in small chunks to using a join instead of the IN clause.
The main culprit may be the UDF TO_NUMBER (assuming that is a UDF). If you can get rid of that that would certainly speed up things.
My favorite though, is as follows - it is a little wordy, but it lets you look at what you are going to delete before you delete.CREATE TABLE #tmpIds(Id INT PRIMARY KEY CLUSTERED);
INSERT INTO #tmpIds
SELECT DISTINCT ENCOUNTER_ID
FROM T_ENCOUNTERS md,
teds_stg.J_STG_SS_ENC_NUMBERS st
WHERE TO_NUMBER(md.SOURCE_ENCOUNTER_ID) = st.LPACCT
AND TO_NUMBER(md.SOURCE_ENCOUNTER_ID) = st.LPACCT
AND md.SOURCE_ID = 'SS4|SS16';
DELETE FROM j
FROM
J_ENC_HLTH_PLANS j
INNER JOIN #tmpIds t ON t.Id = j.ENCOUNTER_ID;
DROP TABLE #tmpIds;
Delete statement taking too long to execute
Posted - 11/14/2012 : 09:10:20
