SQL Join 需要很长时间

Question

我有两个表

(1) MonthlyTarget {SalesManCode, TargetMonthYear, TargetValue}; 该表有 1966177 行。

(2) MonthlySales  {SalesManCode, SaleDate, AchievedValue};

该表有 400310 行。

我必须进行一个查询，生成如下表所示的结果：

{SalesManCode, JanTar, JanAch, FebTar, FebAch,....., DecTar, DecAch}

问题是，连接这两个表需要很长时间。

应该查询什么？

如何优化查询？

我不想考虑索引。

Answer 1

好吧，如果您不想考虑索引，那么您将始终执行全表扫描，并且性能不会提高。

Answer 2

您的 MonthlyTarget 表中似乎缺少一些列，即“T​​argetDate”列。

除了每个人都已经说过的关于索引的内容之外，有时分而治之的方法确实很有帮助。 不要将 1966177 行表连接到 400310 行表，而是创建小型临时表并将它们连接在一起：

CREATE TABLE #MonthlySalesAgg
(
    SalesManCode int,
    JanTar money,
    FebTar money,
    MarTar money,
    AprTar money,
    MayTar money,
    JunTar money,
    JulTar money,
    AugTar money,
    SepTar money,
    OctTar money,
    NovTar money,
    DecTar money

    PRIMARY KEY CLUSTERED (SalesManCode)
)

INSERT INTO #MonthlySalesAgg
SELECT *
FROM
(SELECT SalesManCode, TargetValue, SaleMonth = Month(TargetDate) FROM MonthlyTarget) as temp
PIVOT
(
    Max(TargetValue)
    FOR [SaleMonth] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) as p

CREATE TABLE #MonthlyTargetAgg
(
    SalesManCode int,
    JanAch money,
    FebAch money,
    MarAch money,
    AprAch money,
    MayAch money,
    JunAch money,
    JulAch money,
    AugAch money,
    SepAch money,
    OctAch money,
    NovAch money,
    DecAch money

    PRIMARY KEY CLUSTERED (SalesManCode)
)

INSERT INTO #MonthlyTargetAgg
SELECT * FROM
(SELECT SalesManCode, AchievedValue, SaleMonth = Month(SaleDate) FROM MonthlySales) as temp
PIVOT
(
    Sum(AchievedValue)
    FOR [SaleMonth] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) as p

上面的查询创建两个中间表，它们应包含与 SalesMan 表相同数量的记录。 加入它们很简单：

SELECT *
FROM #MonthlyTargetAgg target
INNER JOIN #MonthlySalesAgg sales ON target.SalesManCode = sales.SalesManCode

如果您发现自己需要始终按月提取数据，请将代码移至视图中。

PIVOT 需要 SQL Server 2005 或更高版本，并且它通常是一个非常有用的运算符。 希望 SQL Server 2008 将允许用户一次旋转多个列，这将导致比上面显示的查询更简单的结果。

使用 SQL Server 2000：

PIVOT 是语法糖。 例如，

SELECT * FROM
(SELECT SalesManCode, AchievedValue, SaleMonth = Month(SaleDate) FROM MonthlySales) as temp
PIVOT
(
    Sum(AchievedValue)
    FOR [SaleMonth] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) as p

变成

SELECT
    SalesManCode,
    [1] = Sum(case SaleMonth when 1 then AchievedValue else 0 end),
    [2] = Sum(case SaleMonth when 2 then AchievedValue else 0 end),
    [3] = Sum(case SaleMonth when 3 then AchievedValue else 0 end),
    [4] = Sum(case SaleMonth when 4 then AchievedValue else 0 end),
    [5] = Sum(case SaleMonth when 5 then AchievedValue else 0 end),
    [6] = Sum(case SaleMonth when 6 then AchievedValue else 0 end),
    [7] = Sum(case SaleMonth when 7 then AchievedValue else 0 end),
    [8] = Sum(case SaleMonth when 8 then AchievedValue else 0 end),
    [9] = Sum(case SaleMonth when 9 then AchievedValue else 0 end),
    [10] = Sum(case SaleMonth when 10 then AchievedValue else 0 end),
    [11] = Sum(case SaleMonth when 11 then AchievedValue else 0 end),
    [12] = Sum(case SaleMonth when 12 then AchievedValue else 0 end)
FROM
    (SELECT SalesManCode, AchievedValue, SaleMonth = Month(SaleDate) FROM MonthlySales) as temp
GROUP BY SalesManCode

Answer 3

检查表上的索引是否正确。 如果不查看数据库本身，几乎不可能说出来，但 99% 的慢连接是由于不正确或丢失的表索引造成的。

Answer 4

我不想考虑索引。

你必须考虑索引。 无论您如何编写查询，数据库引擎别无选择，只能扫描每个表以查找连接，并且它可能会一遍又一遍地执行此操作。 你没有选择。

如果您因为无法控制数据库而不想搞乱索引，请考虑将数据导出到本地 SQL Express 实例。

哎呀，即使将数据导出到平面文件，按 SalesManCode 对文件进行排序并编写一个简单的程序来读取和匹配它们也会更快。

Answer 5

除了解雇一群销售人员之外，请考虑以下选项：

• 创建一个批处理流程以每晚运行并使用查询的输出填充一些报告表
• 创建所需查询的索引/物化视图（尽管您需要在索引视图上创建索引，所以这可能会破坏您的无索引规则）

Answer 6

你有过滤器吗？ 您能否将一些部分结果存储在临时表中，然后在减小数据大小后加入到其余数据中？

Answer 7

如果无法选择索引，则唯一加快速度的其他方法是将其放在更快的服务器上。 有些事情告诉我索引会更容易。