title: Why are multiple where in LINQ so slow?
body: <p>Using C# and Linq to SQL, I found that my query with multiple <code>where</code> is orders of magnitude slower than with a single <code>where</code> / <code>and</code>.</p>
  
  <p>Here is the query</p>
  
  <pre class="lang-cs prettyprint-override"><code>using (TeradiodeDataContext dc = new TeradiodeDataContext())
  {
      var filterPartNumberID = 71;
      var diodeIDsInBlades = (from bd in dc.BladeDiodes
                              select bd.DiodeID.Value).Distinct();
      var diodesWithTestData = (from t in dc.Tests
                                join tt in dc.TestTypes on t.TestTypeID equals tt.ID
                                where tt.DevicePartNumberID == filterPartNumberID
                                select t.DeviceID.Value).Distinct();
      var result = (from d in dc.Diodes
                    where d.DevicePartNumberID == filterPartNumberID
                    where diodesWithTestData.Contains(d.ID)
                    where !diodeIDsInBlades.Contains(d.ID)
                    orderby d.Name
                    select d);
      var list = result.ToList();
      // ~15 seconds
  }
  </code></pre>
  
  <p>However, when the condition in the final query is this</p>
  
  <pre class="lang-cs prettyprint-override"><code>where d.DevicePartNumberID == filterPartNumberID
  &amp; diodesWithTestData.Contains(d.ID)
  &amp; !diodeIDsInBlades.Contains(d.ID)
  // milliseconds
  </code></pre>
  
  <p>it is very fast.</p>
  
  <p>Comparing the SQL in <code>result</code> before calling <code>ToList()</code>, here are the queries (value 71 manually added in place of @params)</p>
  
  <pre class="lang-sql prettyprint-override"><code>-- MULTIPLE WHERE
  SELECT [t0].[ID], [t0].[Name], [t0].[M2MID], [t0].[DevicePartNumberID], [t0].[Comments], [t0].[Hold]
  FROM [dbo].[Diode] AS [t0]
  WHERE (NOT (EXISTS(
      SELECT NULL AS [EMPTY]
      FROM (
          SELECT DISTINCT [t2].[value]
          FROM (
              SELECT [t1].[DiodeID] AS [value]
              FROM [dbo].[BladeDiode] AS [t1]
              ) AS [t2]
          ) AS [t3]
      WHERE [t3].[value] = [t0].[ID]
      ))) AND (EXISTS(
      SELECT NULL AS [EMPTY]
      FROM (
          SELECT DISTINCT [t6].[value]
          FROM (
              SELECT [t4].[DeviceID] AS [value], [t5].[DevicePartNumberID]
              FROM [dbo].[Test] AS [t4]
              INNER JOIN [dbo].[TestType] AS [t5] ON [t4].[TestTypeID] = ([t5].[ID])
              ) AS [t6]
          WHERE [t6].[DevicePartNumberID] = (71)
          ) AS [t7]
      WHERE [t7].[value] = [t0].[ID]
      )) AND ([t0].[DevicePartNumberID] = 71)
  ORDER BY [t0].[Name]
  </code></pre>
  
  <p>and</p>
  
  <pre class="lang-sql prettyprint-override"><code>-- SINGLE WHERE
  SELECT [t0].[ID], [t0].[Name], [t0].[M2MID], [t0].[DevicePartNumberID], [t0].[Comments], [t0].[Hold]
  FROM [dbo].[Diode] AS [t0]
  WHERE ([t0].[DevicePartNumberID] = 71) AND (EXISTS(
      SELECT NULL AS [EMPTY]
      FROM (
          SELECT DISTINCT [t3].[value]
          FROM (
              SELECT [t1].[DeviceID] AS [value], [t2].[DevicePartNumberID]
              FROM [dbo].[Test] AS [t1]
              INNER JOIN [dbo].[TestType] AS [t2] ON [t1].[TestTypeID] = ([t2].[ID])
              ) AS [t3]
          WHERE [t3].[DevicePartNumberID] = (71)
          ) AS [t4]
      WHERE [t4].[value] = [t0].[ID]
      )) AND (NOT (EXISTS(
      SELECT NULL AS [EMPTY]
      FROM (
          SELECT DISTINCT [t6].[value]
          FROM (
              SELECT [t5].[DiodeID] AS [value]
              FROM [dbo].[BladeDiode] AS [t5]
              ) AS [t6]
          ) AS [t7]
      WHERE [t7].[value] = [t0].[ID]
      )))
  ORDER BY [t0].[Name]
  </code></pre>
  
  <p>The two SQL queries execute in &lt; 1 second in SSMS and produce the same results.</p>
  
  <p>So I'm wondering why the first is slower on the LINQ side. It's worrying to me because I know I've used multiple <code>where</code> elsewhere, without being aware of a such a severe performance impact.</p>
  
  <p><a href="https://stackoverflow.com/q/35202648/832052">This question</a> even has answered with both multiple &amp; and where. And <a href="https://stackoverflow.com/a/10324589/832052">this answer</a> even suggests using multiple where clauses.</p>
  
  <p>Can anyone explain why this happens in my case?</p>