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?
SELECT Timestamp,ComponentId,ParameterId FROM TABLE WHERE ComponentId=1 AND Timestamp BETWEEN '2021-05-01' AND '2021-05-02'
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.