Sfrgttk

I'm currently designing a transaction table. I realized that calculating running totals for each row will be needed and this might be slow in performance. So I created a table with 1 million rows for testing purposes.

CREATE TABLE [dbo].[Table_1](

    [seq] [int] IDENTITY(1,1) NOT NULL,

    [value] [bigint] NOT NULL,

 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 

(

    [seq] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

And I tried to get 10 recent rows and its running totals, but it took about 10 seconds.

--1st attempt

SELECT TOP 10 seq

    ,value

    ,sum(value) OVER (ORDER BY seq) total

FROM Table_1

ORDER BY seq DESC



--(10 rows affected)

--Table 'Worktable'. Scan count 1000001, logical reads 8461526, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

--Table 'Table_1'. Scan count 1, logical reads 2608, physical reads 516, read-ahead reads 2617, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

--Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

--

--(1 row affected)

--

-- SQL Server Execution Times:

--   CPU time = 8483 ms,  elapsed time = 9786 ms.

I suspected TOP for the reason of slow performance from the plan, so I changed query like this, and it took about 1~2 seconds. But I think this is still slow for production and wondering if this can be improved further.

--2nd attempt

SELECT *

    ,(

        SELECT SUM(value)

        FROM Table_1

        WHERE seq <= t.seq

        ) total

FROM (

    SELECT TOP 10 seq

        ,value

    FROM Table_1

    ORDER BY seq DESC

    ) t

ORDER BY seq DESC



--(10 rows affected)

--Table 'Table_1'. Scan count 11, logical reads 26083, physical reads 1, read-ahead reads 443, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

--

--(1 row affected)

--

-- SQL Server Execution Times:

--   CPU time = 1422 ms,  elapsed time = 1621 ms.

My questions are:

• Why the query from 1st attempt is slower than the 2nd one?


• How can I improve the performance further? I can also change schemas.

Just to be clear, both queries return the same result as below.

When dealing with such a small subset of rows returned, the triangular join is a good option. However, when using window functions you have more options that can increase their performance. The default option for window option is RANGE, but the optimal option is ROWS. Be aware that the difference is not only in the performance, but in the results as well when ties are involved.

The following code is slightly faster than the ones you presented.

SELECT TOP 10 seq

    ,value

    ,sum(value) OVER (ORDER BY seq ROWS UNBOUNDED PRECEDING) total

FROM Table_1

ORDER BY seq DESC