我在使用以下实体框架查询时遇到性能问题:
using (MyEntities context = new MyEntities())
{
return context.Companies
.Single(c => c.CompanyId == company.CompanyId)
.DataFile.Sum(d => d.FileSize);
}
在SQL profiler中进行跟踪时,我看到以下SQL命令:
exec sp_executesql N'SELECT
[Extent1].[DataFileID] AS [DataFileID],
[Extent1].[LocalFileName] AS [LocalFileName],
[Extent1].[ServerFileName] AS [ServerFileName],
[Extent1].[DateUploaded] AS [DateUploaded],
[Extent1].[FileSize] AS [FileSize],
[Extent1].[CompanyID] AS [CompanyID]
FROM [dbo].[DataFile] AS [Extent1]
WHERE [Extent1].[CompanyID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=16
据我所见,所有数据文件行(超过10,000行)都被返回到内存中,然后进行Sum()
计算。
编辑:
根据Patryk的建议,我已将查询更改为以下内容:
using (MyEntities context = new MyEntities())
{
return context.Companies
.Where(c => c.CompanyId == company.CompanyId)
.Select(x => x.DataFiles.Sum(d => d.FileSize))
.Single();
}
SQL跟踪看起来像这样:
SELECT TOP (2)
(
SELECT
SUM([Extent2].[FileSize]) AS [A1]
FROM
[dbo].[DataFile] AS [Extent2]
WHERE
[Extent1].[CompanyId] = [Extent2].[CompanyID]
) AS [C1]
FROM
[dbo].[Company] AS [Extent1]
WHERE
[Extent1].[CompanyId] = 16
这已经好多了,但实际上我只想要像这样简单快速的东西:
SELECT SUM(FileSize) FROM DataFile WHERE CompanyId = 16
context.Companies.Where(c => ...).Select(x => x.DataFile.Sum(d => d.FileSize)).Single()
。这样表达式访问器可能会更容易处理,生成的SQL也可能更好;如果没有帮助,您可以在Select
之后手动调用AsEnumerable
,看看是否有所改变。 - Patryk Ćwiekcontext.Companies.SqlQuery("...")
- 我怀疑这两个查询之间的性能差异并不显著。 - D Stanley.Single()
/产生一个懒惰且Linq感知的返回,但他们没有这样做。 - Aron