如何最有效地计算已安装基础？

Question

我有一个需求，需要计算在不同国家、不同“环境”下，多年内不同放置/运输方式的装机量，给定每个单位的一组特定“退役率”。这些放置、曲线定义和曲线分配存储在不同的数据库表中（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更有效地计算这些数据？暴力迭代也太慢了，所以这不是一个选项。

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'

- Peter Majeed

---

这听起来像是我通常保存在单独表格中的汇总数据。该表格可以每天或在其他适当的时间间隔内通过预定作业进行更新。 - HABO

这些数据需要是“实时”的吗？还是在计算上有一些延迟也可以接受？ - dana

@dana：感谢您的评论，与HABO的评论类似。这让我意识到这可能会促进与用户进行诚实对话，讨论“实时”数据与“实时”负载的优缺点，因为如果两者等效（在一种情况下需要10分钟加载/几秒钟更新，在另一种情况下则相反），那么两者都是有效的“实时”。 - Peter Majeed

这似乎需要一个分治解决方案。你能否偶然运行多个线程来访问数据库？那么你就可以将问题分解并添加结果。应该是你所需要的。http://en.wikipedia.org/wiki/Divide_and_conquer_algorithm - uvesten

@uvesten：我会研究一下的。你觉得你能发表一个包含这种方法成功实现的答案吗？无论如何，我会在周末进行测试并更新我的进展情况。 - Peter Majeed

Answer 1

针对以下声明做出回应：

我们还尝试了一种SQL触发器解决方案，每次修改放置或费率时更新已安装的基础设施，但这使得批量更新的数据库更新变得不合理缓慢，并且也不可靠。我想如果这真的是最佳选择，这可能值得更多的调查。

你听说过 SQL Service Broker吗？它非常擅长允许您将数据排队进行异步处理。如果触发器本身太慢，您可以使用触发器将记录排队进行异步处理。

- dana

---

不，我还没有！异步处理可能是一个好的选择。我会研究一下并告诉你它的效果如何。 - Peter Majeed

Answer 2

看起来这可能会成为一个问问题就能得到正确答案的情况。事实证明，答案大部分都在我之前提供的查询中，而那个查询是完全低效的。通过以下优化查询，我已经能够获得我所寻找的负载时间。

SELECT
    P.UnitId,
    P.Country,
    P.YearColumn,
    P.Environment,
    P.Placement,
    sum(IBP.Placement * FRR.Rate) InstalledBase
FROM
    @Placements P
    INNER JOIN @Placements IBP ON
        P.UnitId = IBP.UnitId
        AND P.Country = IBP.Country
        AND P.Environment = IBP.Environment
        AND P.YearColumn >= IBP.YearColumn
    INNER JOIN @CurveAssignments RR ON
        IBP.UnitId = RR.UnitId
        AND IBP.Country = RR.Country
        AND IBP.Environment = RR.Environment
        AND IBP.YearColumn = RR.YearColumn
    INNER JOIN @CurveDefinitions FRR ON
        Rr.RateId = FRR.RateId
        AND P.YearColumn - IBP.YearColumn = FRR.YearOffset
GROUP BY
    P.UnitId,
    P.YearColumn,
    P.Country,
    P.Environment,
    P.Placement

- Peter Majeed