Split date range without Calendar table

Hi johnqflorida,

thank you so much for taking time to answer.

• Yes, we run this repeatedly, but users are defining from - to period as well as interval (10, 15 30 minutes). That's why we decided to create temporary table for this for each query execution.


• In the meantime, we modified the query to query from calendar and then fact table, with very small performance improvements.


• As I mentioned in previous post, data we are querying from Fact is not so small, in my example for 1 week period divided into 15 minute intervals, there will be 6.5 million records and we have a query that uses 12 week interval


• I agree that defining boundaries properly will save some time, but this query we are currently using is just for performance testing and at this point it really doesn't matter if we are counting some activities twice.

The reason I started this topic is because we tested 2 approaches for 12 week / 15 minutes interval: one with pure T-SQL and other with CLR which calls individual periods separately (there is no need for join with calendar table) in multiple threads. Pure SQL took 30m17s to complete, while CLR took 1m28s! Same indexes, same data, no other executions on the server. Of course, CLR multithead approach is taking 80% of processor's time, but query speed is a priority here.

Finally, I am very sorry if I offended someone with my answers. I really appreciate every answer I get, because it helps me a lot. A lot of times I get answer for the problem which I already tried, but I still appreciate it because it shows me that I'm on the right track of thinking how to solve a problem. Anyway, thank you for pointing me that my answers may seem offending to someone; I will definitely try to change this in the future.

Cheers,