为什么在Linq中的多个位置如此慢

Question

使用c＃和linq to sql，我发现与单个where/ and相比，使用多个where的查询速度慢。

。

这是查询

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
}

但是，当最终查询中的条件是此

时

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

它非常快。

在调用ToList()之前比较result中的SQL，以下是查询（值71手动添加代替@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]

和

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

两个SQL查询在＆lt;SSM中的1秒并产生相同的结果。

所以我想知道为什么第一个在LINQ侧较慢。这让我担心，因为我知道我在其他地方使用了多个where，而又没有意识到如此严重的性能影响。

这个问题甚至都回答了多个＆amp;在哪里。这个答案甚至建议使用多个条款。

谁能解释为什么在我的情况下会发生这种情况？

Answer 1

，因为像这样的写作

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

是 not（upd）与（如果某个parem1和someparam2！= 0）

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

是（未删除）与

相同

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

upd

是的，我确实错误。第二查询是相同的，第一个是不一样的。

第一和第二查询不完全相同。让我向你展示我的意思。

用lamda-expression第1查询为

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

第二个查询为

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

在这种情况下，在生成的SQL谓词和某个Field2中首先进行（很重要，见下文）

在第一种情况下，我们得到了此SQL：

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

在2个情况下，SQL为：

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

正如我们看到的，有2个'相同的SQL。如我们所见，OP的SQL也"相同"，在WHERE子句中的谓词顺序（如我的示例中）不同。而且我猜SQL优化器会生成2个不同的执行计划，并且可能是（!!!）执行NOT EXISTS，然后是EXISTS，然后过滤比第一次过滤需要更多的时间，然后DO EXISTS和NOT EXISTS

upd2

这是Linq提供商（ORM）的"问题"。我正在使用另一个ORM（LINQ2DB），并且在两种情况下为我生成完全相同的SQL。