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

By the way, Naomi, is there something in SQL 2008 like a non transactional table type? In MySQL, you have InnoDB and MyIsam tables. The MyIsam tables are non-transactional and therefore insert's and delete's are much faster than in InnoDB. You loose the possibility of Rollback, but for these type of tables speed is the only thing you need at that moment. I am wondering if in SQL 2008 there is also something like a Table engine type, that could be a way to speed the process up.