a follow-up to my previous post - I looked at the execution plan, here’s what it advices:
/*
Missing Index Details from SQL_Window_Functions.sql - MO.Demo Database NAV NA (7-0) (Mo\Modric (66))
The Query Processor estimates that implementing the following index could improve the query cost by 23.6078%.
*/
/*
USE [Demo Database NAV NA (7-0)]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[CRONUS Canada, Inc_$G_L Entry] ([Posting Date])
INCLUDE ([G_L Account No_],[Amount])
GO
*/
there IS one not-good-practice issue in the query - DISTINCT clause is resource-heavy (13% in this case), but even with it and without that recommended the execution was instantaneous in DEMO DB
In real DB with 1’965’226 records in GL Entry table it took 1min 24sec, but it was not run on a server hardware, I did it at home (on FRI evening BTW!) on single i3 CPU desktop PC, well, disks are SSDs with 550+ Mbit/sec transfers. Anyway, 49% was again eaten by DISTINCT clause - now I understand why it is said to be used only if you can’t get along otherwise…
updated: Got rid of DISTINCT clause:
WITH ctx AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY GLE.[G_L Account No_], GLE.[Posting Date] ORDER BY GLE.[G_L Account No_], GLE.[Posting Date] ) AS SimilarRowNo,
GLE.[G_L Account No_] AS GLAcc,
GLE.[Posting Date] AS Tx_Date,
FORMAT(GLE.[Posting Date],‘yyyy-MM’) AS Tx_YearMonth,
SUM(GLE.[Amount]) OVER ( PARTITION BY GLE.[G_L Account No_], GLE.[Posting Date] ) AS Net_Change_By_Day,
SUM(GLE.[Amount]) OVER ( PARTITION BY GLE.[G_L Account No_], FORMAT(GLE.[Posting Date],‘yyyy-MM’) ) AS Net_Change_By_YearMonth,
SUM(GLE.[Amount]) OVER ( PARTITION BY GLE.[G_L Account No_] ) AS Ending_Balance
FROM somecompany$G_L Entry] AS GLE
WHERE GLE.[Posting Date] < ‘2014-01-01’
)
SELECT
GLAcc,
FORMAT(Tx_Date,‘d’,‘en-CA’) AS Tx_Date,
Tx_YearMonth,
Net_Change_By_Day,
Net_Change_By_YearMonth,
Ending_Balance
FROM ctx
WHERE SimilarRowNo = 1
exec time on ~2M rows - 49 seconds