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

1. You don't need a hint.

2. How many records correspond to one manufacturer and what is the percent of records to delete to the total number of records?

In other words, do you only need to delete records for this one manufacturer or you're calling this SP in a loop?

the Pk_ID field somehow suggests it's a PK field in a table? Is it true?