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:
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:
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:
Estimated query plan cost for three Sensor IDs is 0.011 - half that of the original single-sensor plan.
SELECT
SUM(IF(user_type='I',1,0)) Individual_Count,
SUM(IF(user_type='G',1,0)) Group_Count,
DATE_FORMAT(dt,'%M') "Month"
FROM
(
SELECT user_type,
(MAKEDATE(YEAR(join_date),1) + INTERVAL (MONTH(join_date)-1) MONTH) dt
FROM consultant WHERE join_date >= '2014-07-01 00:00:00'
) A GROUP BY dt;
or
SELECT
SUM(user_type='I') Individual_Count,
SUM(user_type='G') Group_Count,
DATE_FORMAT(dt,'%M') "Month"
FROM
(
SELECT user_type,
(MAKEDATE(YEAR(join_date),1) + INTERVAL (MONTH(join_date)-1) MONTH) dt
FROM consultant WHERE join_date >= '2014-07-01 00:00:00'
) A GROUP BY dt;
If you would like the counts summed up, include WITH ROLLUP
SELECT
SUM(IF(user_type='I',1,0)) Individual_Count,
SUM(IF(user_type='G',1,0)) Group_Count,
IFNULL(DATE_FORMAT(dt,'%M'),'Total') "Month"
FROM
(
SELECT user_type,
(MAKEDATE(YEAR(join_date),1) + INTERVAL (MONTH(join_date)-1) MONTH) dt
FROM consultant WHERE join_date >= '2014-07-01 00:00:00'
) A GROUP BY dt WITH ROLLUP;
or
SELECT
SUM(user_type='I') Individual_Count,
SUM(user_type='G') Group_Count,
IFNULL(DATE_FORMAT(dt,'%M'),'Total') "Month"
FROM
(
SELECT user_type,
(MAKEDATE(YEAR(join_date),1) + INTERVAL (MONTH(join_date)-1) MONTH) dt
FROM consultant WHERE join_date >= '2014-07-01 00:00:00'
) A GROUP BY dt WITH ROLLUP;
Best Answer
For a table called
cristian
, here is the needed query:Here is your sample data:
and here is the query's output
These would also work
Give it a Try !!!