Optimize BETWEEN Query in SQL Server Azure

Question

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?

Answer 1

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://learn.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