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