>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

For this reason I don't delete the records right away, but flag the records as deleted:
UPDATE [table] SET DeletedFlag = 1 WHERE id = 1

But that means you have to add the field DeletedFlag and modify your queries to include
WHERE DeletedFlag = 0
everywhere where you don't want to see the "deleted" records.

Once it a blue moon, we optimize the table by running a DELETE FROM [table] WHERE DeletedFlag = 1. This can be done during the night and should not disturb anybody.