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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cplusplus
Aged Yak Warrior

567 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  
 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.03 seconds. Powered By: Snitz Forums 2000