Delete statement taking too long to execute

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Delete statement taking too long to execute

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2012-11-14 : 09:10:20

I have teh following delete statement within SP, total 7 statements within Sp.
It is taking too long to finish.

Is there a better way than this:

DELETE  FROM J_ENC_HLTH_PLANS
   WHERE ENCOUNTER_ID IN
            (SELECT 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');

Thanks a lot for the helpful info.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-14 : 09:23:48

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;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources