Very bad Delete Performance in my SQL

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 2005 Forums

Transact-SQL (2005)

Very bad Delete Performance in my SQL

Author

Topic

niranjankumark
Posting Yak Master

164 Posts

Posted - 2009-04-09 : 04:23:06

Below SQL deletes the record from the table . but it takes more time , Join are perfect , it has only date filter.

for a given input date it deletes for a single day it takes 3 hours. total record is 40 lakhs records .. like this some more same sql having .. 3 hours is for all SQL .. but all are same only , it uses the same logic deletion .

My analysis is ....
1) i shud remove distinct ,

2) change order of where clause like

WHERE D.SESSION_DATE BETWEEN @START_DATE AND @END_DATE
and B.SESSION_ID =D.SESSION_ID

Here it deletes 3 lak first then in loop it deletes 3 lak .. is this right way ????

Delete statement writes log in LDF , can i avoid this like truncate action ???

Anything can be improved ???

set @commitcnt=300000

DECLARE @continue INT
DECLARE @rowcount INT

-- DELETE DATA FROM VENT_VAR
SET @continue = 1

WHILE @continue = 1
BEGIN
SET ROWCOUNT @commitcnt
BEGIN TRANSACTION
DELETE ENT_VAR
WHERE EVENT_ID IN
(SELECT DISTINCT(B.Event_ID)
FROM VENT B, ESSION D
WHERE B.SESSION_ID =D.SESSION_ID
AND D.SESSION_DATE BETWEEN @START_DATE AND @END_DATE)
SET @rowcount = @@rowcount
COMMIT
IF @rowcount = 0
BEGIN
SET @continue = 0
END
END

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-04-09 : 06:02:43

replace your delete query with this one as In has less performance than inner joins.

delete EV from
ENT_VAR EV
inner join VENT B on EV.Event_ID = B.Event_Id
inner join ESSION D on D.Event_ID = B.Event_Id
where D.SESSION_DATE BETWEEN @START_DATE AND @END_DATE

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources