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


Hi,
I importing every day massive recordset from different manufacturers with updated pricelist and artikel set. Before import it, I delete old one, and then INSERT BULK another pricelist.
Don't exact understand what you mean about pk_id, but pk_id field exist, its guid string and updatable