Hi All,

I have this delete query called in a C# that will delete records based on DataID. DataID is GUID in the DB. The fact is it is super slow (30 mins-40 mins)
Code:
Query.AppendLine("DELETE  ");
Query.AppendLine("FROM  [dbo].[tblAttachments]");
Query.AppendLine("WHERE ");
Query.AppendLine(string.Format("  DataID Not In({0}) ", DataIDList));
I manage to improve it using batch delete concept. Took only 9 mins. to finish.
Code:
            Query.AppendLine(" DECLARE @Rowcount INT ");
            Query.AppendLine(" SET @Rowcount = 1");
            Query.AppendLine(" WHILE @Rowcount > 0 ");
            Query.AppendLine(" BEGIN");
            Query.AppendLine("   DELETE Top (20) FROM [dbo].[tblAttachments] WHERE");
            Query.AppendLine(String.Format("  DataID Not In({0}) ", DataIDList));
            Query.AppendLine("   SET @Rowcount = @@ROWCOUNT");
            Query.AppendLine(" END");
Is there a way to improve the 2nd query(batch delete) to lessen the time of delete?


Other Details:
* DB is MSSQL
* Added Index to DataID
* Number of Rows: 2,687
* Expected Rows after delete: 394
* The table has a binary field that stores the attachments. (huge attachments)
* DataIDList is a string builder variable with 68 GUID DataID.
'67A69794-F41B-4DDE-8C87-B09585ED5BBB','2285E0B3-0FD7-46FC-A768-66EB03BCF2F7','6E2FFDDC-9999-4680-B064-A1CE4A81DFB4', and so on....


Regards,

KGC