LINQ to SQL 比较性能

Question

我有一个关于Linq to SQL查询的问题 - 性能方面。 我的目标是找出我的大约500个项目（在List中）的元素是否与数据库条目匹配。目前，仅这个操作就需要大约300秒才能完成！数据库包含超过一百万行数据，并且未来还将增长，因此这样早期的性能表现是不可接受的。以下是示例：

var query = from item in db.DataTable.Where(x => x.date == suppliedDate)
            where inputList.Contains(item.name)
            select new { item.name};

帮帮我！

编辑： 非常感谢您的所有建议！我只想补充一些额外的观察，因为我现在已经能够查看LINQ查询的SQL输出（如下所示）。

SELECT [t0].[name]
FROM [dw].[DataTable] AS [t0]
WHERE ([t0].[name] IN (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17)) AND ([t0].[date] = @p18)
-- @p0: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [Mark]
-- @p1: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [Owen]
-- @p2: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [James]
-- @p3: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [John]

这是否会在数据库上产生500个单独的访问？（如Ian所建议的那样）并且有没有办法在不使用存储过程或创建额外表的情况下提高性能？ （这两个选项现在都不是很可用）。我尝试了Geoff的建议，这将我的运行时间从300秒降至约126秒 - 但这仍然很多，特别是考虑到数据库连接最多只需要不到10秒。

非常感谢

- StephenA

---

您已经给您的数据库表添加了索引吗？您有哪些索引？ - Mark Byers

涉及的表格是否在（x.date和item.name）上有适当的索引？ - Bob Jarvis - Слава Україні

我的数据库中有“名称”和“日期”列上的索引 - 我在某处读到过LINQ to SQL不支持索引，但那是真的吗？ - StephenA

Stephen，SQL支持索引，LINQ生成SQL，所以这种说法是不正确的。有时候可能会比较棘手，需要生成你期望的SQL以利用现有的索引，但只要查看生成的SQL并相应地采取行动就可以了。 - Neil Trodden

@StephenA - 这只是一个查询。你尝试过在“数据库引擎调整顾问”中运行查询吗？也许你的索引没有正确设置。 - Geoff Appleford

@StephenA - 你的意思是毫秒而不是秒吗？我刚刚对一个有一百万行和五百个名字的表进行了测试，执行时间不到一秒。 - Geoff Appleford

Answer 1

尝试

var query = from item in db.DataTable
            where item.date == suppliedDate
            where inputList.Contains(item.name)
            select new { item.name};

您可以使用 LinqPad 来测试您的查询，并且它还会显示生成的 SQL 语句。

- Geoff Appleford

---

我非常确定这会生成一个带有IN子句的SQL查询。这样做的危险在于您可能超出SQL参数的最大数量。 - Neil Trodden

@Neil - 是的，它确实可以，但是在500个项目上，他仍然远远落后于+-2000个SQL限制。 - Geoff Appleford

同意，Geoff。我猜他会知道自己是否会接近这个限制。我第一次遇到这个问题是在收到 YSODs 报告时，不得不重新架构我的代码，因为网站已经崩溃了。要是我之前更加清楚这个潜在的问题就好了 :-) - Neil Trodden

@Neil - 是的，绝对需要注意。 - Geoff Appleford

Answer 2

-- @p0: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [Mark] 

列是否为varchar？

如果您检查该查询的执行计划，您可能会看到SQL Server将整个索引转换为nvarchar（DOOOOOM！）

解决方法是将参数转换为varchar。

---

您可以直接获取命令并重置其参数类型（在您的情况下为ansi-string）。

http://msdn.microsoft.com/en-us/library/system.data.linq.datacontext.getcommand.aspx http://msdn.microsoft.com/en-us/library/system.data.dbtype.aspx

然后，您可以对该命令调用ExecuteReader，以获得一个DbDataReader。 您可以将此DbDataReader交给datacontext的Translate方法，它将为您提供您从linq中期望的>。

http://msdn.microsoft.com/en-us/library/bb534213.aspx

我已经发布了实现此操作的代码，请参见这里

- Amy B

---

哇..执行时间从2分钟降至约3秒。谢谢！！ - StephenA

Answer 3

你可能正在对数据库进行500次单独的请求，使用该查询！

首先，在SQL Server中使用查询分析器查看运行的SQL语句，看看Linq-to-SQL是否符合你的预期。

- Ian Ringrose

---

他没错，但我认为适当的索引将提高性能，尽管我更倾向于Shiv的答案。 - Neil Trodden

Answer 4

考虑到您正在处理的记录数量，这类操作在数据库中完成会更快。

我建议将列表中的项批量插入到临时表中，然后根据需要执行内部连接或交集/差集等操作以获得结果。

MSSQL 2008 还有一个超级快的 MERGE SQL 语句，可能也会有所帮助。如果您的硬件可以承受，在处理超过十亿条记录时，执行此类操作只需要几毫秒。

这也带来了另外一件事：硬件。不要低估硬件要求，特别是硬盘速度（通常是由6/12个主轴组成的RAID 5/6阵列），对于所处理的数据类型，如果您需要真正良好的查询性能，则需要使用高速硬盘。

- Shiv Kumar

Answer 5

一种更快的方法是避免使用linq代码，您可以将inputList的名称保存到一个SQL表中（使用xml插入），然后编写一个存储过程来执行选择并返回数据集。然后在linq中，您可以调用该SP并提取结果。

- Stefan P.

---

将Xml发送到数据库并存储实际上比查询要花费更多的代价，一旦解决了这个问题。 - Amy B