我有一个需求,需要计算在不同国家、不同“环境”下,多年内不同放置/运输方式的装机量,给定每个单位的一组特定“退役率”。这些放置、曲线定义和曲线分配存储在不同的数据库表中(DDL和示例数据如下,也在SQLFiddle.com上)。计算已安装的基础公式如下:
其中1990年是我们拥有放置数据的第一年。
问题:
对于包含3到16百万行单位/国家/环境/年份放置组合数据集的计算比目标加载/计算时间长得多的时间,需要30秒至1分钟。
Sql Server方法
当使用PIVOT使每年成为自己的列时,我会得到任何从100,000到400,000个原始数据(放置+费率)的返回行,需要大约8-15秒。然而,如果我通过包含在下面的SQL语句中手动计算它,则需要至少10分钟。
我们还尝试了一个SQL触发器解决方案,该解决方案在每次修改放置或费率时更新了已安装的基础,但这使批量更新的数据库更新变得不合理缓慢,并且也不可靠。如果这真的是最佳选择,我想这可能值得进一步研究。
Excel-VSTO方法(到目前为止是最快的方法):
这些数据最终以由C# VSTO驱动的Excel工作簿形式出现,其中数据通过一系列VLOOKUP计算,但在加载150,000个放置位置跨越6年,每个单元格约20个VLOOKUP(约20百万个VLOOKUP)时,Excel会崩溃。当VLOOKUP分批完成并将公式转换为值时,它不会崩溃,但仍需要比一分钟更长的时间来计算。
问题:
是否存在某种数学或编程结构,可以帮助我通过C#或SQL更有效地计算这些数据?暴力迭代也太慢了,所以这不是一个选项。
问题:
对于包含3到16百万行单位/国家/环境/年份放置组合数据集的计算比目标加载/计算时间长得多的时间,需要30秒至1分钟。
Sql Server方法
当使用PIVOT使每年成为自己的列时,我会得到任何从100,000到400,000个原始数据(放置+费率)的返回行,需要大约8-15秒。然而,如果我通过包含在下面的SQL语句中手动计算它,则需要至少10分钟。
我们还尝试了一个SQL触发器解决方案,该解决方案在每次修改放置或费率时更新了已安装的基础,但这使批量更新的数据库更新变得不合理缓慢,并且也不可靠。如果这真的是最佳选择,我想这可能值得进一步研究。
Excel-VSTO方法(到目前为止是最快的方法):
这些数据最终以由C# VSTO驱动的Excel工作簿形式出现,其中数据通过一系列VLOOKUP计算,但在加载150,000个放置位置跨越6年,每个单元格约20个VLOOKUP(约20百万个VLOOKUP)时,Excel会崩溃。当VLOOKUP分批完成并将公式转换为值时,它不会崩溃,但仍需要比一分钟更长的时间来计算。
问题:
是否存在某种数学或编程结构,可以帮助我通过C#或SQL更有效地计算这些数据?暴力迭代也太慢了,所以这不是一个选项。
DECLARE @Placements TABLE
(
UnitId int not null,
Environment varchar(50) not null,
Country varchar(100) not null,
YearColumn smallint not null,
Placement decimal(18,2) not null,
PRIMARY KEY (UnitId, Environment, Country, YearColumn)
)
DECLARE @CurveAssignments TABLE
(
UnitId int not null,
Environment varchar(50) not null,
Country varchar(100) not null,
YearColumn smallint not null,
RateId int not null,
PRIMARY KEY (UnitId, Environment, Country, YearColumn)
)
DECLARE @CurveDefinitions TABLE
(
RateId int not null,
YearOffset int not null,
Rate decimal(18,2) not null,
PRIMARY KEY (RateId, YearOffset)
)
INSERT INTO
@Placements
(
UnitId,
Country,
YearColumn,
Environment,
Placement
)
VALUES
(
1,
'United States',
1991,
'Windows',
100
),
(
1,
'United States',
1990,
'Windows',
100
)
INSERT INTO
@CurveAssignments
(
UnitId,
Country,
YearColumn,
Environment,
RateId
)
VALUES
(
1,
'United States',
1991,
'Windows',
1
)
INSERT INTO
@CurveDefinitions
(
RateId,
YearOffset,
Rate
)
VALUES
(
1,
0,
1
),
(
1,
1,
0.5
)
SELECT
P.UnitId,
P.Country,
P.YearColumn,
P.Placement *
(
SELECT
Rate
FROM
@CurveDefinitions CD
INNER JOIN @CurveAssignments CA ON
CD.RateId = CA.RateId
WHERE
CA.UnitId = P.UnitId
AND CA.Environment = P.Environment
AND CA.Country = P.Country
AND CA.YearColumn = P.YearColumn - 0
AND CD.YearOffset = 0
)
+
(
SELECT
Placement
FROM
@Placements PP
WHERE
PP.UnitId = P.UnitId
AND PP.Environment = P.Environment
AND PP.Country = P.Country
AND PP.YearColumn = P.YearColumn - 1
)
*
(
SELECT
Rate
FROM
@CurveDefinitions CD
INNER JOIN @CurveAssignments CA ON
CD.RateId = CA.RateId
WHERE
CA.UnitId = P.UnitId
AND CA.Environment = P.Environment
AND CA.Country = P.Country
AND CA.YearColumn = P.YearColumn
AND CD.YearOffset = 1
) [Installed Base - 1993]
FROM
@Placements P
WHERE
P.UnitId = 1
AND P.Country = 'United States'
AND P.YearColumn = 1991
AND P.Environment = 'Windows'