C# 使用 Linq to SQL 确定行是否存在的最快方法是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/648795/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the fastest way to determine if a row exists using Linq to SQL?
提问by Protagonist
I am not interested in the contents of a row, I just want to know if a row exists. The Namecolumn is a primary key, so there will either be 0 or 1 matching rows. Currently, I am using:
我对一行的内容不感兴趣，我只想知道一行是否存在。该Name列是主键，因此将有 0 或 1 个匹配行。目前，我正在使用：
if ((from u in dc.Users where u.Name == name select u).Count() > 0)
// row exists
else
// row doesn't exist
While the above works, it does a lot of unnecessary work by selecting all the contents of the row (if it exists). Does the following create a faster query:
虽然上述工作，但它通过选择行的所有内容（如果存在）做了很多不必要的工作。以下是否会创建更快的查询：
if (dc.Users.Where(u => u.Name == name).Any())
...or is there an even faster query?
...或者有更快的查询吗？
采纳答案by Marc Gravell
The Count()approach may do extra work, as (in TSQL) EXISTSor TOP 1are often much quicker; the db can optimise "is there at least one row". Personally, I would use the any/predicate overload:
该Count()方法可能会做额外的工作，如（在 TSQL 中）EXISTS或TOP 1通常更快；数据库可以优化“是否至少有一行”。就个人而言，我会使用 any/predicate 重载：
if (dc.Users.Any(u => u.Name == name)) {...}
Of course, you can compare what each one does by watching the TSQL:
当然，您可以通过观看 TSQL 来比较每个人所做的事情：
dc.Log = Console.Out;
回答by MRFerocius
I think:
我认为：
if (dc.Users.Any(u => u.Name == name)) {...}
is the best approach.
是最好的方法。
回答by Pita.O
I do not agree that selecting top 1 will always outperform select count for all SQL implementations. It's all implementation dependent, you know. Curiously, even the nature of data stored in a particular database also affects the overall outcome.
我不同意为所有 SQL 实现选择前 1 项总是优于选择计数。你知道，这一切都取决于实现。奇怪的是，即使存储在特定数据库中的数据的性质也会影响整体结果。
Let's examine both of them the way I would implement them if I were to do so: For both cases, the projection (WHERE clause) evaluation is a common step.
如果我这样做的话，让我们按照我实施它们的方式来检查它们：对于这两种情况，投影（WHERE 子句）评估是一个常见的步骤。
Next for select top 1, you will have to do a read of all fields (unless you did select top 1 'x' eg: select top 1 1). This will be functionally equivalent to IQueryable.Any(...)., except that you will spend some time flashing in the value for each column of the first encountered record if EXISTS. If SELECT TOP is found in the statement, the projection is truncation if there's no post-projection proc (eg ORDER BY clause). This preprocess incurs a small cost but this is extra cost if no record does exist, in which case, a full project is still done.
接下来选择前 1，您将必须读取所有字段（除非您确实选择了前 1 'x'，例如：选择前 1 1）。这在功能上等同于 IQueryable.Any(...).，除了您将花一些时间在第一个遇到的记录的每一列的值中闪烁（如果存在）。如果在语句中找到 SELECT TOP，如果没有后投影过程（例如 ORDER BY 子句），则投影将被截断。此预处理会产生少量成本，但如果不存在任何记录，则这是额外成本，在这种情况下，仍会完成整个项目。
For select count, the preprocess is not done. A projection is done and if EXISTS is false, the result is instant. If EXISTS is true, the count is still fast because it will be a mere dW_Highest_Inclusive - dW_Lowest_Exclusive. As quick as 500 - 26. If exists is false, the result is even more instant.
对于选择计数，不进行预处理。投影完成，如果 EXISTS 为假，结果是即时的。如果 EXISTS 为真，计数仍然很快，因为它只是 dW_Highest_Inclusive - dW_Lowest_Exclusive。快到 500 - 26。如果exists 为false，则结果更加即时。
The remaining case therefore is: How fast is the projection and what do you loose by doing full projection? And the answer leads to the most crucial issue here which is: is the [NAME] field indexed or not! If you have an index on [NAME] the performance of either query will be so close that it boils down to developer's preference.
因此，剩下的情况是：投影的速度有多快，进行完全投影会损失什么？答案导致这里最关键的问题是：[NAME] 字段是否已编入索引！如果您在 [NAME] 上有索引，则任一查询的性能都会非常接近，以至于归结为开发人员的偏好。
By and large, I will simply write two to four linq queries and output difference in time before and after.
总的来说，我将简单地编写两到四个 linq 查询并输出前后的时间差异。
- select count
- select top 1
- select top 1 1
- select any
- 选择计数
- 选择前 1
- 选择前 1 1
- 选择任何
Repeat all 4 with an nonclustered index on [NAME];
使用 [NAME] 上的非聚集索引重复所有 4 步；
回答by Raju
Of Course
当然
if (dc.Users.Where(u => u.Name == name).Any())
this is best and if multiple conditions to check then it is very simple to write as
这是最好的，如果要检查多个条件，那么写为非常简单
Say you want to check the user for company then
假设您要检查公司的用户然后
if (dc.Users.Where(u => u.ID== Id && u.Company==company).Any())
回答by Tod
For those people claiming Any() is the way forward I've done a simple test in LinqPad against a SQL database of CommonPasswords, 14 million give or take. Code:
对于那些声称 Any() 是前进方向的人，我在 LinqPad 中针对 CommonPasswords 的 SQL 数据库做了一个简单的测试，1400 万次。代码：
var password = "qwertyuiop123";
var startTime = DateTime.Now;
"From DB:".Dump();
startTime = DateTime.Now;
if (CommonPasswords.Any(c => System.Data.Linq.SqlClient.SqlMethods.Like(c.Word, password)))
{
$"FOUND: processing time: {(DateTime.Now - startTime).TotalMilliseconds}\r\n".Dump();
}
else
{
$"NOT FOUND: processing time: {(DateTime.Now - startTime).TotalMilliseconds}\r\n".Dump();
}
"From DB:".Dump();
startTime = DateTime.Now;
if (CommonPasswords.Where(c => System.Data.Linq.SqlClient.SqlMethods.Like(c.Word, password)).Count() > 0)
{
$"FOUND: processing time: {(DateTime.Now - startTime).TotalMilliseconds}\r\n".Dump();
}
else
{
$"NOT FOUND: processing time: {(DateTime.Now - startTime).TotalMilliseconds}\r\n".Dump();
}
"From DB:".Dump();
startTime = DateTime.Now;
if (CommonPasswords.Where(c => c.Word.ToLower() == password).Take(1).Any())
{
$"FOUND: processing time: {(DateTime.Now - startTime).TotalMilliseconds}\r\n".Dump();
}
else
{
$"NOT FOUND: processing time: {(DateTime.Now - startTime).TotalMilliseconds}\r\n".Dump();
}
Here is the translated SQL:
这是翻译后的 SQL：
-- Region Parameters
DECLARE @p0 NVarChar(1000) = 'qwertyuiop123'
-- EndRegion
SELECT
(CASE
WHEN EXISTS(
SELECT NULL AS [EMPTY]
FROM [Security].[CommonPasswords] AS [t0]
WHERE [t0].[Word] LIKE @p0
) THEN 1
ELSE 0
END) AS [value]
GO
-- Region Parameters
DECLARE @p0 NVarChar(1000) = 'qwertyuiop123'
-- EndRegion
SELECT COUNT(*) AS [value]
FROM [Security].[CommonPasswords] AS [t0]
WHERE [t0].[Word] LIKE @p0
GO
-- Region Parameters
DECLARE @p0 NVarChar(1000) = 'qwertyuiop123'
-- EndRegion
SELECT
(CASE
WHEN EXISTS(
SELECT NULL AS [EMPTY]
FROM (
SELECT TOP (1) NULL AS [EMPTY]
FROM [Security].[CommonPasswords] AS [t0]
WHERE LOWER([t0].[Word]) = @p0
) AS [t1]
) THEN 1
ELSE 0
END) AS [value]
You can see that ANY wraps the query up in another layer of code to do a CASE Where Exists Then 1 where as Count() just adds in a Count command. Problem with both of these is you can't do a Top(1) but I can't see a better way using Top(1)
您可以看到 ANY 将查询包装在另一层代码中以执行 CASE Where Exists Then 1 where as Count() 只是添加了一个 Count 命令。这两个的问题是你不能做 Top(1) 但我看不到使用 Top(1) 的更好方法
Results:
结果：
From DB: FOUND: processing time: 13.3962
从数据库：找到：处理时间：13.3962
From DB: FOUND: processing time: 12.0933
从数据库：找到：处理时间：12.0933
From DB: FOUND: processing time: 787.8801
从数据库：找到：处理时间：787.8801
Again:
再次：
From DB: FOUND: processing time: 13.3878
从数据库：找到：处理时间：13.3878
From DB: FOUND: processing time: 12.6881
从数据库：找到：处理时间：12.6881
From DB: FOUND: processing time: 780.2686
从数据库：找到：处理时间：780.2686
Again:
再次：
From DB: FOUND: processing time: 24.7081
从数据库：找到：处理时间：24.7081
From DB: FOUND: processing time: 23.6654
从数据库：找到：处理时间：23.6654
From DB: FOUND: processing time: 699.622
从数据库：找到：处理时间：699.622
Without Index:
无索引：
From DB: FOUND: processing time: 2395.1988
从数据库：找到：处理时间：2395.1988
From DB: FOUND: processing time: 390.6334
从数据库：找到：处理时间：390.6334
From DB: FOUND: processing time: 664.8581
从数据库：找到：处理时间：664.8581
Now some of you may be thinking it's only a millisecond or two. However the varience was much greater before I put an index on it; by a few seconds.
现在你们中的一些人可能认为这只是一两毫秒。然而，在我给它加上索引之前，差异要大得多；几秒钟。
The last calculation is there as I started with the notion that ToLower() would be faster than LIKE, and I was right, until I tried count and put an Index on it. I guess the Lower() makes the index irrelavent.
当我开始认为 ToLower() 会比 LIKE 更快时，最后一个计算就在那里，我是对的，直到我尝试计数并在其上放置索引。我猜Lower() 使索引无关紧要。
