Stored Procedure using huge IO?

Question

Stored Procedure using huge IO?

SQL Galaxy

One Orange Chip

Points: 29472
More actions
April 2, 2013 at 12:44 am

#272978

Hi,
one of the SQL SP written like this as below. it is getting blocking every time due to waiting IO pending for compeletion... please suggest me how to resolve blocking and optimize this sp. is there any alternative way re-write this sp?
each table having total records 2334567
Create procedure [dbo].[DeleteforRetention]
@LastChangeDate DateTime
as
begin
delete from dbo.OperatingLimitHighValues_tracking
where OperatingLimitHighValue_PK_ID in
(select a.OperatingLimitHighValue_PK_ID from OperatingLimitHighValues a
where a.EffectiveTime<@LastChangeDate and EffectiveTime not in
(select MAX(EffectiveTime) from OperatingLimitHighValues b
where b.OperatingLimit_PK_ID=a.OperatingLimit_PK_ID))
delete from dbo.BoundaryHighValues_tracking
where BoundaryHighValue_PK_ID in
( select a.BoundaryHighValue_PK_ID from BoundaryHighValues a
where a.EffectiveTime < @LastChangeDate and EffectiveTime not in
(select max(EffectiveTime) from BoundaryHighValues b
where b.Boundary_PK_ID = a.Boundary_PK_ID))
delete from BoundaryHighValues
where BoundaryHighValue_PK_ID in
(select a.BoundaryHighValue_PK_ID from BoundaryHighValues a
where a.EffectiveTime < @LastChangeDate and EffectiveTime not in
(select max(EffectiveTime) from BoundaryHighValues b
where b.Boundary_PK_ID = a.Boundary_PK_ID))
end
GO
Thanks
ananda

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 1

It might be worth splitting out the derived tables into temp tables then use them to form part of the delete this should reduce the amount of blocking that's going on internally.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

ralm Hall of Fame Points: 3156 More actions · Answer 2

Hi Ananda,

First delete statment can be done using a CTE like below...the below code might not exactly work for your requirement, please test it with select to match the count.

;with OperatingLimitCTE(OperatingLimitHighValue_PK_ID,OperatingLimit_PK_ID,EffectiveTimeRowID)

(SELECT OperatingLimitHighValue_PK_ID,OperatingLimit_PK_ID, dense_rank() Over (partition by OperatingLimit_PK_ID order by EffectiveTime desc) EffectiveTimeRowID

from OperatingLimitHighValues)

delete from dbo.OperatingLimitHighValues_tracking m

inner join OperatingLimitCTE a on m.OperatingLimitHighValue_PK_ID = a.OperatingLimitHighValue_PK_ID

and a.EffectiveTime<@LastChangeDate and a.EffectiveTimeRowID > 1

For the rest two delete statments as suggested by Jason you can have temp table for subqueries and then join them in the main delete statements.

Thanks.

^{[font="Verdana"]Regards,
Rals[/font].}

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass