In last week's Query Exercise, I challenged you to play some code golf to generate big spills with tiny T-SQL.
Today, I'm going to walk you through my thought process - the initial attempts I tried and failed with, and the discoveries I made along the way, because I think it makes for fun storytelling.
In your most recent Query Exercise challenge, I gave you these two tables:
[crayon-6a3b9053f3590793297006/]
For this week's Query Exercise, your mission had two parts:
Do a first pass search through the Users table looking for columns where people had snuck in email addresses where they didn't belong
Write a more high-quality query to identify exactly which rows we needed to handle
This week's Query Exercise challenged you to figure out why these 3 estimates went so badly:
[crayon-6a3b9053f3bc9252971420/]
This Query Exercise was very different: I didn't ask you to solve a particular problem. I pointed out that I've heard advice that SELECT MAX is faster than SELECT TOP 1, and that's not quite true. I asked you to find factors that would cause these two queries to get different execution plans: [crayon-6a3b9053f3e66786510063/] In the…
In last week's Query Exercise, we added a user-defined function to the Users table to check whether their WebsiteUrl was valid or not. I noted that even with an index on Reputation, SQL Server 2022 simply ignored the index, did a table scan, and spent 2 minutes of time calling the user-defined function on a row-by-row basis.
For this week's Query Exercise, I asked you to write a better query than ChatGPT wrote. Your goal was to find the best days and times to post questions on Stack Overflow.
I found it interesting that a lot of the initial answers focused on the times when there were the most questions, or which questions were the most highly upvoted. For me, the best time to post a question is when you have the highest likelihood of getting the right answer, quickly.
In last week's post, I gave you a trigger that populated a history table with all changes to the Users.AboutMe column. It was your job to write a T-SQL query that turned these Users_Changes audit table rows:
Into the full before & after data for each change, like the earlier query from the blog post:
Your challenge for this week was to find out who keeps mangling the contents of the AboutMe column in the Stack Overflow database.
Conceptually, there are a lot of ways we can track when data changes: Change Tracking, Change Data Capture, temporal tables, auditing, and I'm sure I'm missing more. But for me, there are a couple of key concerns when we need to track specific changes in a high-throughput environment:
This week's query exercise asked you to find two kinds of locations in the Stack Overflow database:
Locations populated with users who seem to be really helpful, meaning, they write really good answers
Locations where people seem to need the most help, meaning, they ask a lot of questions, but they do not seem to be answering those of their neighbors
Your query exercise for this week was to write a query to find users created in the last 90 days, with a reputation higher than 50 points, from highest reputation to lowest. Because everyone's Stack Overflow database might be slightly different, we had to start by finding the "end date" for our query. I'm working with the 2018-06 export that I use in my training classes, so here's my end date:
In last week's Query Exercise, our developers had a query that wasn't going as fast as they'd like:
[crayon-6a3b905400fb8402658613/]
The query had an index, but SQL Server was refusing to use the index - even though the query would do way less logical reads if it used the index. You had 3 questions to answer:
Your challenge for last week was to take this Stack Overflow database query to show the top-ranking users in the most popular location:
[crayon-6a3b9054012a1066580038/]
And make it read less pages only by tuning the query? You weren't allowed to make index or server changes, and you weren't allowed to hard code the location in the query since it might change over time.
The Core of the Problem
The main problem is that when we run a statement (like SELECT), SQL Server:
In the last Query Exercise discussion, we hit a performance wall when we were trying to quickly find questions with a specific tag. We threw up our hands and said it's time to make database changes, but we had a few restrictions: We have a lot of existing code that uses the Posts.Tags column That…
Your query exercise was to take this Stack Overflow query to find the top-voted questions for any given tag:
[crayon-6a3b905401a7f117612355/]
That's currently using this index in its execution plan:
[crayon-6a3b905401a84944859269/]
And answer 3 questions:
What kinds of tags will perform worse than others for this query?
Could you change the query to perform better?
Could you change the indexes to perform better, without changing the table structure?
Your Query Exercise was to find the best time to do database maintenance by querying the Users table, looking for a one-hour window with the lowest number of created users. We kept this one pretty simple by looking at the data in just one table, and we didn't hassle with time zones. We just wanted the 3 lowest-load hours, in this format:
Your Query Exercise was to find denormalization accuracy problems: checking the accuracy of a reporting column, Posts.CommentCount. There were two parts: finding the top 100 most problematic Posts with the biggest variances, and thinking about a long term solution to keep the CommentCount accuracy as high as practical. Question 1: Finding the Problematic Posts Your…
Your Query Exercise was to find foreign key problems: data that might stop us from successfully implementing foreign keys in the Stack Overflow database.
Question 1: Write queries to find the problematic data.
