Optimize BETWEEN Query in SQL Server Azure 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?
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.
full syntax https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/performance-tuning-ordered-cci
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