>>>Hi,
>>>I have SQL Server 2008, and want to delete 200 000 records using DELETE FROM [table] WHERE [table].[manufacturer_id]=@tnManufacturerId
>>>And this operation is too slow!.. more then 1 minute.
>>>I had try to delete it partial within WHILE.. by 1000 records per transaction, but it bring me no performance.
>>>I had clustered unique index by ID and also my manufacturer_id I have an index.
>>>
>>>Is there way to increase performance of DELETE FROM ?
>>>
>>>Thanks
>>>Denis Gavrikov
>>
>>Try deleting 10K records at once without a loop.
>
>Hi Naomi,
>I wrote SP like this
>
>ALTER PROCEDURE [dbo].[sp_deleteartikelbymanufacturer] @tnManufacturerId int
>AS
>BEGIN
>WHILE (SELECT COUNT(*) FROM ERPBESTBUY.dbo.vbestbuy WHERE ERPBESTBUY.dbo.vbestbuy.manufacturer_pk_id=@tnManufacturerId) > 0
>BEGIN
> DELETE TOP (1000)
> FROM ERPBESTBUY.dbo.vbestbuy WITH (TABLOCK)
> WHERE ERPBESTBUY.dbo.vbestbuy.manufacturer_pk_id=@tnManufacturerId
> CONTINUE
>END
>END
>
>
>it does not increase performance.
BTW, a slightly simpler version will be
declare @Cnt int
set @Cnt = 1
while @Cnt > 0
begin
delete top (10000) -- I suggest 10K at once if you need to delete 200K
...
set @cnt = @@ROWCOUNT
end
Also, no need for TABLOCK hint.
Finally, what is the recovery model for the DB? If you need to perform a mass update, then it can make sense to set it to SIMPLE.
If it's not broken, fix it until it is.
My Blog