Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Background

I have a couple of devices, each with a couple of sensors. I log these every now and then and stores them in a table described below. When someone requests a web page, I fetch a couple of these values (the latest logged) one by one and displays them to the user. But currently this takes too long time because there are too many values that needs to be fetched, the fetch takes about 8ms per value and in total we talk about around 300ms increase in total page load time - for a relatively good page.

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

The table is partitioned weekly on the Date column.

What I do now

So, what I do is the following. I select the largest value in each partition that is before the current date/time. and pick out the largest value.

SELECT TOP (1) ca.SensorValue, ca.Date
  FROM sys.partitions AS p
  CROSS APPLY
  (
  SELECT TOP (1) v.Date, v.SensorValue
    FROM SensorValue AS v
    WHERE $PARTITION.SensorValues_Date_PF(v.Date) = p.[partition_number]
    AND v.DeviceId = @fDeviceId
    AND v.SensorId = @fSensorId
    AND v.Date <= @fDate
    ORDER BY v.Date DESC
  ) AS ca
  WHERE p.[partition_number] <= $PARTITION.SensorValues_Date_PF(@fDate)
  AND p.[object_id] = OBJECT_ID(N'dbo.SensorValues', N'U')
  AND p.index_id = 1
  ORDER BY p.[partition_number] DESC, ca.Date DESC;

What I want to do

