From: esquel@sommarskog.se   
      
   Jens Riedel (JensRie@gmx.de) writes:   
   > currently I use the following SQL statement to delete all data from   
   > table A which have a reference in table B:   
   >   
   > delete   
   > from table_a   
   > where id in (select b_id from table_b where type = 'x')   
   >   
   > This works fine, but currently I have only very small result sets for   
   > the subquery.   
   > I'm concerned now that if the subquery delivers a very big result set   
   > the "IN" operator will make the whole statement execution very slow.   
   >   
   > Can I change the delete statement that way that it is working with   
   > better performance for big amounts of data?   
      
   If you need to delete a million rows, you need to delete a million rows,   
   and that may take some time.   
      
   However, if you are concerned about locking, or the transaction log   
   grow too much, you can do it batches:   
      
      SELECT @rowc = @batchsize   
      WHILE @rowc = @batchsize   
      BEGIN   
         DELETE TOP (5000)   
         from table_a   
         where id in (select b_id from table_b where type = 'x')   
         SELECT @rowc = @@rowcount   
      END   
      
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Links for SQL Server Books Online:   
   SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
   SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)