title: SQL Server DELETE is slower with indexes
body: <p>I have an SQL Server 2005 database, and I tried putting indexes on the appropriate fields in order to speed up the <code>DELETE</code> of records from a table with millions of rows (<code>big_table</code> has only 3 columns), but now the <code>DELETE</code> execution time is even <em>longer</em>!  (1 hour versus 13 min for example)</p>
  
  <p>I have a relationship between to tables, and the column that I filter my <code>DELETE</code> by is in the other table.  For example</p>
  
  <pre><code>DELETE FROM big_table
  WHERE big_table.id_product IN (
  SELECT small_table.id_product FROM small_table
  WHERE small_table.id_category = 1)
  </code></pre>
  
  <p>Btw, I've also tried:</p>
  
  <pre><code>DELETE FROM big_table
  WHERE EXISTS
  (SELECT 1 FROM small_table
  WHERE small_table.id_product = big_table.id_product
  AND small_table.id_category = 1)
  </code></pre>
  
  <p>and while it seems to run slightly faster than the first, it's still a lot slower with the indexes than without.</p>
  
  <p>I created indexes on these fields:</p>
  
  <ol>
  <li><code>big_table.id_product</code></li>
  <li><code>small_table.id_product</code></li>
  <li><code>small_table.id_category</code></li>
  </ol>
  
  <p>My .ldf file grows a lot during the <code>DELETE</code>.</p>
  
  <p><strong>Why are my <code>DELETE</code> queries slower when I have indexes on my tables?</strong>  I thought they were supposed to run faster.</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>Okay, consensus seems to be indexes will slow down a huge <code>DELETE</code> becuase the index has to be updated. Although, I still don't understand why it can't <code>DELETE</code> all the rows all at once, and just update the index once at the end.</p>
  
  <p>I was under the impression from some of my reading that indexes would speed up <code>DELETE</code> by making searches for fields in the <code>WHERE</code> clause faster.</p>
  
  <p><a href="http://odetocode.com/Articles/70.aspx" rel="nofollow noreferrer">Odetocode.com says:</a></p>
  
  <p>"Indexes work just as well when searching for a record in DELETE and UPDATE commands as they do for SELECT statements."</p>
  
  <p>But later in the article, it says that too many indexes can hurt performance.</p>
  
  <p>Answers to <strong>bobs</strong> questions:</p>
  
  <ol>
  <li>55 million rows in table</li>
  <li>42 million rows being deleted</li>
  <li>Similar <code>SELECT</code> statement would not run (Exception of type 'System.OutOfMemoryException' was thrown)</li>
  </ol>
  
  <p>I tried the following 2 queries:</p>
  
  <pre><code>SELECT * FROM big_table
  WHERE big_table.id_product IN (
  SELECT small_table.id_product FROM small_table
  WHERE small_table.id_category = 1)
  
  SELECT * FROM big_table
  INNER JOIN small_table
  ON small_table.id_product = big_table.id_product
  WHERE small_table.id_category = 1
  </code></pre>
  
  <p>Both <strong>failed</strong> after running for <strong>25 min</strong> with this error message from SQL Server 2005:</p>
  
  <pre><code>An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.
  </code></pre>
  
  <p>The database server is an older dual core Xeon machine with 7.5 GB ram.  It's my toy test database :) so it's not running anything else.</p>
  
  <p><em>Do I need to do something special with my indexes after I <code>CREATE</code> them to make them work properly?</em></p>