Sql-server – Is it possible to speed up this PARTITION BY query

sql serversql-server-2008-r2window functions

I've got a timeserie table. Each datapoint has also a statut field which
indicates its providing source. I'd like to request last available datapoint
as of date T but there could be several results, each with a different statut.

Therefore, I'm using a "statut preference list" to retrieve datapoint in
accordance with providers priority.

Here's the query : (in this example, my preferred ordered statuts would be
10 then 9 then 1)

DECLARE @MyStatuts TABLE (StatutOrder tinyint, StatutValue tinyint) 
INSERT INTO @MyStatuts VALUES(1, 10) 
INSERT INTO @MyStatuts VALUES(2, 9) 
INSERT INTO @MyStatuts VALUES(3, 1) 

SELECT TOP 1 Id, Date, Statut, Value 
FROM (
    SELECT data.Id, data.Date, data.Statut, data.Value, s.StatutOrder, 
           MIN(s.StatutOrder)
             OVER(PARTITION BY data.Date) As MinStatutOrder
    FROM [DATA].[TimeSerie] as data
      JOIN @MyStatuts as s on data.Statut = s.StatutValue
    WHERE Id = 'timeserieid' AND Date <= '24/04/2014'
    ) AS T
WHERE T.StatutOrder = T.MinStatutOrder 
ORDER BY T.Date DESC

Do you have any ideas how I can speed that up ? I already used Index on [TimeSerie] table…

Here's table code :

CREATE TABLE [DATA].[TimeSerie](
[Id] [varchar](25) NOT NULL,
[Date] [date] NOT NULL,
[Statut] [tinyint] NOT NULL,
[Value] [decimal](19, 8) NOT NULL,
CONSTRAINT [PK_Price] PRIMARY KEY CLUSTERED 
(
[Id] ASC,
[Date] ASC,
[Statut] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
       ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Link to the execution plan

Best Answer

I think your query uses only the rows where the date is the maximum so it is equivalent to this:

SELECT TOP (1) data.Id, data.Date, data.Statut, data.Value
FROM [DATA].[TimeSerie] AS data
  JOIN @MyStatuts AS s ON data.Statut = s.StatutValue
WHERE data.Id = 'timeserieid' 
  AND data.Date = ( SELECT MAX(m.Date)
                    FROM [DATA].[TimeSerie] AS m
                    WHERE m.Id = 'timeserieid'
                      AND m.Date <= '20140424'
                  )
ORDER BY s.StatutOrder ASC ;
Related Question