February 12, 2007 at 3:10 pm
Hi,
I have a job which run every minute to delete records.Every time customer log on
to the application a new record is inserted. during the peak time the number of
records in the table increase upto 400,000 and the job take upto 45 min to delete
90,000 records.Here is the code which is executed
Declare
@RowCount
INT ,
@CountCheck
INT,
@Sql
VARCHAR(4000),
@Datetime
varchar(50),
@RowCnt
INT
begin
set @Datetime= getdate()
SET
@RowCnt = 0
SET
ROWCOUNT 1000
WHILE
1 = 1
BEGIN
Begin tran
delete from dbo.MLS_View_State
WHERE Expires <=@Datetime
SET @CountCheck = @@RowCount
Commit tran
IF @@error <> 0
begin
rollback tran
end
SET @RowCnt = @RowCnt + @CountCheck
IF @CountCheck = 0
BREAK
END
SET ROWCOUNT 0
'Rows Deleted ' + CAST(@RowCnt as VARCHAR(200))
END.
when i run Sp_who2 i notice the status of inserted statements 'Suspended'
Even though i have index on Expires column but the delete statement is doing Table scan
and i think that's why it takes 45 min to delete 90,000 records.
Any idea how can i improved the performance .
Thanks
Hammad
February 12, 2007 at 4:47 pm
In your code you are already committing the transaction before checking for @@error?
Also, your DELETE is locking up the table for the INSERTS. Hence the 'suspended' state.
Also you might want to run the job on a nightly basis rather than during the day while the inserts are happening or even let the job run every 5 mins instead of every minute.
You might want to modify your code as follows:
Declare
@RowCount INT ,
@CountCheck INT,
@Sql VARCHAR(4000),
@Datetime varchar(50),
@RowCnt INT
@Error int
begin
set @Datetime= getdate()
SET @RowCnt = 0
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
Begin tran
delete from dbo.MLS_View_State
WHERE Expires <=@Datetime
SELECT @Error = @@ERROR, @CountCheck = @@RowCount
If @error 0
rollback tran
else
commit tran
SET @RowCnt = @RowCnt + @CountCheck
IF @CountCheck = 0
BREAK
END
SET ROWCOUNT 0
print 'Rows Deleted ' + CAST(@RowCnt as VARCHAR(200))
END
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
February 12, 2007 at 5:13 pm
Um.... let me get this straight... you say the delete job is scheduled to run once every minute and that everytime someone logs in to the app, it creates a new record... then you say it takes 45 minutes to delete 90,000 rows... so, you're saying that 90,000 customers login in a single minute?
--Jeff Moden
February 12, 2007 at 5:27 pm
The table scan could caused by using a VARCHAR variable to store the cutoff date instead of a datetime.
The DELETE may be creating a range lock on the Expired index for all values before @DateTime but you're only deleting 1000 of them. If the Expired index is non-clustered and you have a clustered index on another field, it MAY run with less locking if you make a list of the 1000 key values to be deleted as a separate step.
DECLARE
@runtime DATETIME,
@RowCount INT,
@CountCheck INT,
@ids TABLE (id INT PRIMARY KEY clustered)
SET @runtime = GETDATE()
SET @RowCount = 0
WHILE 1=1 BEGIN
INSERT INTO @ids(id)
SELECT TOP 1000 ID
FROM dbo.MLS_View_State
WHERE Expires <= @runtime
ORDER BY ID
SET @CountCheck = @@ROWCOUNT
IF @CountCheck=0 BREAK
SET @RowCount = @RowCount + @CountCheck
DELETE FROM dbo.MLS_View_State
WHERE ID IN (SELECT ID FROM @ids)
DELETE @ids
END
PRINT 'Rows Deleted ' + CAST(@RowCount AS VARCHAR(20))
February 14, 2007 at 5:39 am
just my 2ct
- check the queryplan your delete query is using ! If it shows a scan, you're in troubles.
- provide an index on column Expires to support the delete.
- If there are foreign keys referencing your dbo.MLS_View_State, they will be checked with each delete action. Provide FK-indexes that match the PK of dbo.MLS_View_State in all dependant objects.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply