SQL Server 2012 Lag and Lead Function

Question

SQL Server 2012 Lag and Lead functions aren't performing well over large amounts of data, ~100 million records with ~200 columns.

The combination of

lag(balance, 1, 0) over (partition by loanId order by datetime series)

trying to get previous date's balance. The execution plan says most time consuming part falls in (order by) part.

Is there a way to improve the performance?

can you put up the whole select statement. – Doug Hauf May 02 '14 at 16:50 — Doug Hauf, May 02 '14 at 16:50

score 3 · Answer 1 · answered Jun 24 '13 at 00:17

3

You can improve the performance by having an index on (loanid, datatime series).

answered Jun 24 '13 at 00:17

Gordon Linoff

1 Answers1