一些Linq2Sql生成的T-SQL出现问题

Question

我在以下的LINQ2SQL查询中遇到了SQL超时问题：

DateTime date = DateTime.Parse("2013-08-01 00:00:00.000");

Clients.Where(e => 
    (
        !Orders.Any(f => f.ClientId.Equals(e.Id) && f.OrderDate >= date)
        ||
        Comments.Any(f => f.KeyId.Equals(e.Id))
    )
).Count().Dump();

当在LinqPad中运行此程序时，如果在服务器上运行，将花费很长时间才能完成，并且可能会因为SQL超时而终止。

生成的SQL代码：

-- Region Parameters
DECLARE @p0 DateTime = '2013-08-01 00:00:00.000'
-- EndRegion
SELECT COUNT(*) AS [value]
FROM [Clients] AS [t0]
WHERE (NOT (EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [Orders] AS [t1]
    WHERE ([t1].[ClientId] = [t0].[Id]) AND ([t1].[OrderDate] >= @p0)
    ))) OR (EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [Comments] AS [t2]
    WHERE [t2].[KeyId] = [t0].[Id]
    ))

在SQL Studio中正常运行！

但是：

SELECT COUNT(*) AS [value]
FROM [Clients] AS [t0]
WHERE 

(NOT (EXISTS(SELECT NULL AS [EMPTY] FROM [Orders] AS [t1] WHERE ([t1].[ClientId] = [t0].[Id]) AND ([t1].[OrderDate] >= '2013-08-01 00:00:00.000')))) 

OR  

(EXISTS(SELECT NULL AS [EMPTY] FROM [Comments] AS [t2] WHERE [t2].[KeyId] = [t0].[Id]))

这将帮助我解决在 LinqPad 中实际运行查询的问题。

使用 DECLARE @p0 DateTime = '2013-08-01 00:00:00.000' 与使用常量日期的区别是什么？如何使我的 Linq2SQL 工作？

编辑：

查看两个查询的执行计划：

超时：

正常：

我注意到的一些其他事情是如果我移除 NOT，它就能正常工作：

SELECT COUNT(*) AS [value]
FROM [Clients] AS [t0]
WHERE 

((EXISTS(SELECT NULL AS [EMPTY] FROM [Orders] AS [t1] WHERE ([t1].[ClientId] = [t0].[Id]) AND ([t1].[OrderDate] >= '2013-08-01 00:00:00.000')))) 

OR  

(EXISTS(SELECT NULL AS [EMPTY] FROM [Comments] AS [t2] WHERE [t2].[KeyId] = [t0].[Id]))

如果我删除OR EXISTS部分，它也可以正常工作。

SELECT COUNT(*) AS [value]
FROM [Clients] AS [t0]
WHERE 

((EXISTS(SELECT NULL AS [EMPTY] FROM [Orders] AS [t1] WHERE ([t1].[ClientId] = [t0].[Id]) AND ([t1].[OrderDate] >= '2013-08-01 00:00:00.000')))) 

感谢您的来信。 /Niels

- Niels Bosma

---

第二段SQL代码中的'2013-08-01 00:00:00.000'只是一个字符串，而不是一个DateTime。 - trinaldi

@Tico 当字符串值与真正的 DateTime 值进行比较时，它将被转换为 DateTime。如果无法进行转换，则会抛出错误，SQL 服务器应该足够聪明，只需一次执行此转换而不是每行都执行，因此这不是一个问题。 - Drew R

@Niels Bosma ，你在SSMS中查询是否完成了？如果是的话，最好查看一下执行计划。 - Drew R

@DrewR 是的，过了34分钟 :(. 我已经附上了执行计划。 - Niels Bosma

很遗憾，我无法在您的屏幕截图中阅读查询计划。此外，您能否连续运行34分钟两次，并查看每个执行的时间是否有变化（考虑计划缓存）？ - gvee

Answer 1

您的订单表必须相当大。您对OrderDate建立了索引，对吗？在这个例子中，SQL Server实际上生成了两个不同的执行计划。或者如果它生成相同的计划，SQL将为这两个语句返回非常不同的行数。

DECLARE @p0 DateTime = '2013-08-01 00:00:00.000'
SELECT * FROM Orders WHERE OrderDate >= @p0
SELECT * FROM Orders WHERE OrderDate >= '2013-08-01 00:00:00.000'

第一条语句生成一个参数化查询，计划优化器会假定@p0是未知的，在时间上选择最适合未知值的执行计划。 第二条语句，优化器将考虑您提供了一个固定值。 SQL将查看索引分布并估计有多少行将被过滤掉>= '2013-08-01'。

- user2880486

---

实际上订单表并不是很大，只有39998行。是的，OrderDate上有一个非聚集、非唯一索引。运行你的SQL，它返回相同的结果，而参数化的SQL实际上具有更大的子树成本。 - Niels Bosma

@NielsBosma 是的，这正是你的一个查询超时的原因。这些查询在功能上是相同的，但它们的执行方式不同。 - user2880486

Answer 2

执行计划不可见，但通常的sql性能建议是不要使用否定语，否则会影响性能。在您的情况下，请尝试使用<=而不是>=的否定表达方式。

如果您使用了许多"or"，这也会影响性能。请尝试使用子查询来避免使用过多的"or"或否定表达。

- Juan

Answer 3

我的解决方案是重建 OrderDate 的索引。

- Niels Bosma