Why are multiple where in LINQ so slow?

Question

Using C# and Linq to SQL, I found that my query with multiple where is orders of magnitude slower than with a single where / and.

Here is the query

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
}

However, when the condition in the final query is this

where d.DevicePartNumberID == filterPartNumberID
& diodesWithTestData.Contains(d.ID)
& !diodeIDsInBlades.Contains(d.ID)
// milliseconds

it is very fast.

Comparing the SQL in result before calling ToList(), here are the queries (value 71 manually added in place of @params)

-- 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]

and

-- 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]

The two SQL queries execute in < 1 second in SSMS and produce the same results.

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 where elsewhere, without being aware of a such a severe performance impact.

This question even has answered with both multiple & and where. And this answer even suggests using multiple where clauses.

Can anyone explain why this happens in my case?

Answer 1

Because writing like this

if (someParam1 != 0)
{
    myQuery = myQuery.Where(q => q.SomeField1 == someParam1)
}
if (someParam2 != 0)
{
    myQuery = myQuery.Where(q => q.SomeField2 == someParam2)
}

is NOT(upd) the same as (in case when someParam1 and someParam2 != 0)

myQuery = from t in Table
          where t.SomeField1 == someParam1
             && t.SomeField2 == someParam2
          select t;

is (NOT deleted) the same as

myQuery = from t in Table
          where t.SomeField1 == someParam1
          where t.SomeField2 == someParam2
          select t;

UPD

Yes, I do mistake. Second query is same, first is not same.

First and Second queries not EXACTLY the same. Let me show you what I mean.

1st query with lamda-expression writen as

t.Where(r => t.SomeField1 == someParam1 && t.SomeField2 == someParam2)

2nd query as

t.Where(r => r.SomeField1 == someParam1).Where(r => r.SomeField2 == someParam2)

In this case in generated SQL Predicate with SomeField2 goes first (it is important, see below)

In 1st case we getting this SQL:

SELECT <all field from Table>
  FROM table t
 WHERE t.SomeField1 = :someParam1
   AND t.SomeField2 = :someParam2

In 2 case the SQL is:

SELECT <all field from Table>
  FROM table t
 WHERE t.SomeField2 = :someParam2
   AND t.SomeField1 = :someParam1

As we see there are 2 'same' SQLs. As we see, the OP's SQLs are also 'same', they are different in order of predicates in WHERE clause (as in my example). And I guess that SQL optimizer generate 2 different execution plans and may be(!!!) doing NOT EXISTS, then EXISTS and then filtering take more time than do first filtering and after that do EXISTS and NOT EXISTS

UPD2

It is a 'problem' of Linq Provider (ORM). I'm using another ORM (linq2db), and it generates for me EXACTLY the same SQLs in both cases.