c# - This Any 比 this contains 好还是不好？

Question

我正在使用 EF6，我想获取表中一组 ID 中的记录。

例如，在我的测试中，我使用了 4 个 ID。

我尝试了两个选项，第一个是任意选项。

dbContext.MyTable
.Where(x => myIDS.Any(y=> y == x.MyID));

这个 linq exrepsion 生成的 T-SQL 是:

SELECT 
    *
    FROM [dbo].[MiTabla] AS [Extent1]
    WHERE  EXISTS (SELECT 
        1 AS [C1]
        FROM  (SELECT 
            [UnionAll2].[C1] AS [C1]
            FROM  (SELECT 
                [UnionAll1].[C1] AS [C1]
                FROM  (SELECT 
                    cast(130 as bigint) AS [C1]
                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
                UNION ALL
                    SELECT 
                    cast(139 as bigint) AS [C1]
                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable2]) AS [UnionAll1]
            UNION ALL
                SELECT 
                cast(140 as bigint) AS [C1]
                FROM  ( SELECT 1 AS X ) AS [SingleRowTable3]) AS [UnionAll2]
        UNION ALL
            SELECT 
            cast(141 as bigint) AS [C1]
            FROM  ( SELECT 1 AS X ) AS [SingleRowTable4]) AS [UnionAll3]
        WHERE [UnionAll3].[C1] = [Extent1].[MiID]
    )

怎么可以看出，T-SQL是一个使用很多子查询和联合的“where exists”。

第二个选项是包含。

dbContext.MyTable
.Where(x => myIDS.Contains(x.MiID));

还有 T-SQL:

SELECT 
    *
    FROM [dbo].[MiTabla] AS [Extent1]
    WHERE [Extent1].[MiID] IN (cast(130 as bigint), cast(139 as bigint), cast(140 as bigint), cast(141 as bigint))

contains 被翻译成“where in”，但查询要简单得多。

我读到 any 它曾经更快，所以我怀疑 any 是否更快，尽管乍一看更复杂。

非常感谢。

编辑:我有一些测试(我不知道这是否是最好的测试方法)。

System.Diagnostics.Stopwatch miswContains = new System.Diagnostics.Stopwatch();
                        miswContains.Start();
                        for (int i = 0; i < 100; i++)
                        {
                            IQueryable<MyTable> iq = dbContext.MyTable
                            .Where(x => myIDS.Contains(x.MyID));

                            iq.ToArrayAsync();
                        }
                        miswContains.Stop();



                        System.Diagnostics.Stopwatch miswAny = new System.Diagnostics.Stopwatch();
                        miswAny.Start();
                        for (int i = 0; i < 20; i++)
                        {
                            IQueryable<MyTable> iq = dbContext.Mytable
                            .Where(x => myIDS.Any(y => y == x.MyID));

                            iq.ToArrayAsync();
                        }
                        miswAny.Stop();

结果是miswAny大约是850ms，miswContains大约是4251ms。

所以第二个选项，包含contaions，比较慢。

Answer 1

如果您的 MiTabla.MiID 在 index 中，您的第二个选项是我能想到的最快的解决方案(至少对于不是非常大的 id 数组)。

如果您想阅读更多关于 in 子句性能的信息:Is SQL IN bad for performance? .