title: Why does this query timeout? V2
body: <p>This question is a followup to <a href="https://stackoverflow.com/questions/3350813/why-does-this-query-timeout">This Question</a></p>
  <p>The solution, clearing the execution plan cache seemed to work at the time, but i've been running into the same problem over and over again, and clearing the cache no longer seems to help.  There must be a deeper problem here.</p>
  <p>I've discovered that if I remove the .Distinct() from the query, it returns rows (with duplicates) in about 2 seconds.  However, with the .Distinct() it takes upwards of 4 minutes to complete.  There are a lot of rows in the tables, and some of the where clause fields do not have indexes.  However, the number of records returned is fairly small (a few dozen at most).</p>
  <p>The confusing part about it is that if I get the SQL generated by the Linq query, via Linqpad, then execute that code as SQL or in SQL Management Studio (including the DISTINCT) it executes in about 3 seconds.</p>
  <p><strong>What is the difference between the Linq query and the executed SQL?</strong></p>
  <p>I have a short term workaround, and that's to return the set without .Distinct() as a List, then using .Distinct on the list, this takes about 2 seconds.  However, I don't like doing SQL Server work on the web server.</p>
  <p><strong>I want to understand WHY the Distinct is 2 orders of magnitude slower in Linq, but not SQL.</strong></p>
  <p>UPDATE:</p>
  <p>When executing the code via Linq, the sql profiler shows this code, which is basically identical query.</p>
  <pre><code>sp_executesql N'SELECT DISTINCT [t5].[AccountGroupID], [t5].[AccountGroup] 
      AS [AccountGroup1]
  FROM [dbo].[TransmittalDetail] AS [t0]
  INNER JOIN [dbo].[TransmittalHeader] AS [t1] ON [t1].[TransmittalHeaderID] = 
      [t0].[TransmittalHeaderID]
  INNER JOIN [dbo].[LineItem] AS [t2] ON [t2].[LineItemID] = [t0].[LineItemID]
  LEFT OUTER JOIN [dbo].[AccountType] AS [t3] ON [t3].[AccountTypeID] = 
      [t2].[AccountTypeID]
  LEFT OUTER JOIN [dbo].[AccountCategory] AS [t4] ON [t4].[AccountCategoryID] = 
      [t3].[AccountCategoryID]
  LEFT OUTER JOIN [dbo].[AccountGroup] AS [t5] ON [t5].[AccountGroupID] = 
      [t4].[AccountGroupID]
  LEFT OUTER JOIN [dbo].[AccountSummary] AS [t6] ON [t6].[AccountSummaryID] = 
      [t5].[AccountSummaryID]
  WHERE ([t1].[TransmittalEntityID] = @p0) AND ([t1].[DateRangeBeginTimeID] = @p1) AND 
  ([t1].[ScenarioID] = @p2) AND ([t6].[AccountSummaryID] = @p3)',N'@p0 int,@p1 int,
     @p2 int,@p3 int',@p0=196,@p1=20100101,@p2=2,@p3=0
  </code></pre>
  <p>UPDATE:</p>
  <p>The only difference between the queries is that Linq executes it with sp_executesql and SSMS does not, otherwise the query is identical.</p>
  <p>UPDATE:</p>
  <p>I have tried various Transaction Isolation levels to no avail.  I've also set ARITHABORT to try to force a recompile when it executes, and no difference.</p>