尝试在 Linq 中实现 EXIST，但嵌套查询的 Any() where 子句需要很长时间才能处理

Question

我正在尝试在where子句中使用Any()，但它需要永远运行。数据本身大约有 100 万行。我在SQL上有工作版本，但由于某种原因，我必须在C#中的linq上运行它。SQL版本运行速度非常快，大约需要8秒来处理，但我不确定linq是否不同。

有两个重要的参数称为 ClientNum 和 CreateDate。客户端编号可以有重复的记录。

困难的部分是我必须获得不同的客户端编号，它在 365 天内只有一条记录，因此代码实际上是在 where 子句中调用它 self。

这是 SQL 代码：

select distinct(t1.ClientNum), month(t1.CreateDate) as monthnum from table1 t1
where 
t1.CreateDate >= '2017-01-15' and 
t1.CreateDate <= '2017-09-17' and
exists
(
select ClientNum, CreateDate from table1 t2
where
t2.ClientNum = t1.ClientNum and
(
(t2.CreateDate < t1.CreateDate and
t2.CreateDate > DATEADD(YEAR,-1,t1.CreateDate))
or
(t1.CreateDate < t2.CreateDate and
t1.CreateDate > DATEADD(YEAR,-1,t2.CreateDate))
)
)

我认为linq应该始终与SQL相同的速度。

当我尝试在 C# 中使用下面的代码时，代码需要很长时间来处理。

db.table1
.Where(o =>
o.CreateDate >= new Datetime(2017,1,15)
&& o.CreateDate <= new Datetime(2017,9,17)
&& db.table1.Any(x =>
x.ClientNum == o.ClientNum
&& (
(x.CreateDate < o.CreateDate && x.CreateDate > o.CreateDate.Value.AddYears(-1))
|| (o.CreateDate < x.CreateDate && o.CreateDate > x.CreateDate.Value.AddYears(-1))
))
).ToList();

不确定是否是该表中数据过多的问题。

谁能帮我一点为什么它卡在 Any() 函数上？或者是否有其他方法可以实现类似于SQL的"存在"功能？

谢谢大家！

Answer 1

我认为你的SQL应该是：

SELECT t1.ClientNum, MAX(MONTH(t1.CreateDate)) AS monthnum FROM table1 t1
JOIN  table1 t2 ON t2.ClientNum = t1.ClientNum
WHERE  
t1.CreateDate >= '2017-01-15' AND 
t1.CreateDate <= '2017-09-17' AND
(t2.CreateDate < t1.CreateDate AND
t2.CreateDate > DATEADD(YEAR,-1,t1.CreateDate))
OR
(t1.CreateDate < t2.CreateDate AND
t1.CreateDate > DATEADD(YEAR,-1,t2.CreateDate))
)
GROUP BY t1.ClientNum
HAVING COUNT(t2.ClientNum) = 1

然后，您的 LINQ 语句将是：

List<Table1> Table1 = new List<Table1>();
var query1 = from t1 in Table1
join t2 in Table1 on t1.ClientNum equals t2.ClientNum
where t1.CreateDate >= new DateTime(2017, 1, 15) && t1.CreateDate <= new DateTime(2017, 9, 17)
&& ((t2.CreateDate < t1.CreateDate && t2.CreateDate > t1.CreateDate.AddYears(-1))
|| (t1.CreateDate < t2.CreateDate && t1.CreateDate > t2.CreateDate.AddYears(-1)))
select new { t1, t2 };
//Check either it run forerver or not 
var result1 = query1.ToList();
//Check either it run forerver or not 
var result = query1.GroupBy(p => p.t1.ClientNum)
.Where(p => p.Count() == 1)
.Select(p => new { ClientNum = p.Key, monthnum = p.Max(a => a.t1.CreateDate).Month })
.ToList();

Answer 2

首先，当您在SQL中运行时，数据不会在线上传输 - 它都在内存/磁盘中，这比通过网络推送百万行要快得多。 也就是说，这可能不是导致经济放缓的原因。

如果要运行 SQL 查询并查看所使用的执行计划，则EXISTS部分可能会重写为 JOIN，或者至少将数据集读入哈希集，然后探测哈希集以查找匹配项。 读取表一次是一百万次读取，但是一旦转换为哈希集，探测到哈希集应该是O(1)操作。(可能还有其他基于索引的优化，由于缺少模式，我们无法从您的示例中看到这些优化。 所以它基本上是 O(2N) = O(N)，即线性操作。

使用 LINQ，如所写，您基本上是让它逐行进入.Where，然后.Any再次扫描同一源。 所以它是O(N^2)，又名二次性能，对于百万的大小来说，这是很多。

我看到了几种可能的方法。 一种是首先通读表并使用 LINQ 的.ToDictionary构建具有适当键的字典，然后在内部探测.Where该字典，使性能线性。

或者，您可以执行一个联接，该联接本质上会在后台执行相同的操作。

最后，围绕日期比较的 LINQ 逻辑似乎与 SQL 不同。 我将使用 SQL 作为重新设计逻辑的事实来源。

db.table1
.Join(db.table1, a => a.ClientNum, b => b.ClientNum, (a, b) => new { T1 = a, T2 = b })
.Where(o =>
(o.T2.CreateDate < o.T1.CreateDate
&& o.T2.CreateDate > o.T1.CreateDate.AddYear(-1))
||
(o.T1.CreateDate < o.T2.CreateDate
&& o.T1.CreateDate > o.T2.CreateDate.AddYear(-1))
).ToList();

请注意，要使其正常工作，db.tabld1的内容应该是可重新枚举的。 也就是说，您可以从中获取IEnumerable<T>的新实例，例如数组或列表。 如果使用数据一次导致数据变得易失性，则首先需要使用诸如将结果分配给变量之类的.ToArray方法将数据构建到内存中，然后执行该变量的连接。