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?

• Charlieface: By the way, if Timestamp includes a time component then you probably want >= AND < rather than BETWEEN
• Artashes Khachatryan: What about daily partition?
• Spaceguy152: This was another option but I think it's quite complicated to implement in regular SQL Server.

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

• Spaceguy152: Thanks for this. I was wondering if it's possible to order the Columnstore indexes in Azure SQL Server or is this possible only in Azure data warehouse?
• Stephen Morris - Mo64: this technique should work in all flavours of MS SQL
• Tibor Karaszi: There's no ORDER clause when you create a columnstore index for the regular SQL Server. So you would have to make sure that SQL Server "happens" to read the data in the order that would match the segment elimination. I believe that Hugo wrote about it here: sqlservercentral.com/steps/…
• Stephen Morris - Mo64: Hi Tibor what I meant was - If you order the data with a regular clustered index and then build the columnstore index with the drop_existing clause you can get ordering in your columnstore - proviso you need maxdop 1. One of Nico's blogs explains it nikoport.com/columnstore (but I didn't have time to search through all 131 entries).