1

We have a SQL Server DB that contains a large table (>100M rows) with archive values, basically in the form of [Id], [StationId], [DatapointId], [Timestamp], [Value]. As the stations do not get polled, there aren't any regular intervals at which a station sends a datapoint, so the timestamps are set to arbitrary times.

For our web visualization, I need to extract entries from this table for a pannable chart that I'm building with D3.js. I need to query by StationId, DatapointId, StartDate and EndDate. This is simple and works as expected, but for the line chart that it feeds, I do need additional data, namely the first value before the chosen time interval, and (if there is one) the first value after the interval. This is neccessary to draw the line from the left resp. right boundary of the chart to the first/last value within the interval (otherwise the data within the chart would look somewhat "isolated" because the line stops before the charts boundaries, like there's no data before or after the values in the chosen interval).

At the moment I need to run 3 queries: one to get the data within the interval, one to retrieve the first matching row before StartDate, and anotherone to get the first matching row after EndDate.

It works, and after creating a covering index the speed is ok, but I think there might be a way to reduce the number of roundtrips to the DB. Do you have any idea?

5
  • 1
    Hi, could you add the query you tried,your sql server version, and if possible the table definition + sample data? Commented Feb 21, 2019 at 8:37
  • Do you notice any performance issues as a result of the three trips to the database? Unless it's causing an issue, your existing solution might be good enough. There are always windowing functions you can use (SQL Server 2012+) if you really want to reduce the number of trips, but basically you need to decide if it's worth the effort. Commented Feb 21, 2019 at 9:07
  • Please edit the question andd the query you use. It's not clear if you query for a single StationID, for a single (StationID, DatapointID) combination or for all stations in a specific time range. Commented Feb 21, 2019 at 9:19
  • Thanks for your comments. I did not show my queries because I did not want to bias your answers, they are very basic select/select top where thingies anyway. I need to query for a single StationId and a single DatapointId at a time.
    – Rob
    Commented Feb 21, 2019 at 9:25
  • @RandolphWest: The performance is ok, but the chance of reducing the execution time by 2/3 is very tempting. Thanks for mentioning windowing functions, I'll have a look at that!
    – Rob
    Commented Feb 21, 2019 at 9:27

2 Answers 2

1

I assume you query for all data (from all stations) in a specific time range.

You can probably write this with window functions (and it might be more efficient) but here is one method, with OUTER APPLY.

(The ds subquery is only used to get a list of distinct StationID. It should be replaced with the Stations table you likely have):

SELECT
    ds.[StationID],                   -- station
    st.ExtendedStartDate,             -- extended start and
    en.ExtendedEndDate                -- and end dates

    tt.[Timestamp],                   -- timestamps and data
    tt.[DatapointId],
    tt.[Value]
FROM 
    ( SELECT DISTINCT [StationID]
      FROM ArchiveTable
    ) AS ds
  OUTER APPLY
    ( SELECT TOP (1) s.[Timestamp] AS ExtendedStartDate
      FROM ArchiveTable AS s
      WHERE s.[StationId] = ds.[StationId]
        AND s.[Timestamp] < @StartDate
      ORDER BY s.[Timestamp] DESC
    ) AS st
  OUTER APPLY
    ( SELECT TOP (1) e.[Timestamp] AS ExtendedEndDate
      FROM ArchiveTable AS e
      WHERE e.[StationId] = ds.[StationId]
        AND e.[Timestamp] > @EndDate
      ORDER BY e.[Timestamp] ASC
    ) AS en
  OUTER APPLY
    ( SELECT t.*
      FROM ArchiveTable AS t
      WHERE t.[StationId] = ds.[StationId]
        AND t.[Timestamp] >= COALESCE(st.ExtendedStartDate, @StartDate)
        AND t.[Timestamp] <= COALESCE(en.ExtendedEndDate, @EndDate)  
    ) AS tt ;
1

Taking roundtrips to the database should not be an issue, if you want to send them to the db as one query, you could do that by using CTE's and UNION ALL operators.

I need to query for a single StationId and a single DatapointId at a time

The query below filters on StationId, DatapointID and the StartDate and EndDate using the timestamp column.

--Filter params

DECLARE @startdate datetime2 = '2019-02-20 8:48:08.633'
DECLARE @EndDate datetime2 = '2019-02-20 10:48:08.633'
DECLARE @stationid int = 1
DECLARE @DatapointId int = 1

--Query

;WITH CTE AS
(
SELECT TOP(1) [StationId],[DatapointId],[TimeStamp] 
FROM PollingTable 
WHERE [TimeStamp] < @startdate
      AND StationId =  @stationId 
      AND [DatapointId] = @Datapointid
ORDER BY [TimeStamp] desc
)
,CTE2 AS
(
SELECT TOP(1) [StationId],[DatapointId],[TimeStamp] 
FROM PollingTable 
WHERE [TimeStamp] > @EndDate
      AND StationId =  @stationId 
      AND [DatapointId] = @Datapointid
ORDER BY [TimeStamp] asc
)
SELECT [StationId],[DatapointId],[TimeStamp]
FROM CTE
UNION ALL
SELECT [StationId],[DatapointId],[TimeStamp] 
FROM PollingTable 
WHERE [TimeStamp] >= @startdate 
      AND [TimeStamp] <= @EndDate
      AND StationId =  @stationId 
      AND [DatapointId] = @Datapointid
UNION ALL
SELECT [StationId],[DatapointId],[TimeStamp] 
FROM CTE2;

Afterwards, this index was added

CREATE INDEX IX_StationId_DatapointId_TimeStamp on PollingTable(StationId,DatapointId,TimeStamp)

Resulting in seeks on my end, but you might have to swap columns around depending on how your dataset is distributed. My dataset is nowhere near yours.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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