5

I'm having serious performance problems with a particular SQL row-to-column translation. A single query takes up to a minute to pull up 6 hours of data. I've simplified the tables below for clarity, but there's well over a million datapoints per day so the table size might be contributing. I don't have much experience with complex queries like this so I'd like some advice. The data tables are as follows...

(DataPoint table)

id     datetime
_____________________
1      2015-09-08 21:00:00
2      2015-09-08 21:00:01
3      2015-09-08 21:00:02
4      2015-09-08 21:00:03
5      2015-09-08 21:00:04

(SensorData table)

id     datapointId     SensorId     Temp    DateTime
_____________________________________________________
1      1               20           34.6    2015-09-08 21:00:00.345
2      1               21           34.2    2015-09-08 21:00:00.551
3      1               22           34.1    2015-09-08 21:00:00.101
4      1               41           34.3    2015-09-08 21:00:00.700
5      2               20           34.2    2015-09-08 21:00:01.223
6      2               21           34.4    2015-09-08 21:00:01.456
7      2               22           34.5    2015-09-08 21:00:01.100
8      2               41           34.6    2015-09-08 21:00:01.870

Note that that individual sensor DateTime and the DataPoint DateTime are not equal (but they are close). This is why I went with a second table to give me the ability to do a rough side-by-side comparison without having to do some fuzzy dateTime logic.

I need to query this information with a resulting dataset that looks like this...

DateTime              Temp1    Temp2    Temp3
__________________________________________________
2015-09-08 21:00:00   34.5       34.1       41.1
2015-09-08 21:00:01   34.4       34.2       41.2
2015-09-08 21:00:02   34.4       34.2       41.2

My current SQL query is written (dynamically) as such...

SELECT DataPoint.DateTime,
max(case when SensorData.SensorId = 20 then SensorData.Temp end) Temp1,
max(case when SensorData.SensorId = 21 then SensorData.Temp end) Temp2,
max(case when SensorData.SensorId = 22 then SensorData.Temp end) Temp3,
FROM DataPoint LEFT JOIN SensorData ON DataPoint.Id=dbo.SensorData.DataPointId
WHERE DataPoint.DateTime BETWEEN [x] and [y]
GROUP BY DataPoint.DateTime ORDER BY DataPoint.DateTime

So my question has three parts:

1) Why is this particular query so slow?

2) Is there a better method for storing this information that I'm missing? I'm still in the design phase at this point. I chose this schema because I need to be able to compare timeseries information for sensors that fire off their data at irregular intervals.

3) Is there a faster way to query and translate this data to my desired format?

EDIT!!! Sorry, there is a GroupBy clause at the end of my query that I forgot to add. My bad.

dweeb_plus_plus
  • 83
  • 1
  • 7
  • 1
    how does the query even work without `group by`? – Vamsi Prabhala Nov 04 '15 at 17:54
  • Could you provide info on your indexes to the columns being queries/filtered? – Paul Sasik Nov 04 '15 at 17:56
  • @vkp It works because it's not an aggregate query, it's a pivot query. The tables, though being relational are being treated like a key/value pair model of databases which require pivoting, which is possible in SQL, to get into a standard columnar format. – Paul Sasik Nov 04 '15 at 18:03
  • 1
    at least show us the execution plan http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan – Juan Carlos Oropeza Nov 04 '15 at 18:11

2 Answers2

3

  • First you need make sure have index for DataPointId, if that is a PK probably have index, but if is a FK you will have to manually add it.

  • Second you need index for SensorId and DateTime

  • Third filter the SensorID before the query right now you are processing all the million record to generate the report

.

WHERE SensorID IN (20,21,22)

Also you can try PIVOT function

SqlFiddleDemo

SELECT [DateTime], [20] as Temp1, [21] as Temp2, [22] as Temp3
FROM
(SELECT [DateTime], SensorId, [Temp] 
 FROM sensor) AS SourceTable
PIVOT
(
MAX([Temp])
    FOR SensorId IN ([20], [21], [22])
) AS PivotTable;
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
-1

I would strongly recommend to use a " GROUP BY " clause for this. Also, join the tables, starting at sensorData.

something like this:

SELECT DP.DateTime, SD.SensorID, MAX(SD.Temp) as MaxTemp
FROM SensorData SD
LEFT JOIN DataPoint DP ON DP.ID = SD.DataPointID
WHERE DP.DateTime BETWEEN [x] and [x]
GROUP BY DP.DateTime, SD.SensorID

You'll get data in the form of: Date, SensorID, MaxTemp If you then need to visualize it transposed, I would recommend (if possible) to use a pivot table (Excel). (if Excel is not an option; let me know; there are other possibilities still).

AlexT82
  • 1,114
  • 1
  • 7
  • 12