16

I run a SQL Server 2016 database where I have the following table with 100+ millions rows:

StationId | ParameterId |       DateTime       | Value
    1     |      2      | 2020-02-04 15:00:000 |  5.20
    1     |      2      | 2020-02-04 14:00:000 |  5.20
    1     |      2      | 2020-02-04 13:00:000 |  5.20
    1     |      3      | 2020-02-04 15:00:000 |  2.81
    1     |      3      | 2020-02-04 14:00:000 |  2.81
    1     |      4      | 2020-02-04 15:00:000 |  5.23
    2     |      2      | 2020-02-04 15:00:000 |  3.70
    2     |      4      | 2020-02-04 15:00:000 |  12.20
    3     |      2      | 2020-02-04 15:00:000 |  1.10

This table has a clustered index for StationId, ParameterId and DateTime, in this order, all ascending.

What I need is, for each unique pair StationId - ParameterId, return the most recent value from DateTime column:

StationId | ParameterId |       LastDate       
    1     |      2      | 2020-02-04 15:00:000 
    1     |      3      | 2020-02-04 15:00:000 
    1     |      4      | 2020-02-04 15:00:000 
    2     |      2      | 2020-02-04 15:00:000 
    2     |      4      | 2020-02-04 15:00:000 
    3     |      2      | 2020-02-04 15:00:000 

What I'm doing now is the following query, which takes around 90 to 120 seconds to run:

    SELECT StationId, ParameterId, MAX(DateTime) AS LastDate
    FROM  MyTable WITH (NOLOCK)
    GROUP BY StationId, ParameterId

I've also seen many posts suggesting the following, which takes 10+ minutes to run:

    SELECT StationId, ParameterId, DateTime AS LastDate
    FROM
    (
       SELECT StationId, ParameterId, DateTime
       ,ROW_NUMBER() OVER (PARTITION BY StationId,ParameterIdORDER BY DateTime DESC) as row_num
       FROM  MyTable WITH (NOLOCK)
    )
    WHERE row_num = 1

Even in the best case (using GROUP BY clause and MAX aggregate funcition), the execution plan doesn't indicate an Index Seek:

Query execution plan

Index Scan detail

I wonder if there's a better way to perform this query (or to build the index) in order to achieve better execution time.

  • If the unique instances are in the near past then you can try adding WHERE DateTime > DATEADD(DAY, -7, GETDATE()) – MonkeyZeus Feb 6 at 18:49
  • @MonkeyZeus while this is true for most of them, I cannot guarantee that for all of them. Some stations may be inactive for months, or even years and then comeback online. – Pedro Ludovico Bozzini Feb 7 at 21:49
23

If you have a small-enough number of (StationID, ParameterID) pairs, then try a query like this:

select StationID, ParameterID, m.DateTime LastDate 
from StationParameter sp
cross apply 
  (
     select top 1 DateTime 
     from MyTable 
     where StationID = sp.StationID
      and ParameterID = sp.ParameterID
     order by DateTime desc
  ) m

To enable SQL Server to perform a lookup, seeking the latest DateTime for each (StationID,ParameterID) pair.

With only a Clustered Index on (StationID, ParameterID, DateTime), there's no way for SQL Server to discover the distinct (StationID, ParameterID) pairs without scanning the leaf level of the index, and it can find the largest DateTime while it's scanning.

Also at 100M+ rows, this table might be better as a Clustered Columnstore instead of a BTree Clustered Index.

| improve this answer | |
  • 4
    The number of (StationID, ParameterID) pairs is around 300. Index Seek is now being performed and execution time dropped to less than 1 second with your suggestion – Pedro Ludovico Bozzini Feb 4 at 21:01
0

If the performance is really critical and you are about frequently ask your table for the most recent date... Why not create lookup table of station and parameter as key with most recent time stamp. You need to update this table each time you modify the big one, but this way you have your results when you need it I milliseconds.

| improve this answer | |
  • Probably worth reading the question again. – MJH Feb 5 at 20:11
0

With the CTE row_number approach, try creating a nonclustered index on StationId, ParameterId, DateTime desc. I've found having an index with the proper sort order that my partition by order clause uses has improved performance for me.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.