I want to select all values in one query. E.g. select the latest value for DeviceId=1 and SensorId=1,2,3,4,5. I have come up with the following so far, where I select with the IN keyword to get values for multiple sensors. However, I still need to group them and sort out the one with the highest date. I'm thinking of adding a GROUP BY clause, but don't know how to get it right (the ones I've tried has failed so far).

SELECT ca.SensorValue, ca.Date
  FROM sys.partitions AS p
  CROSS APPLY
  (
  SELECT TOP (1) v.Date, v.SensorValue
    FROM SensorValue AS v
    WHERE $PARTITION.SensorValues_Date_PF(v.Date) = p.[partition_number]
    AND v.DeviceId = @fDeviceId
    AND v.SensorId IN (@fSensorId1, @fSensorId2, @fSensorId3)
    AND v.Date <= @fDate
    ORDER BY v.Date DESC
  ) AS ca
  WHERE p.[partition_number] <= $PARTITION.SensorValues_Date_PF(@fDate)
  AND p.[object_id] = OBJECT_ID(N'dbo.SensorValues', N'U')
  AND p.index_id = 1
  ORDER BY p.[partition_number] DESC, ca.Date DESC;
share|improve this question
1  
You may want to consider a lower-tech approach: instead of using Partition functions in a cross apply, use an old-fashioned Calendar Table to get week values for the dates and JOIN on that. –  JNK Nov 27 '12 at 14:34
    
@JNK I suppose you mean something like this made2mentor.com/2011/04/calendar-tables-why-you-need-one. But I can't actually see how that would help me. I would certainly get rid of the partition lookup but I would still have trouble getting the latest value for each sensor, wouldn't I? I'm using the partition lookup to speed things up since I have a couple of million values per sensor. And a couple of thousand sensors. I.e. it's a large table and I'm using it to help SQL Server to pinpoint which partition to look at and not to look at. –  m__ Nov 27 '12 at 14:47
1  
SQL Server won't need help if it knows the date you want - that's the benefit of using partitioning, the engine knows what's on what partition as long as you give it the partition field. –  JNK Nov 27 '12 at 15:01
2  
Informational: the APPLY pattern used in the question is a workaround for the poor performance of MIN and MAX aggregates on partitioned tables. Itzik Ben-Gan wrote an article about it. –  Paul White Nov 28 '12 at 11:47
add comment

1 Answer

up vote 7 down vote accepted

First things first, I notice that your 'what I do now' query:

SELECT TOP (1)
    ca.SensorValue,
    ca.Date
FROM sys.partitions AS p
CROSS APPLY
(
    SELECT TOP (1)
        v.Date, 
        v.SensorValue
    FROM SensorValues AS v
    WHERE 
        $PARTITION.SensorValues_Date_PF(v.Date) = p.[partition_number]
        AND v.DeviceId = @fDeviceId
        AND v.SensorId = @fSensorId
        AND v.Date <= @fDate
    ORDER BY 
        v.Date DESC
) AS ca
WHERE 
    p.[partition_number] <= $PARTITION.SensorValues_Date_PF(@fDate)
    AND p.[object_id] = OBJECT_ID(N'dbo.SensorValues', N'U')
    AND p.index_id = 1
ORDER BY
    p.[partition_number] DESC, 
    ca.Date DESC;

...produces an execution plan like this:

Original Plan

This execution plan has an estimated total cost of 0.02 units. Over 50% of this estimated cost is the final Sort, running in Top-N mode. Now estimates are just that, but sorts can be expensive in general, so let's remove it without changing the semantics:

SELECT TOP (1)
    ca.SensorId,
    ca.SensorValue,
    ca.Date
FROM
(
    -- Partition numbers
    SELECT DISTINCT
        partition_number = prv.boundary_id
    FROM
        sys.partition_functions AS pf
    JOIN sys.partition_range_values AS prv ON
        prv.function_id = pf.function_id
    WHERE
        pf.name = N'SensorValues_Date_PF'
        AND prv.boundary_id <= $PARTITION.SensorValues_Date_PF(@fDate)
) AS p
CROSS APPLY
    (
    SELECT TOP (1)
        v.Date,
        v.SensorValue,
        v.SensorId
    FROM dbo.SensorValues AS v
    WHERE
        $PARTITION.SensorValues_Date_PF(v.Date) = p.partition_number
        AND v.DeviceId = @fDeviceId
        AND v.SensorId = @fSensorId
        AND v.Date <= @fDate
    ORDER BY
        v.Date DESC
  ) AS ca
ORDER BY
    p.partition_number DESC,
    ca.Date DESC

Now the execution plan has no blocking operators, and no sorts in particular. The estimated cost of the new query plan below is 0.01 units and the total cost is distributed evenly over the data access methods:

Improved Query Plan

With the improvement in place, all we need to produce a result for each Sensor ID is to make a list of Sensor IDs and APPLY the previous code to each one:

SELECT
    PerSensor.SensorId,
    PerSensor.SensorValue,
    PerSensor.Date
FROM 
(
    -- Sensor ID list
    VALUES 
        (@fSensorId1),
        (@FSensorId2),
        (@FSensorId3)
) AS Sensor (Id)
CROSS APPLY
(
    -- Optimized code applied to each sensor
    SELECT TOP (1)
        ca.SensorId,
        ca.SensorValue,
        ca.Date
    FROM
    (
        -- Partition numbers
        SELECT DISTINCT
            partition_number = prv.boundary_id
        FROM
            sys.partition_functions AS pf
        JOIN sys.partition_range_values AS prv ON
            prv.function_id = pf.function_id
        WHERE
            pf.name = N'SensorValues_Date_PF'
            AND prv.boundary_id <= $PARTITION.SensorValues_Date_PF(@fDate)
    ) AS p
    CROSS APPLY
        (
        SELECT TOP (1)
            v.Date,
            v.SensorValue,
            v.SensorId
        FROM dbo.SensorValues AS v
        WHERE
            $PARTITION.SensorValues_Date_PF(v.Date) = p.partition_number
            AND v.DeviceId = @fDeviceId
            AND v.SensorId = Sensor.Id--@fSensorId1
            AND v.Date <= @fDate
        ORDER BY
            v.Date DESC
      ) AS ca
    ORDER BY
        p.partition_number DESC,
        ca.Date DESC
) AS PerSensor;

The query plan is:

Final Query Plan

Estimated query plan cost for three Sensor IDs is 0.011 - half that of the original single-sensor plan.

share|improve this answer
1  
You've got skills. Have cut the loading times of some pages by more than 50% thanks to your solution. Thank you very much! (answer upvoted and accepted) –  m__ Nov 29 '12 at 7:59
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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