SQL Server 2019: Slow Performance with Window Functions on Large Dataset
I've been struggling with this for a few days now and could really use some help. I keep running into Hey everyone, I'm running into an issue that's driving me crazy... I'm experiencing important performance optimization when using window functions on a large dataset in SQL Server 2019. I have a table with over 1 million rows and I'm trying to calculate a rolling sum using the `SUM()` function as a window function. The query runs extremely slow, taking over 10 seconds to return results, which is unacceptable for my application. Here’s the query I’m using: ```sql SELECT OrderID, OrderDate, Amount, SUM(Amount) OVER (ORDER BY OrderDate ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS RollingSum FROM Orders WHERE OrderDate >= '2023-01-01' ORDER BY OrderDate; ``` I’ve tried creating an index on the `OrderDate` column to speed up the filtering, but it doesn't seem to help much. I also ensured that statistics are updated for better optimization. Despite these efforts, the performance remains unsatisfactory. Is there a better way to write this query or an alternative approach to calculating the rolling sum without sacrificing performance? Any suggestions on optimizing window functions in SQL Server would be greatly appreciated. My development environment is Linux. What am I doing wrong?