> 
> **Rewrote this question 2012-06-20 for clarity, check edits for older version**
> 
> 
> 

Background and Question
-----------------------

I collect data for a couple of sensors on some devices. This data I want to display for the user as curves in a graph, i.e. I want to plot the measured points and draw lines between them. The user is able to decide for what date range he wants to show values for by zooming and panning in the graph.

The number of measured values begins to add up and at this moment I have a number of values in the range of *500'000'000*, and counting. For a single sensor I can at most find about *2'000'000* values as of now, but It will most likely increase. These values are not logged at an even interval (i.e. every second) but instead when a change can be measured (see the difference in the date column below).

```
Date  SensorValue
----- -----------
10    123
30    118
70    114
85    115
90    116
95    117

```

Since the display area for this graph is *1000px* there's no point fetching all those values. Instead I try to fetch at most *1000* values and keep them evenly distributed inside the date range the user has zoomed in upon. Exactly which values inside the date range that is returned doesn't matter as long as they are evenly distributed in terms of the date (this is important to remember since they aren't logged at an even interval).

I store these values in a quite simple table as can be seen below, with only one (clustered) index. Notice that the `Date` is stored as an integer ("unix time") not as a `datetime2` field. The table is partitioned by `Date` if that matters.

```
CREATE TABLE [dbo].[SensorValues](
  [DeviceId] [int] NOT NULL,
  [SensorId] [int] NOT NULL,
  [SensorValue] [int] NOT NULL,
  [Date] [int] NOT NULL,
CONSTRAINT [PK_SensorValues] PRIMARY KEY CLUSTERED 
(
  [DeviceId] ASC,
  [SensorId] ASC,
  [Date] DESC
);

```

I'm searching for one of two things, *either* an improved query that will return these values faster, *or* a different approach on how to extract those values. If anything is unclear, keep asking me and I will try to answer.

My current approach
-------------------

To achieve this I calculate the difference in time for the date range I'm fetching data for and divides this by the number of values I want to fetch at most. I store this in the variable I call `@resolution` below.

When I query the database it calculates and groups the matching rows by the date divided by `@resolution` and picks out one value per group. I do know that this is not a fool-proof algorithm. In some cases it returns more values than requested, but it doesn't matter that much for me since it at least keeps the number of values down to a manageable level.

For a sensor with small amount of data (tens of thousand of values), this is fairly quick but when it comes to a sensor with much data (millions of rows) it slows down and takes a couple of seconds (at least) to query the database. How should one go about querying for this information in a better way?

```
SELECT sv.SensorValue, sv.Date
  FROM SensorValues sv
  INNER JOIN (SELECT MAX(svInner.Date) AS svInner.Date
                FROM SensorValues svInner
                WHERE svInner.SensorId = @sensorid
                  AND svInner.DeviceId = @deviceid
                  AND svInner.Date BETWEEN @startdate AND @stopdate
                  AND svInner.Sensorvalue != -32767
                  AND svInner.SensorValue != -32768
                  GROUP BY svInner.Date / @resolution) j ON sv.Date = j.Date
  WHERE sv.SensorId = @sensorid
    AND sv.DeviceId = @deviceid
    AND sv.SensorValue != -32767
    AND sv.SensorValue != -32768
  ORDER BY sv.Date DESC

```

![Query Plan for above query](https://i.stack.imgur.com/DyxIG.png)

**Example:** If I want to select 3 values between time 0 and 95 from the table example above I do the following.

I calculate the `@resolution` to `(95-0)/3=31` and then for each row between time 0 and 95, calculates `Date/@resolution` (integer division everywhere):

```
Date  SensorValue Date/@resolution
----- ----------- ----------------
10    123         0
30    118         0
70    114         2
85    115         2
90    116         2
95    117         3

```

I then select one value from each group by using `MAX(Date)`.

```
Date  SensorValue Date/@resolution
----- ----------- ----------------
30    118         0
90    116         2
95    117         3

```