All we need is an easy explanation of the problem, so here it is.
I have a table that stores time-series (5-minute) data and am trying to run a query that has a BETWEEN clause included in it. Below is the table structure:-
Timestamp | ComponenentID | Parameter1 | Parameter2 | Parameter3
The table has an index on ComponentId and also a Clustered Columnstore index (Azure S3 and above get this feature).
The query I am trying to run:-
SELECT * FROM table WHERE Timestamp BETWEEN '2020-01-01' AND '2020-01-02'
I am looping through multiple similar tables to fetch data and sometimes it takes about 30 seconds to get one day worth of data. Is there anything I can do to reduce this time?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
To make this query run fast you will need the data ordered by timestamp.
You have a few options
If you use a clustered columnstore index you need to FIRST order the data in the table by adding a clustered index on Timestamp and then create the columnstore index using maxdop = 1 and with drop_existing.
You could get rid of the columnstore index and just use a clustered index on Timestamp.
You could use a nonclustered index on Timestamp and include all the other indexes in the table (might be OK if you only have 5 columns depending on the datatypes).
You could partition the table by timestamp – this is another longer conversation
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