Jacob Wilkins (9/27/2016)
The Dixie Flatline (9/26/2016)
you should specify the window frame (generally ROWS UNBOUNDED PRECEDING), because the default is RANGE UNBOUNDED PRECEDING, and which is much slower than specifying the range using the ROWS keyword.
Drew
Do you know why that is, Drew?
It's a matter of the worktable for the spool being in-memory (ROWS) or on-disk (RANGE, or very large frames).
See https://www.simple-talk.com/sql/learn-sql-server/window-functions-in-sql-server-part-3-questions-of-performance/ for an investigation of this.
Cheers!
Jacob is right, but he left out a detail. To calculate a running sum using ROWS, you only need the previous total and the current amount, so the frame is only ever two records. With RANGE, you're not sure if any other records tie with the current record on the sort, so you don't know how many values you need at each point until you've read all of the records.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA