Optimize BETWEEN Query in SQL Server Azure

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.

Method 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://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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply