>>>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.