SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Delete statement taking too long to execute
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cplusplus
Aged Yak Warrior

541 Posts

Posted - 11/14/2012 :  09:10:20  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/14/2012 :  09:23:48  Show Profile  Reply with Quote
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;
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000