For background, I am on an instance of SQL Server 2012 Enterprise Edition.
I have three tables that join on the same set of three keys - all of them are INT. All tables have their keys clustered in the following order:
- Key-Time (represents a week)
The main table left joins to the other two tables, and the result is fed into a set of windowing functions. The purpose is to sum a specific number of weeks' data based on Product-Location, like so:
SUM(Metric) OVER (PARTITION BY Key-Product, Key-Location ORDER BY Key-Time ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS Result
The where clause limits the time frame by Key-Time, and an outer query discards any results that would not have 3 preceding rows. There are 15 windowing functions calculating different metrics.
The issue comes when sorting for the partitioning function. This is the first part of my query plan:
When the tables are joined, the results are already sorted by Time-Product-Location (the key). The windowing function needs to order by Product-Location then Time, hence the second sort. This query takes just over two minutes to execute for the 22 million rows shown, but in production the tables are much larger.
We have other queries that depend on the Time-Location-Product ordering, so changing the order of the keys won't be an option. Also, the two left join tables contain the metrics data, but the main table must be present to ensure accuracy.
How would I go about speeding this up? Is there room for improvement here?
UPDATE: Here is the information when I hover over the sort: