Problem Description / Requirement
This example comes from a customer from the automtive sector. Whenever a vehicle is built, EVERY single step must be precisely recorded. So-called “telegrams” are used for this purpose. These telegrams can convey simple information (e.g., entry into an assembly station) or complex information. The crucial thing is that EVERY piece of information must be stored in a traceable manner!
Each process step in a vehicle”s production is logged in a table as “telegrams.”
– The type of telegram is identified by a flag.
– Each telegram has a start and end date.
In the event of a malfunction, you want to know which processes were active at that exact time (timeline).
– These can be very short processes (login, tool change, etc.).
– These can be very long processes (painting, wedding, etc.).
The customer complains about very long response times. Especially if a timeline spans several days (which is possible), the query sometimes takes several minutes.
Problem
The problem with executing the query is the unpredictable amount of data that can be returned depending on the time specification. For example, if a query is started with a time range that is very close to the end or beginning of the table, the query runs very well. However, the closer you get to the middle stored date value, the longer the query takes.
Quick running query
SELECT id, [t_status], begin_ts, end_ts FROM dbo.telegram WHERE begin_ts <= '2025-06-11 14:00' AND end_ts >= '2025-06-11 13:00'; GO
Long running query
SELECT id, [t_status], begin_ts, end_ts FROM dbo.telegram WHERE begin_ts <= '2025-05-26 14:00' AND end_ts >= '2025-05-26 13:00'; GO
Note
When you run both queries without optimization you will see the same IO and CPU statistics. When you try the same with an appropriate index the first query should run faster than the second one.
The customer wants a performance for the query independent from the date and time but only accepts longer runtime when more records are found!
Challenge
- The table [dbo].[telegram] has only a clustered Primary Key on [internal_id], no other indexes are set
- Use ANY technique you know to optimize the queries.
- The customer wants a predictable runtime for the query.
- The runtime must be independent of the time period in which a process is queried.
Condition
- none. You can do all to improve the query (adding indexes, tables, partitions, …)
Technical Information
The creation of the challenge can take up to 5 Minutes and creates the following objects / data
- Table [dbo].[telegram]
- 40 Mio rows
How to start the challenge
When you are connected to the SQL Server instance, run the following commands:
EXECUTE sqlchallenge.dbo.create_challenge 'list'; |
will give you a list of open challenges |
EXECUTE sqlchallenge.dbo.create_challenge 'SQL Challenge #8 - querying time ranges'; |
will create the challenge for you |
customer expectations
The runtime may not vary due to a different time window; however, it may vary due to the amount of data.
Solution
This challenge is certainly one of the most difficult. Whoever finds this solution can call themselves an “experienced DBA.”
Connect to Microsoft SQL Server
| SQL Server: | sqlchallenges.germanywestcentral.cloudapp.azure.com |
| Port: | 1433 |
| Version: |
16.0.4165.4 |
| Edition: | Developer Edition |
| Authentication Type: | SQL Server Authentication |
| Login Name: | sqlchallenge |
| Password: | sqlchallenge#2025! |
| Encryption | Mandatory |
| Trust Server Certificate: | True |
Tried a lot, ended up with a columnstore index because it’s a bit of analytics for timeseries. Furthermore, the amount of telegrams in May is massively more than in June. I don’t have experience with columnstore indexes and don’t know the dependencies in a production environment. For my understanding, it’s more for DWH and analytics. Fast Query 40ms, Slow Query 1300ms.
Dear Gabriel,
god idea and if it works it’s fine. There is a “better” approach when you think of the problem with the two columns begin_ts and end_ts which cannot be covered fully in one index.
Think about a solution where you can access the time range with ONE column – maybe that’s the trick here.
Good luck and THANK YOU for accepting the callenge.
Working on a solution. But its not easy. Having problems running out of log-space when adding + updating columns.
Hope i have a solution later today. Google seems to be my friend on this one.
Would like to add that log-space is not the problem. Sorry for unclear post
Hi Uwe,
Many thanks for designing new challenge!
This is interesting one, few tricks I had in mind did not deliver.
At the end, good old KISS worked just fine:
short running query:
(604 rows affected)
Table ‘telegram’. Scan count 1, logical reads 8,…
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 2 ms.
long running query:
(135946 rows affected)
Table ‘telegram’. Scan count 1, logical reads 49044
SQL Server Execution Times:
CPU time = 1219 ms, elapsed time = 1251 ms.
Index seek for both queries.
Not posting script yet, I am still looking at NOTE section (I have the feeling something is lurking there 🙂
I have spend quite some time on this one. Really interesting.
Google helped me to get here:
https://lucient.com/en/blog/interval-queries-in-sql-server-part-1/
https://lucient.com/blog/a-static-relational-interval-tree/
https://web.archive.org/web/20220526022251/https://blogs.solidq.com/en/businessanalytics/using-static-relational-interval-tree-time-intervals/
I have:
Added lower_int, upper_int and node (persisted) to telegram – my time starts at 2020-01-01 and the grain is minutes. Its not fast so patience is virtue.
Created a function to calculate the node
Indexed the node column with a covering index for the output query.
Created a bitmaptable with appropriate depth.
I have created the helper functions to get the different parts of the tree…… So far so good
Then populated a new table with all intervals and corresponding overlapping nodes.
On the positive note: Its seems to be blazingly fast on searching
Bad news: I’m clearly off by a bit in the node maths – some windows return a few rows too many, others a few too few.
Its been really fun – but i’m getting in too deep here – trying to implement bit-logic i’m not on top of. So for my part it would not get to production – even if i could make it work
So. thanks for a great challenge – so much fun playing around with it.
Hope its ok to submit this with no code – there is too much to fit in this box.
Dear Peter,
thank you so much for your valued comment. I came to my solution by reading the great article from Itzik Ben Gan but our tests with this feature didn’t scale good when writing data.
So our approach was a new table with the intervalls (15 min) in one table. You can read the details of my solution in link [Solution] in the challenge.
Once again thank you for taking the challenge. I love your idea and will give it a try when I find the time for it.
All the best, Uwe
I didn’t manage to find the original article from Itzik Ben Gan – i only managed to dig up broken links.
Looking forward to reading your solution later this week.
Hi Uwe,
How big is the real table?
The one at customer’s database?
I have a feeling it is bit larger than the one in the challenge.
Thank you.
Hey Art,
it’s nice to read from you. Your assumption is correct – the customers table contains app. 1.2 Billion rows and the table is much wider than this example which only contains one simple attribute which is not necessary [t_status].
Thank you very much for participating to this challenge…