0

I have an SQL Server Azure Database and am trying to store time-series data that I get from multiple sensors.

Data Source:- 5-minute data is obtained via an API.

Current table structure: -

 Timestamp | ComponentId | Parameter1 | Parameter2 | Parameter3

Each sensor has a unique ComponentId. I have a non-clustered index on Timestamp and ComponentId to eliminate duplicates and also a clustered column store index on the whole table (It compresses data and saves space. Also gives a performance boost for aggregate queries). A python script is used to fetch the data via the API and pyodbc library is used to push this data to the table. The script runs every 10 minutes and inserts data into the table.

Some queries like fetching data for a particular component for just one day seem to take 5 seconds. Is this normal?

QUERY:-

SELECT Timestamp,ComponentId,ParameterId FROM TABLE WHERE ComponentId=1 AND Timestamp BETWEEN '2021-05-01' AND '2021-05-02'

Execution Plan

IO Stats: Table 'RawData'. Scan count 2, logical reads 10164, physical reads 3, page server reads 0, read-ahead reads 10146, page server read-ahead reads 0, lob logical reads 2766, lob physical reads 12, lob page server reads 0, lob read-ahead reads 4877, lob page server read-ahead reads 0. Table 'RawData'. Segment reads 2, segment skipped 10.

Is this way of fetching/pushing data fine? Please let me know if it can be improved and if there are any better methods to do the same.

Improve this question
2
  • About 48k rows per day. Posted the plan and IO stats above in the question.
    – Spaceguy152
    Commented May 3, 2021 at 19:32
  • All parameters are float.
    – Spaceguy152
    Commented May 4, 2021 at 4:47

2 Answers 2

Reset to default
2

With that design it is expected that the query requires a full scan of the columnstore. That should be very fast at that scale, but the IO stats show that you're reading from disk. If you look at the wait stats for the actual execution plan you should see that it's more IO waits than CPU utilization.

You could scale up the database to get more cache memory, or perhaps partition the columnstore either by componentId or timestamp. Whenever you partition a columnstore you want to ensure that partitions have at least 1M rows.

Improve this answer
2
  • I was reading about partitioning. Since this is a live table the partitioning function/scheme would have to be changed every time the limit is reached right? Say I use Timestamp to partition and perform a yearly partition, then once the current year is done the partition function/scheme must be again right? Or is there another way to do this?
    – Spaceguy152
    Commented May 3, 2021 at 19:43
  • You can create partitions for the future up-front.
    – David Browne - Microsoft
    Commented May 3, 2021 at 19:53
2

I think the Clustered Columnstore is probably overkill for your situation and working against you.

Given the amount of data and how you're trying to query, you should just do a regular clustered index (primary key (ComponentId, Timestamp) and enable page compression if you're trying to save space/reduce disk i/o.

The thing to remember about columnstore is that it will physically decompose your data, often in a way that is unsuitable for time series analysis/queries. For wide, "denormalized" data it's a great tool, but for what you're trying to do the overhead imposed by searching the entire columnstore to reassemble the few rows you need isn't worth any potential benefit you might get for some aggregate queries.

Improve this answer

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.