SQL Server 2008 慢速查询总和

Question

我是管理我们的sqlserver数据库的新手。 收到了这个请求。

我有一个包含一百万条记录的大表。 想要运行此报告。 使用 SQL Server 2008 需要一个多小时。

Rowid 是 PK，在 dx 和 varcharfield 上有一个索引。

SELECT T1.VarcharField,
       T1.DX,
       T1.ROWID,
       (SELECT Sum(t2.QC) AS Total
        FROM   tbl t2
        WHERE  T2.VarcharField = T1.VarcharField
               AND t2.dx <= t1.dx) AS Total
FROM   tbl AS T1 

Answer 1

> 这在SQL Server 2012中要容易得多。

SELECT T1.VarcharField,
       T1.DX,
       T1.ROWID,
       SUM(QC) OVER (PARTITION BY VarcharField ORDER BY DX) AS Total
FROM   tbl AS T1 

由于您在 2008 年并且上述语法不起作用，您可以尝试添加此索引

CREATE INDEX ix ON tbl(VarcharField,DX) INCLUDE (ROWID, QC)

然后使用以下查询

WITH RecursiveCTE
     AS (SELECT VarcharField,
                DX,
                ROWID,
                QC,
                QC AS Total
         FROM   (SELECT *,
                        ROW_NUMBER() OVER (PARTITION BY VarcharField ORDER BY DX) AS RN
                 FROM   tbl) T
         WHERE  RN = 1
         UNION ALL
         SELECT R.VarcharField,
                R.DX,
                R.ROWID,
                R.QC,
                R.Total
         FROM   (SELECT T.*,
                        T.QC + Total AS Total,
                        rn = ROW_NUMBER() OVER (ORDER BY T.DX)
                 FROM   tbl T
                        JOIN RecursiveCTE R
                          ON R.VarcharField = T.VarcharField
                             AND R.DX < T.DX) R
         WHERE  R.rn = 1)
SELECT VarcharField,
       DX,
       ROWID,
       Total
FROM   RecursiveCTE
OPTION (MAXRECURSION 0); 

Answer 2

我认为这会运行得更快：

SELECT
  T1.VarcharField,
  T1.DX,
  T1.ROWID,
  SUM(t2.QC) AS Total
FROM
  tbl t2
  JOIN tb1 T1 ON T2.VarcharField = T1.VarcharField
GROUP BY 
  T1.VarcharField,
  T1.DX,
  T1.ROWID
WHERE
  t2.dx <= t1.dx