实体框架多个聚合性能 [英] Entity Framework multiple aggregation performance
本文介绍了实体框架多个聚合性能的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个关于实体框架查询建设问题。
模式
我有一个表的结构一样这样的：
CREATE TABLE [DBO] [数据记录器（
[ID] [BIGINT] IDENTITY（1。 1）NOT NULL，
[专案编号] [BIGINT] NULL，
约束[PrimaryKey1] PRIMARY KEY CLUSTERED（[ID] ASC）
）
CREATE TABLE [ DBO]。[DCDistributionBox（
[ID] [BIGINT] IDENTITY（1,1）NOT NULL，
[DataLoggerID] [BIGINT] NOT NULL，
约束[PrimaryKey2] PRIMARY KEY CLUSTERED （[ID] ASC）
）
ALTER TABLE [DBO]。[DCDistributionBox]
ADD CONSTRAINT [FK_DCDistributionBox_DataLogger]外键（[DataLoggerID]）
参考文献[ DBO]。[数据记录器]（[ID]）
CREATE TABLE [DBO]。[DCString（
[ID] [BIGINT] IDENTITY（1,1）NOT NULL，
[DCDistributionBoxID] [BIGINT] NOT NULL，
[CurrentMPP] [十进制（18,2）NULL，
约束[PrimaryKey3] PRIMARY KEY CLUSTERED（[ID] ASC）
）
ALTER TABLE [DBO]。[DCString]
ADD CONSTRAINT [FK_DCString_DCDistributionBox]外键（[DCDistributionBoxID]）
参考文献[DBO]。[DCDistributionBox]（[ID]）
CREATE TABLE [DBO]。[StringData是（
[DCStringID] [BIGINT] NOT NULL，
[时间戳] [日期时间] NOT NULL，
[DCCurrent ] [十进制（18,2）NULL，
约束[PrimaryKey4] PRIMARY KEY CLUSTERED（[时间戳] DESC [DCStringID] ASC）
）
的
[StringData是] 表作为具有以下存储数据：
- 数据空间：26,901.86 MB
- 行数：131827749
- 分区：真正的
- 分区数：62
用法
我现在想组中的 [StringData是] 表中的数据，并做一些聚集。
在纯SQL它是这样的：
宣布@projectID BIGINT = 20686;
申报@from日期时间= '06 .02.2016';
申报@till日期时间= '07 .02.2016';
申报@interval INT = 15;
选择
DATEADD（分，DATEDIFF（分，0，[StringData是]。[时间戳]）/ * @interval @interval，0）AS [时间戳]
，AVG （[StringData是]。[DCCurrent] / [DCString]。[CurrentMPP]）AS [DCCurrentAvg]
，MIN（[StringData是]。[DCCurrent] / [DCString]。[CurrentMPP]）AS [DCCurrentMin]
，MAX（[StringData是]。[DCCurrent] / [DCString]。[CurrentMPP]）AS [DCCurrentMax]
，STDEV（[StringData是]。[DCCurrent] / [DCString]。[CurrentMPP]）AS [ DCCurrentStDev]
，COUNT（*）AS [计数]
从[StringData是]
加入[DCString] ON [DCString]。[ID] = [StringData是]。[DCStringID ]
加入[DCDistributionBox] ON [DCDistributionBox]。[ID] = [DCString]。[DCDistributionBoxID]
加入[数据记录器] ON [数据记录器]。[ID] = [DCDistributionBox]。[DataLoggerID]
，其中[数据记录器] [专案编号] = @projectID
和[StringData是] [时间戳]方式> = @from
和[StringData是] [时间戳]< @till
GROUP BY DATEADD（分，DATEDIFF（分，0，[StringData是]。[时间戳]）/ * @interval @interval，0）
Excecution时间跨度： 653ms
现在我创建了一个实体框架当量（我想）：
VAR compareData = model.StringDatas
AsNoTracking（）
。凡（ p => p.DCString.DCDistributionBox.DataLogger.ProjectID ==专案编号和放大器;&安培; p.TimeStamp> =&从功放;&安培; p.TimeStamp<至）
。选择（D =>新建
{
时间戳= d.Key，
DCCurrentMin = d.Min（v => v.DCCurrent / v.DCString.CurrentMPP），
DCCurrentMax = d.Max（ v => v.DCCurrent / v.DCString.CurrentMPP），
DCCurrentAvg = d.Average（v => v.DCCurrent / v.DCString.CurrentMPP），
DCCurrentStDev = DbFunctions.StandardDeviation（ d.Select（v => v.DCCurrent / v.DCString.CurrentMPP））
}）
.ToList（）;
和excecution的结果是超时（时间越长30秒）！？
尝试
现在我接过来一看进入实体框架生成的SQL查询，看起来像这样：
SELECT
1 AS [C1]，
[Project10]。[C1] AS [C2]，
[Project10 ]。〔C2〕的AS〔C3〕，
[Project10]。[C3]的AS〔C4〕，
[Project10]。[4] AS [C5]，
[Project10]。 [C5] AS [6]
FROM（SELECT
[Project8]。[C1] AS [C1]，
[Project8]。[C2] AS [C2]，
[Project8]。[C3]的AS〔C3〕，
[Project8]。[4] AS〔C4〕，
（任选
STDEV（[Project9]。[A1〕）AS [ A1]
FROM（SELECT
[Project9]。[DCCurrent] / [Project9]。[CurrentMPP] AS [A1]
FROM（SELECT
[Extent17]。[DCStringID] AS [DCStringID]，
[Extent17]。[DCCurrent] AS [DCCurrent]，
[Extent18]。[ID] AS [ID]，
[Extent18]。[CurrentMPP] AS [ CurrentMPP]
从[DBO]。[StringData是] AS [Extent17]
INNER JOIN [DBO]。[DCString] AS [Extent18] ON [Extent17]。[DCStringID] = [Extent18]。[ID ]
INNER JOIN [DBO]。[DCDistributionBox] AS [Extent19] ON [Extent18]。[DCDistributionBoxID] = [Extent19]。[ID]
INNER JOIN [DBO]。[数据记录器] AS [Extent20 ] ON [Extent19]。[DataLoggerID] = [Extent20]。[ID]
WHERE（（[Extent20]。[专案编号] = @ p__linq__0）OR（（[Extent20]。[专案编号] IS NULL）AND（ @ p__linq__0 IS NULL）））AND（[Extent17] [时间戳]方式> = @ p__linq__1）AND（[Extent17] [时间戳]< @ p__linq__2）AND（（[Project8]。[C1] =（DATEADD（分，（（DATEDIFF（分，@ p__linq__4，[Extent17]。[时间戳]））/ @ p__linq__5）* @ p__linq__6，@ p__linq__3））） OR（（[Project8]。[C1] IS NULL）AND（DATEADD（分，（（DATEDIFF（分，@ p__linq__4，[Extent17]。[时间戳]））/ @ p__linq__5）* @ p__linq__6，@ p__linq__3）IS NULL ）））
）AS [Project9]
）AS [Project9]）AS [C5]
FROM（SELECT
[Project6]。[C1] AS [C1]，
〔Project6]〔C2〕的AS〔C2〕，
[Project6]。[C3]的AS〔C3〕，
（任选
AVG（[项目7]。[A1〕 ）AS [A1]
FROM（SELECT
[项目7]。[DCCurrent] / [项目7]。[CurrentMPP] AS [A1]
FROM（SELECT
[Extent13。 [DCStringID] AS [DCStringID]，
[Extent13]。[DCCurrent] AS [DCCurrent]，
[Extent14]。[ID] AS [ID]，
[Extent14]。[CurrentMPP ] AS [CurrentMPP]
从[DBO]。[StringData是] AS [Extent13]
INNER JOIN [DBO]。[DCString] AS [Extent14] ON [Extent13]。[DCStringID] = [Extent14] 。[ID]
INNER JOIN [DBO]。[DCDistributionBox] AS [Extent15] ON [Extent14]。[DCDistributionBoxID] = [Extent15]。[ID]
INNER JOIN [DBO]。[数据记录器] AS [Extent16] ON [Extent15]。[DataLoggerID] = [Extent16]。[ID]
WHERE（（[Extent16]。[专案编号] = @ p__linq__0）OR（（[Extent16]。[专案编号] IS NULL ）AND（@ p__linq__0 IS NULL）））AND（[Extent13] [时间戳]方式> = @ p__linq__1）AND（[Extent13] [时间戳]< @ p__linq__2）AND（（[Project6]。[C1] =（DATEADD（分，（（DATEDIFF（分，@ p__linq__4，[Extent13]。[时间戳]））/ @ p__linq__5）* @ p__linq__6，@ p__linq__3））） OR（（[Project6]。[C1] IS NULL）AND（DATEADD（分，（（DATEDIFF（分，@ p__linq__4，[Extent13]。[时间戳]））/ @ p__linq__5）* @ p__linq__6，@ p__linq__3）IS NULL ）））
）AS [项目7]
）AS [项目7]）AS [4]
FROM（SELECT
[Project4]。[C1] AS [C1]，
〔Project4]。[C2] AS [C2]，
（SELECT
MAX（[Project5]。[A1]）AS [A1]
FROM（SELECT
[Project5]。[DCCurrent] / [Project5]。[CurrentMPP] AS [A1]
FROM（SELECT
[Extent9]。[DCStringID] AS [DCStringID]，
[Extent9。 [DCCurrent] AS [DCCurrent]，
[Extent10]。[ID] AS [ID]，
[Extent10]。[CurrentMPP] AS [CurrentMPP]
从[DBO]。[StringData是] AS [Extent9]
INNER JOIN [DBO]。[DCString] AS [Extent10] ON [Extent9]。[DCStringID] = [Extent10]。[ID]
INNER JOIN [DBO]。[DCDistributionBox ] AS [Extent11] ON [Extent10]。[DCDistributionBoxID] = [Extent11]。[ID]
INNER JOIN [DBO]。[数据记录器]作为[Extent11] [Extent12]。[DataLoggerID] = [Extent12] 。[ID]
WHERE（（[Extent12]。[专案编号] = @ p__linq__0）OR（（[Extent12]。[专案编号] IS NULL）AND（@ p__linq__0 IS NULL）））AND（[Extent9。 [时间戳]> = @ p__linq__1）AND（[Extent9] [时间戳]< @ p__linq__2）AND（（[Project4]。[C1] =（DATEADD（分，（（DATEDIFF（分，@ p__linq__4，[Extent9]。[时间戳]））/ @ p__linq__5）* @ p__linq__6，@ p__linq__3））） OR（（[Project4]。[C1] IS NULL）AND（DATEADD（分，（（DATEDIFF（分，@ p__linq__4，[Extent9]。[时间戳]））/ @ p__linq__5）* @ p__linq__6，@ p__linq__3）IS NULL ）））
）AS [Project5]
）AS [Project5]）AS [C3]
FROM（SELECT
[Project2中] [C1] AS [C1]，
（SELECT
MIN（[项目3]。[A1]）AS [A1]
FROM（SELECT
[项目3]。[DCCurrent] / [项目3]。[CurrentMPP] AS [A1]
FROM（SELECT
[Extent5]。[DCStringID] AS [DCStringID]，
[Extent5]。[DCCurrent] AS [DCCurrent]，
[Extent6。 [ID] AS [ID]，
[Extent6]。[CurrentMPP] AS [CurrentMPP]
从[DBO]。[StringData是] AS [Extent5]
INNER JOIN [DBO]。[ DCString] AS [Extent6] ON [Extent5]。[DCStringID] = [Extent6]。[ID]
INNER JOIN [DBO]。[DCDistributionBox] AS [Extent7] ON [Extent6]。[DCDistributionBoxID] = [Extent7 ]。[ID]
INNER JOIN [DBO]。[数据记录器] AS [Extent8] ON [Extent7]。[DataLoggerID] = [Extent8]。[ID]
WHERE（（[Extent8]。[专案编号] = @ p__linq__0）OR（（[Extent8] [专案编号] IS NULL）AND（@ p__linq__0 IS NULL）））AND（[Extent5] [时间戳]方式> = @ p__linq__1）AND（[Extent5] [时间戳< @ p__linq__2）AND（（[Project2中] [C1] =（DATEADD（分，（（DATEDIFF（分，@ p__linq__4，[Extent5]。[时间戳]））/ @ p__linq__5）* @ p__linq__6，@ p__linq__3））） OR（（[Project2中] [C1] IS NULL）AND（DATEADD（分，（（DATEDIFF（分，@ p__linq__4，[Extent5]。[时间戳]））/ @ p__linq__5）* @ p__linq__6，@ p__linq__3）IS NULL ）））
）AS [项目3]
）AS [项目3]）AS [C2]
FROM（SELECT
[Distinct1]。[C1] AS [C1]
FROM（SELECT DISTINCT
DATEADD（分，（（DATEDIFF（分，@ p__linq__4，[Extent1]。[时间戳]））/ @ p__linq__5）* @ p__linq__6，@ p__linq__3）AS [C1]
从[DBO]。[StringData是] AS [Extent1]
INNER JOIN [DBO]。[DCString] AS [Extent2] ON [Extent1]。[DCStringID] = [Extent2]。[ID]
INNER JOIN [DBO]。[DCDistributionBox] AS [Extent3] ON [Extent2]。[DCDistributionBoxID] = [Extent3]。[ID]
INNER JOIN [DBO]。[数据记录器] AS [Extent4] ON [Extent3] [DataLoggerID] = [Extent4]。[ID]
WHERE（（[Extent4]。[专案编号] = @ p__linq__0）OR（（[Extent4]。[专案编号] IS NULL）AND（@ p__linq__0 IS NULL） ））AND（[Extent1] [时间戳]方式> = @ p__linq__1）AND（[Extent1] [时间戳]< @ p__linq__2）
）AS [Distinct1]
）AS [Project2的]
）AS [Project4]
）AS [Project6]
）AS [Project8]
）AS [Project10]
问题
为什么实体框架的方式隔开每个聚集成一个SNGLE子选择，我怎么能避免这种情况，要接近到原始的SQL查询的性能？
更新1
这有确切的同一个SQL查询的输出和超时结果：
VAR查询=从model.StringDatas
d其中d.DCString.DCDistributionBox.DataLogger.ProjectID ==专案编号
，其中d.TimeStamp> =寄件者
，其中d.TimeStamp< tillDate
组由DbFunctions.AddMinutes（DateTime.MinValue，DbFunctions.DiffMinutes（DateTime.MinValue，d.TimeStamp）/ minuteInterval * minuteInterval）到G
D选择新的
{
时间戳= g.Key，
DCCurrentMin = g.Min（v => v.DCCurrent / v.DCString.CurrentMPP），
DCCurrentMax =栽培大豆（v => v.DCCurrent / v .DCString.CurrentMPP）
DCCurrentAvg = g.Average（v => v.DCCurrent / v.DCString.CurrentMPP）
DCCurrentStDev = DbFunctions.StandardDeviation（g.Select（v => v .DCCurrent / v.DCString.CurrentMPP））
};
VAR QueryResult中= query.ToList（）;
解决方案
我注意到的行为（错误？）时，回答如何我是否能够得到EF6生成包含多张聚合列有效的SQL？。
我能解决这个问题的唯一途径是通过的组引入临时投影 C $ C>运行，同样也适用于您的情况：
VAR的查询=从E在
（从d。在db.StringDatas.AsNoTracking（）
，其中d.DCString.DCDistributionBox.DataLogger.ProjectID ==专案编号
和;&安培; d.TimeStamp> =寄件者&放大器;&安培; d.TimeStamp< tillDate
选择新的{D，S = d.DCString}）由DbFunctions.AddMinutes（DateTime.MinValue，DbFunctions.DiffMinutes（DateTime.MinValue，edTimeStamp）/ minuteInterval * minuteInterval）为G
E组
让利比= g.Select（E => edDCCurrent / esCurrentMPP）
选择新的
{
时间戳= g.Key，
DCCurrentMin =比例。 MIN（），
DCCurrentMax = ratio.Max（），
DCCurrentAvg = ratio.Average（），
DCCurrentStDev = DbFunctions.StandardDeviation（比）
};
EF生成的SQL：
SELECT
1 AS [C1]，
[GroupBy1]。[K1] AS [C2]，
[GroupBy1]。[A1] AS [C3]
[GroupBy1]。[A2]的AS〔C4〕，
[GroupBy1]。[A3] AS [C5]，
[GroupBy1]。[A4] AS [C6]
从（选择
[PROJECT1]。[K1] AS [K1]，
MIN（[PROJECT1]。[A1]）AS [A1]，
MAX（[PROJECT1]。[ A2]）AS〔A2〕，
AVG（[PROJECT1]。[A3]）AS [A3]，
STDEV（[PROJECT1]。[A4]）AS [A4]
从（SELECT
DATEADD（分，（（DATEDIFF（分，@ p__linq__4，[PROJECT1]。[时间戳]））/ @ p__linq__5）* @ p__linq__6，@ p__linq__3）AS [K1]，
[PROJECT1 ]。[DCCurrent] / [PROJECT1]。[CurrentMPP] AS〔A1〕，
[PROJECT1]。[DCCurrent] / [PROJECT1]。[CurrentMPP] AS〔A2〕，
[PROJECT1]。 [DCCurrent] / [PROJECT1]。[CurrentMPP] AS [A3]，
[PROJECT1]。[DCCurrent] / [PROJECT1]。[CurrentMPP] AS [A4]
FROM（SELECT
[Extent1]。[时间戳] AS [时间戳]，
[Extent1]。[DCCurrent] AS [DCCurrent]，
[Extent2]。[CurrentMPP] AS [CurrentMPP]
FROM [DBO ]。[StringDatas] AS [Extent1]
INNER JOIN [DBO]。[DCStrings] AS [Extent2] ON [Extent1]。[DCStringID] = [Extent2]。[ID]
INNER JOIN [DBO ]。[DCDistributionBoxes] AS [Extent3] ON [Extent2]。[DCDistributionBoxID] = [Extent3]。[ID]
INNER JOIN [DBO]。[数据采集器] AS [Extent4] ON [Extent3]。[DataLoggerID] 。= [Extent4] [ID]
WHERE（[Extent4] [专案编号] = @ p__linq__0）AND（[Extent1] [时间戳]方式> = @ p__linq__1）AND（[Extent1] [时间戳]< ; @ p__linq__2）
）AS [PROJECT1]
）AS [PROJECT1]
GROUP BY [K]
）AS [GroupBy1]
I have a question about entity framework query building.
Schema
I have a table structure like this:
CREATE TABLE [dbo].[DataLogger](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[ProjectID] [bigint] NULL,
CONSTRAINT [PrimaryKey1] PRIMARY KEY CLUSTERED ( [ID] ASC )
)
CREATE TABLE [dbo].[DCDistributionBox](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[DataLoggerID] [bigint] NOT NULL,
CONSTRAINT [PrimaryKey2] PRIMARY KEY CLUSTERED ( [ID] ASC )
)
ALTER TABLE [dbo].[DCDistributionBox]
ADD CONSTRAINT [FK_DCDistributionBox_DataLogger] FOREIGN KEY([DataLoggerID])
REFERENCES [dbo].[DataLogger] ([ID])
CREATE TABLE [dbo].[DCString] (
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[DCDistributionBoxID] [bigint] NOT NULL,
[CurrentMPP] [decimal](18, 2) NULL,
CONSTRAINT [PrimaryKey3] PRIMARY KEY CLUSTERED ( [ID] ASC )
)
ALTER TABLE [dbo].[DCString]
ADD CONSTRAINT [FK_DCString_DCDistributionBox] FOREIGN KEY([DCDistributionBoxID])
REFERENCES [dbo].[DCDistributionBox] ([ID])
CREATE TABLE [dbo].[StringData](
[DCStringID] [bigint] NOT NULL,
[TimeStamp] [datetime] NOT NULL,
[DCCurrent] [decimal](18, 2) NULL,
CONSTRAINT [PrimaryKey4] PRIMARY KEY CLUSTERED ( [TimeStamp] DESC, [DCStringID] ASC)
)
The [StringData] table as has following storage stats:
- Data space: 26,901.86 MB
- Row count: 131,827,749
- Partitioned: true
- Partition count: 62
Usage
I now want to group the data in the [StringData] table and do some aggregation.
In pure SQL it would look like this:
declare @projectID bigint = 20686;
declare @from datetime = '06.02.2016';
declare @till datetime = '07.02.2016';
declare @interval int = 15;
SELECT
DATEADD(MINUTE, DATEDIFF(MINUTE, 0, [StringData].[TimeStamp] ) / @interval * @interval, 0) AS [TimeStamp]
, AVG([StringData].[DCCurrent] / [DCString].[CurrentMPP]) AS [DCCurrentAvg]
, MIN([StringData].[DCCurrent] / [DCString].[CurrentMPP]) AS [DCCurrentMin]
, MAX([StringData].[DCCurrent] / [DCString].[CurrentMPP]) AS [DCCurrentMax]
, STDEV([StringData].[DCCurrent] / [DCString].[CurrentMPP]) AS [DCCurrentStDev]
, COUNT(*) AS [Count]
FROM [StringData]
JOIN [DCString] ON [DCString].[ID] = [StringData].[DCStringID]
JOIN [DCDistributionBox] ON [DCDistributionBox].[ID] = [DCString].[DCDistributionBoxID]
JOIN [DataLogger] ON [DataLogger].[ID] = [DCDistributionBox].[DataLoggerID]
WHERE [DataLogger].[ProjectID] = @projectID
AND [StringData].[TimeStamp] >= @from
AND [StringData].[TimeStamp] < @till
GROUP BY DATEADD(MINUTE, DATEDIFF(MINUTE, 0, [StringData].[TimeStamp] ) / @interval * @interval, 0)
Excecution timespan: 653ms
Now I created a Entity Framework equivalent (I thought):
var compareData = model.StringDatas
AsNoTracking()
.Where(p => p.DCString.DCDistributionBox.DataLogger.ProjectID == projectID && p.TimeStamp >= from && p.TimeStamp < till)
.Select(d => new
{
TimeStamp = d.Key,
DCCurrentMin = d.Min(v => v.DCCurrent / v.DCString.CurrentMPP),
DCCurrentMax = d.Max(v => v.DCCurrent / v.DCString.CurrentMPP),
DCCurrentAvg = d.Average(v => v.DCCurrent / v.DCString.CurrentMPP),
DCCurrentStDev = DbFunctions.StandardDeviation(d.Select(v => v.DCCurrent / v.DCString.CurrentMPP))
})
.ToList();
And the result of excecution was a timeout (longer the 30 seconds)!?
Attempts
I now took a look into the Entity Framework generated SQL query and looks like this:
SELECT
1 AS [C1],
[Project10].[C1] AS [C2],
[Project10].[C2] AS [C3],
[Project10].[C3] AS [C4],
[Project10].[C4] AS [C5],
[Project10].[C5] AS [C6]
FROM ( SELECT
[Project8].[C1] AS [C1],
[Project8].[C2] AS [C2],
[Project8].[C3] AS [C3],
[Project8].[C4] AS [C4],
(SELECT
STDEV([Project9].[A1]) AS [A1]
FROM ( SELECT
[Project9].[DCCurrent] / [Project9].[CurrentMPP] AS [A1]
FROM ( SELECT
[Extent17].[DCStringID] AS [DCStringID],
[Extent17].[DCCurrent] AS [DCCurrent],
[Extent18].[ID] AS [ID],
[Extent18].[CurrentMPP] AS [CurrentMPP]
FROM [dbo].[StringData] AS [Extent17]
INNER JOIN [dbo].[DCString] AS [Extent18] ON [Extent17].[DCStringID] = [Extent18].[ID]
INNER JOIN [dbo].[DCDistributionBox] AS [Extent19] ON [Extent18].[DCDistributionBoxID] = [Extent19].[ID]
INNER JOIN [dbo].[DataLogger] AS [Extent20] ON [Extent19].[DataLoggerID] = [Extent20].[ID]
WHERE (([Extent20].[ProjectID] = @p__linq__0) OR (([Extent20].[ProjectID] IS NULL) AND (@p__linq__0 IS NULL))) AND ([Extent17].[TimeStamp] >= @p__linq__1) AND ([Extent17].[TimeStamp] < @p__linq__2) AND (([Project8].[C1] = (DATEADD (minute, ((DATEDIFF (minute, @p__linq__4, [Extent17].[TimeStamp])) / @p__linq__5) * @p__linq__6, @p__linq__3))) OR (([Project8].[C1] IS NULL) AND (DATEADD (minute, ((DATEDIFF (minute, @p__linq__4, [Extent17].[TimeStamp])) / @p__linq__5) * @p__linq__6, @p__linq__3) IS NULL)))
) AS [Project9]
) AS [Project9]) AS [C5]
FROM ( SELECT
[Project6].[C1] AS [C1],
[Project6].[C2] AS [C2],
[Project6].[C3] AS [C3],
(SELECT
AVG([Project7].[A1]) AS [A1]
FROM ( SELECT
[Project7].[DCCurrent] / [Project7].[CurrentMPP] AS [A1]
FROM ( SELECT
[Extent13].[DCStringID] AS [DCStringID],
[Extent13].[DCCurrent] AS [DCCurrent],
[Extent14].[ID] AS [ID],
[Extent14].[CurrentMPP] AS [CurrentMPP]
FROM [dbo].[StringData] AS [Extent13]
INNER JOIN [dbo].[DCString] AS [Extent14] ON [Extent13].[DCStringID] = [Extent14].[ID]
INNER JOIN [dbo].[DCDistributionBox] AS [Extent15] ON [Extent14].[DCDistributionBoxID] = [Extent15].[ID]
INNER JOIN [dbo].[DataLogger] AS [Extent16] ON [Extent15].[DataLoggerID] = [Extent16].[ID]
WHERE (([Extent16].[ProjectID] = @p__linq__0) OR (([Extent16].[ProjectID] IS NULL) AND (@p__linq__0 IS NULL))) AND ([Extent13].[TimeStamp] >= @p__linq__1) AND ([Extent13].[TimeStamp] < @p__linq__2) AND (([Project6].[C1] = (DATEADD (minute, ((DATEDIFF (minute, @p__linq__4, [Extent13].[TimeStamp])) / @p__linq__5) * @p__linq__6, @p__linq__3))) OR (([Project6].[C1] IS NULL) AND (DATEADD (minute, ((DATEDIFF (minute, @p__linq__4, [Extent13].[TimeStamp])) / @p__linq__5) * @p__linq__6, @p__linq__3) IS NULL)))
) AS [Project7]
) AS [Project7]) AS [C4]
FROM ( SELECT
[Project4].[C1] AS [C1],
[Project4].[C2] AS [C2],
(SELECT
MAX([Project5].[A1]) AS [A1]
FROM ( SELECT
[Project5].[DCCurrent] / [Project5].[CurrentMPP] AS [A1]
FROM ( SELECT
[Extent9].[DCStringID] AS [DCStringID],
[Extent9].[DCCurrent] AS [DCCurrent],
[Extent10].[ID] AS [ID],
[Extent10].[CurrentMPP] AS [CurrentMPP]
FROM [dbo].[StringData] AS [Extent9]
INNER JOIN [dbo].[DCString] AS [Extent10] ON [Extent9].[DCStringID] = [Extent10].[ID]
INNER JOIN [dbo].[DCDistributionBox] AS [Extent11] ON [Extent10].[DCDistributionBoxID] = [Extent11].[ID]
INNER JOIN [dbo].[DataLogger] AS [Extent12] ON [Extent11].[DataLoggerID] = [Extent12].[ID]
WHERE (([Extent12].[ProjectID] = @p__linq__0) OR (([Extent12].[ProjectID] IS NULL) AND (@p__linq__0 IS NULL))) AND ([Extent9].[TimeStamp] >= @p__linq__1) AND ([Extent9].[TimeStamp] < @p__linq__2) AND (([Project4].[C1] = (DATEADD (minute, ((DATEDIFF (minute, @p__linq__4, [Extent9].[TimeStamp])) / @p__linq__5) * @p__linq__6, @p__linq__3))) OR (([Project4].[C1] IS NULL) AND (DATEADD (minute, ((DATEDIFF (minute, @p__linq__4, [Extent9].[TimeStamp])) / @p__linq__5) * @p__linq__6, @p__linq__3) IS NULL)))
) AS [Project5]
) AS [Project5]) AS [C3]
FROM ( SELECT
[Project2].[C1] AS [C1],
(SELECT
MIN([Project3].[A1]) AS [A1]
FROM ( SELECT
[Project3].[DCCurrent] / [Project3].[CurrentMPP] AS [A1]
FROM ( SELECT
[Extent5].[DCStringID] AS [DCStringID],
[Extent5].[DCCurrent] AS [DCCurrent],
[Extent6].[ID] AS [ID],
[Extent6].[CurrentMPP] AS [CurrentMPP]
FROM [dbo].[StringData] AS [Extent5]
INNER JOIN [dbo].[DCString] AS [Extent6] ON [Extent5].[DCStringID] = [Extent6].[ID]
INNER JOIN [dbo].[DCDistributionBox] AS [Extent7] ON [Extent6].[DCDistributionBoxID] = [Extent7].[ID]
INNER JOIN [dbo].[DataLogger] AS [Extent8] ON [Extent7].[DataLoggerID] = [Extent8].[ID]
WHERE (([Extent8].[ProjectID] = @p__linq__0) OR (([Extent8].[ProjectID] IS NULL) AND (@p__linq__0 IS NULL))) AND ([Extent5].[TimeStamp] >= @p__linq__1) AND ([Extent5].[TimeStamp] < @p__linq__2) AND (([Project2].[C1] = (DATEADD (minute, ((DATEDIFF (minute, @p__linq__4, [Extent5].[TimeStamp])) / @p__linq__5) * @p__linq__6, @p__linq__3))) OR (([Project2].[C1] IS NULL) AND (DATEADD (minute, ((DATEDIFF (minute, @p__linq__4, [Extent5].[TimeStamp])) / @p__linq__5) * @p__linq__6, @p__linq__3) IS NULL)))
) AS [Project3]
) AS [Project3]) AS [C2]
FROM ( SELECT
[Distinct1].[C1] AS [C1]
FROM ( SELECT DISTINCT
DATEADD (minute, ((DATEDIFF (minute, @p__linq__4, [Extent1].[TimeStamp])) / @p__linq__5) * @p__linq__6, @p__linq__3) AS [C1]
FROM [dbo].[StringData] AS [Extent1]
INNER JOIN [dbo].[DCString] AS [Extent2] ON [Extent1].[DCStringID] = [Extent2].[ID]
INNER JOIN [dbo].[DCDistributionBox] AS [Extent3] ON [Extent2].[DCDistributionBoxID] = [Extent3].[ID]
INNER JOIN [dbo].[DataLogger] AS [Extent4] ON [Extent3].[DataLoggerID] = [Extent4].[ID]
WHERE (([Extent4].[ProjectID] = @p__linq__0) OR (([Extent4].[ProjectID] IS NULL) AND (@p__linq__0 IS NULL))) AND ([Extent1].[TimeStamp] >= @p__linq__1) AND ([Extent1].[TimeStamp] < @p__linq__2)
) AS [Distinct1]
) AS [Project2]
) AS [Project4]
) AS [Project6]
) AS [Project8]
) AS [Project10]
Question
Why does Entity Framework seperates every aggregation into a sngle subselect and how can I avoid this to get a performance near to the raw SQL query?
Update 1
This has exact the same SQL query output and timeout result:
var query = from d in model.StringDatas
where d.DCString.DCDistributionBox.DataLogger.ProjectID == projectID
where d.TimeStamp >= fromDate
where d.TimeStamp < tillDate
group d by DbFunctions.AddMinutes(DateTime.MinValue, DbFunctions.DiffMinutes(DateTime.MinValue, d.TimeStamp) / minuteInterval * minuteInterval) into g
select new
{
TimeStamp = g.Key,
DCCurrentMin = g.Min(v => v.DCCurrent / v.DCString.CurrentMPP),
DCCurrentMax = g.Max(v => v.DCCurrent / v.DCString.CurrentMPP),
DCCurrentAvg = g.Average(v => v.DCCurrent / v.DCString.CurrentMPP),
DCCurrentStDev = DbFunctions.StandardDeviation(g.Select(v => v.DCCurrent / v.DCString.CurrentMPP))
};
var queryResult= query.ToList();
解决方案
I've noticed that behavior (bug?) when answering How do I get EF6 to generate efficient SQL containing mulitple aggregate columns?.
The only way I was able to resolve it was by introducing a temporary projection before the group by operation, and the same applies to your case:
var query =
from e in (from d in db.StringDatas.AsNoTracking()
where d.DCString.DCDistributionBox.DataLogger.ProjectID == projectID
&& d.TimeStamp >= fromDate && d.TimeStamp < tillDate
select new { d, s = d.DCString })
group e by DbFunctions.AddMinutes(DateTime.MinValue, DbFunctions.DiffMinutes(DateTime.MinValue, e.d.TimeStamp) / minuteInterval * minuteInterval) into g
let ratio = g.Select(e => e.d.DCCurrent / e.s.CurrentMPP)
select new
{
TimeStamp = g.Key,
DCCurrentMin = ratio.Min(),
DCCurrentMax = ratio.Max(),
DCCurrentAvg = ratio.Average(),
DCCurrentStDev = DbFunctions.StandardDeviation(ratio)
};
EF generated SQL:
SELECT
1 AS [C1],
[GroupBy1].[K1] AS [C2],
[GroupBy1].[A1] AS [C3],
[GroupBy1].[A2] AS [C4],
[GroupBy1].[A3] AS [C5],
[GroupBy1].[A4] AS [C6]
FROM ( SELECT
[Project1].[K1] AS [K1],
MIN([Project1].[A1]) AS [A1],
MAX([Project1].[A2]) AS [A2],
AVG([Project1].[A3]) AS [A3],
STDEV([Project1].[A4]) AS [A4]
FROM ( SELECT
DATEADD (minute, ((DATEDIFF (minute, @p__linq__4, [Project1].[TimeStamp])) / @p__linq__5) * @p__linq__6, @p__linq__3) AS [K1],
[Project1].[DCCurrent] / [Project1].[CurrentMPP] AS [A1],
[Project1].[DCCurrent] / [Project1].[CurrentMPP] AS [A2],
[Project1].[DCCurrent] / [Project1].[CurrentMPP] AS [A3],
[Project1].[DCCurrent] / [Project1].[CurrentMPP] AS [A4]
FROM ( SELECT
[Extent1].[TimeStamp] AS [TimeStamp],
[Extent1].[DCCurrent] AS [DCCurrent],
[Extent2].[CurrentMPP] AS [CurrentMPP]
FROM [dbo].[StringDatas] AS [Extent1]
INNER JOIN [dbo].[DCStrings] AS [Extent2] ON [Extent1].[DCStringID] = [Extent2].[ID]
INNER JOIN [dbo].[DCDistributionBoxes] AS [Extent3] ON [Extent2].[DCDistributionBoxID] = [Extent3].[ID]
INNER JOIN [dbo].[DataLoggers] AS [Extent4] ON [Extent3].[DataLoggerID] = [Extent4].[ID]
WHERE ([Extent4].[ProjectID] = @p__linq__0) AND ([Extent1].[TimeStamp] >= @p__linq__1) AND ([Extent1].[TimeStamp] < @p__linq__2)
) AS [Project1]
) AS [Project1]
GROUP BY [K1]
) AS [GroupBy1]
这篇关于实体框架多个聚合性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
