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?

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

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

  • 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? – Spaceguy152 Apr 12 at 7:58
  • this technique should work in all flavours of MS SQL – Stephen Morris - Mo64 Apr 12 at 8:00
  • 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/… – Tibor Karaszi Apr 12 at 12:11
  • 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). – Stephen Morris - Mo64 Apr 12 at 12:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.