title: Is SQL Server DRI (ON DELETE CASCADE) slow?
body: <p>I've been analyzing a recurring "bug report" (perf issue) in one of our systems related to a particularly slow delete operation.  Long story short: It seems that the <code>CASCADE DELETE</code> keys were largely responsible, and I'd like to know (a) if this makes sense, and (b) why it's the case.</p>
  
  <p>We have a schema of, let's say, widgets, those being at the root of a large graph of related tables and related-to-related tables and so on.  To be perfectly clear, deleting from this table is actively discouraged; it is the "nuclear option" and users are under no illusions to the contrary.  Nevertheless, it sometimes just has to be done.</p>
  
  <p>The schema looks something like this:</p>
  
  <pre><code>Widgets
     |
     +--- Anvils [1:1]
     |    |
     |    +--- AnvilTestData [1:N]
     |
     +--- WidgetHistory (1:N)
          |
          +--- WidgetHistoryDetails (1:N)
  </code></pre>
  
  <p>Column definitions look like the following:</p>
  
  <pre><code>Widgets (WidgetID int PK, WidgetName varchar(50))
  Anvils (AnvilID int PK, WidgetID int FK/IX/UNIQUE, ...)
  AnvilTestData (AnvilID int FK/IX, TestID int, ...Test Data...)
  WidgetHistory (HistoryID int PK, WidgetID int FK/IX, HistoryDate datetime, ...)
  WidgetHistoryDetails (HistoryID int FK/IX, DetailType smallint, ...)
  </code></pre>
  
  <p>Nothing too scary, really.  A <code>Widget</code> can be different types, an <code>Anvil</code> is a special type, so that relationship is 1:1 (or more accurately 1:0..1).  Then there's a large amount of data - perhaps thousands of rows of <code>AnvilTestData</code> per <code>Anvil</code> collected over time, dealing with hardness, corrosion, exact weight, hammer compatibility, usability issues, and impact tests with cartoon heads.</p>
  
  <p>Then every <code>Widget</code> has a long, boring history of various types of transactions - production, inventory moves, sales, defect investigations, RMAs, repairs, customer complaints, etc.  There might be 10-20k details for a single widget, or none at all, depending on its age.</p>
  
  <p>So, unsurprisingly, there's a <code>CASCADE DELETE</code> relationship at every level here.  If a <code>Widget</code> needs to be deleted, it means something's gone terribly wrong and we need to erase any records of that widget ever existing, including its history, test data, etc.  Again, nuclear option.</p>
  
  <p>Relations are all indexed, statistics are up to date.  Normal queries are fast.  The system tends to hum along pretty smoothly for everything except deletes.</p>
  
  <p>Getting to the point here, finally, for various reasons we only allow deleting one widget at a time, so a delete statement would look like this:</p>
  
  <pre><code>DELETE FROM Widgets
  WHERE WidgetID = @WidgetID
  </code></pre>
  
  <p>Pretty simple, innocuous looking delete... <strong>that takes over 2 minutes to run</strong>, for a widget with <strong>no data!</strong></p>
  
  <p>After slogging through execution plans I was finally able to pick out the <code>AnvilTestData</code> and <code>WidgetHistoryDetails</code> deletes as the sub-operations with the highest cost.  So I experimented with turning off the <code>CASCADE</code> (but keeping the actual FK, just setting it to <code>NO ACTION</code>) and rewriting the script as something very much like the following:</p>
  
  <pre><code>DECLARE @AnvilID int
  SELECT @AnvilID = AnvilID FROM Anvils WHERE WidgetID = @WidgetID
  
  DELETE FROM AnvilTestData
  WHERE AnvilID = @AnvilID
  
  DELETE FROM WidgetHistory
  WHERE HistoryID IN (
      SELECT HistoryID
      FROM WidgetHistory
      WHERE WidgetID = @WidgetID)
  
  DELETE FROM Widgets WHERE WidgetID = @WidgetID
  </code></pre>
  
  <p>Both of these "optimizations" resulted in significant speedups, each one shaving nearly a full minute off the execution time, so that the original 2-minute deletion now takes about 5-10 seconds - at least for <em>new</em> widgets, without much history or test data.</p>
  
  <p>Just to be absolutely clear, there is still a <code>CASCADE</code> from <code>WidgetHistory</code> to <code>WidgetHistoryDetails</code>, where the fanout is highest, I only removed the one originating from <code>Widgets</code>.</p>
  
  <p>Further "flattening" of the cascade relationships resulted in progressively less dramatic but still noticeable speedups, to the point where deleting a <em>new</em> widget was almost instantaneous once all of the cascade deletes to larger tables were removed and replaced with explicit deletes.</p>
  
  <p>I'm using <code>DBCC DROPCLEANBUFFERS</code> and <code>DBCC FREEPROCCACHE</code> before each test.  I've disabled all triggers that might be causing further slowdowns (although those would show up in the execution plan anyway).  And I'm testing against older widgets, too, and noticing a significant speedup there as well; deletes that used to take 5 minutes now take 20-40 seconds.</p>
  
  <p>Now I'm an ardent supporter of the "SELECT ain't broken" philosophy, but there just doesn't seem to be any logical explanation for this behaviour other than crushing, mind-boggling inefficiency of the <code>CASCADE DELETE</code> relationships.</p>
  
  <p>So, my questions are:</p>
  
  <ul>
  <li><p><strong>Is this a known issue with DRI in SQL Server?</strong> (I couldn't seem to find any references to this sort of thing on Google or here in SO; I suspect the answer is no.)</p></li>
  <li><p><strong>If not, is there another explanation for the behaviour I'm seeing?</strong></p></li>
  <li><p><strong>If it is a known issue, why is it an issue, and are there better workarounds I could be using?</strong></p></li>
  </ul>