June 21, 2012 at 8:30 pm
hello friends,
I’ve created this proc but when I run this it takes a lot of time and I need your help to optimize it…Can somebody please help me in this….
Create procedure usp_del_event @date_prm datetime =null, @daystokeep int=15
as
begin
set nocount on
declare @date datetime
if @date_prm is null
begin
set @date=GETDATE()
end
else
begin
set @date=@date_prm
end
set @date=convert(datetime,CONVERT(char(11),@date))
set @date=DATEADD(DD,-@daystokeep,@date)
create table #PERFORMANCENODE
(Eventid int null, HASHCODE nvarchar (255) null)
create table #event
(eventid int null)
insert into #event
select eventid from EVENT(nolock)
whereUTCEVENTDATE <@date
Insert #PERFORMANCENODE
Select eventid,HASHCODE
from PERFORMANCENODE
where EVENTID in(select eventid from #event)
delete from CHAINENTRYINFO
where EVENTID in(select eventid from #event)
delete from EVENTDETAIL
where EVENTID in(select eventid from #event)
delete from EXCEPTIONNODE
where EVENTID in(select eventid from #event)
delete from PERFORMANCENODE
where EVENTID in(select eventid from #event)
delete from PMEVENTTRACE
where EVENTID in(select eventid from #event)
delete from RESOURCENODE
where EVENTID in(select eventid from #event)
delete DISTRIBUTEDCHAIN
from DISTRIBUTEDCHAIN a,
#PERFORMANCENODE b
where b.HASHCODE=a.HASHCODE
delete CHAINHASHCODE
from CHAINHASHCODE a,
#PERFORMANCENODE b
where b.HASHCODE=a.HASHCODE
delete from event
where EVENTID in(select eventid from #event)
set nocount off
end
June 22, 2012 at 12:48 am
Hi,
What is the size of the tables you are deleting? If they are big delete the data in chunks.
Regards,
Ravi.
Regards,
Ravi.
June 22, 2012 at 5:06 am
Instead of loading temporary tables first, just join to the original tables in the DELETE statements. That eliminates a step of processing right there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 22, 2012 at 7:02 am
If you want to keep the temp tables in place, I would try joining them to your deletion table on eventid instead of using the IN with a subquery and comparing the execution plans of both.
June 25, 2012 at 10:31 am
Hi,
Thanks for your quick response. Could you please help me in writing this script as I am new to this coding stuff?….Thank you in advance..
June 26, 2012 at 5:23 am
Hi,
Deleting the records by joining more than one table is not possible.
for example:
delete from EMP inner join dept
on emp.deptno=dept.deptno and dept.deptno=50
error:
Incorrect syntax near the keyword 'inner'.
thanks,
kumar
June 26, 2012 at 7:07 am
Reddy Ksr (6/26/2012)
Deleting the records by joining more than one table is not possible.for example:
delete from EMP inner join dept
on emp.deptno=dept.deptno and dept.deptno=50
error:
Incorrect syntax near the keyword 'inner'.
thanks,
kumar
Actually, it is. It helps if you learn how to read the BNF description of the statements in BOL.
The correct command is
DELETE FROM Emp
FROM Emp
INNER JOIN dept
ON emp.deptno=dept.deptno AND dept.deptno=50
Notice that FROM Emp is listed twice.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 27, 2012 at 3:34 am
pls try below code
DELETE FROM Emp
where deptno in( select distinct e1.deptno from emp e1
INNER JOIN dept
ON e1.deptno=dept.deptno AND dept.deptno=50)
June 27, 2012 at 3:58 am
Use indexes on those temp tables
Create procedure usp_del_event
@date_prm datetime = null, @daystokeep int = 15
as
begin
set nocount on
declare @date datetime
if @date_prm is null
begin
set @date = GETDATE()
end
else
begin
set @date = @date_prm
end
set @date=convert(datetime,CONVERT(char(11),@date))
set @date=DATEADD(DD,-@daystokeep,@date)
create table #PERFORMANCENODE (HASHCODE nvarchar (255) not null) -- changed code
create table #event (eventid int not null) -- changed code
insert into #event
select eventid
from [EVENT] (nolock)
where UTCEVENTDATE <@date
AND eventid IS NOT NULL
GROUP BY eventid -- changed code
CREATE UNIQUE CLUSTERED INDEX [ucx_eventid] ON #event (eventid) -- new code
Insert #PERFORMANCENODE
Select HASHCODE -- changed code
from PERFORMANCENODE
where EVENTID in(select eventid from #event)
AND HASHCODE IS NOT NULL
GROUP BY HASHCODE
CREATE UNIQUE CLUSTERED INDEX [ucx_HASHCODE] ON #PERFORMANCENODE (HASHCODE) -- new code
delete from CHAINENTRYINFO where EVENTID in(select eventid from #event)
delete from EVENTDETAIL where EVENTID in(select eventid from #event)
delete from EXCEPTIONNODE where EVENTID in(select eventid from #event)
delete from PERFORMANCENODE where EVENTID in(select eventid from #event)
delete from PMEVENTTRACE where EVENTID in(select eventid from #event)
delete from RESOURCENODE where EVENTID in(select eventid from #event)
delete DISTRIBUTEDCHAIN
from DISTRIBUTEDCHAIN a,
#PERFORMANCENODE b
where b.HASHCODE=a.HASHCODE
delete CHAINHASHCODE
from CHAINHASHCODE a,
#PERFORMANCENODE b
where b.HASHCODE=a.HASHCODE
delete from event
where EVENTID in(select eventid from #event)
set nocount off
end
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply