sql server 2008 slow query on sum

Question

I am new to having to manage our sqlserver db. got handed this request.

I have a large table with a million records. Would like to run this report. It is taking over an hour using SQL Server 2008.

Rowid is PK and there is an index on dx and 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

I think this will run faster:

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

Answer 2

This is all much easier in 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 

As you are on 2008 and the above syntax doesn't work you can try adding this index

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

And then using the following query

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);