Blog

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:

• Mondays, 1:00-1:59
• Saturdays, 23:00-23:59
• Sundays, 2:00-2:59

The comments on this one were wild! Lots of different approaches, but they all boiled down to two basic styles.

The easy (but not necessarily right) way:
group by date_part

One way to do this is to use the built-in DATEPART function to group users by weekday and hour of their creation dates, like this:

And at first glance, that looks like it works:

For the database I’m using (the 2018-06 Stack Overflow database) and the date ranges I picked, it looks like late Sunday night, early Monday morning would be the winners here, so my answer might be:

• Monday, 02:00-02:59
• Monday, 00:00-00:59
• Sunday, 00:00-00:59

If that was your solution, you’d be in fine company: ChatGPT 4 suggested a similar solution. It even added a delightful caveat:

Note that DATEPART(dw, CreationDate) returns an integer representing the day of the week, where the specific value depends on the SET DATEFIRST setting of your SQL Server instance. Typically, 1 represents Sunday, but this can vary. Similarly, DATEPART(hour, CreationDate) returns the hour of the day based on a 24-hour clock.

That’s one of the things I love about using ChatGPT for coding tasks like that – it can add valuable context that saves me time from checking the documentation.

That grouping approach has a bug, though.

That technique only works as long as our historical data is populated for every hour of every day. What if we’ve been taking the web servers down every Sunday at 16:00-17:00? There wouldn’t be any users created in that date range, and thus, no rows would show up in the result set for that query!

To illustrate it, let’s query the table looking for days when no users were created during particular hours:

The results in the 2018-06 database show that we’ve had several days where we only created users in some (but not all) hours of the day:

And if that was a repeating trend in the data, we’d have a problem.

The Safer Technique:
generate a list of days/times first.

In pseudocode:

That way, if we have any weekday/hour combinations with no users created, we’ll still have rows for them in our result set. Starting with compatibility level 160 (SQL Server 2022), one option for this is the generate_series function. In older versions, you’d probably use a numbers table, aka tally table.

We need two sets of rows:

• weekdays: a set of rows 1 through 7
• hours: a set of rows 0 through 23

So we’ll call generate_series twice:

To get a list of days and hours:

Then, let’s take our first version of this query, use that as a CTE, and left outer join to it:

That way, the GENERATE_SERIES calls will make sure rows exist in our result set – even if no users were created in that date range.

Hope you enjoyed it! For more challenges like this, check out the Query Exercises page.

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 first attempt might have looked something like this:

But there are 2 problems with that. First, because it’s an inner join, it only finds Posts with Comments. What if the CommentCount is off because there are no matching rows in the Comments table at all? We’re gonna need a left outer join instead.

Second, that approach focuses on variances where p.CommentCount is HIGHER than the actual number of comments – but not the other way around. In this case, those variances are fairly small, only off by a couple comments:

Your next approach might have been two different queries, unioned together, with one sorting for higher p.CommentCounts, and the other sorting for higher actual comments. However, given the size of these tables, that’d be a performance nightmare, making multiple passes through the same tables.

The slick way to do it is to sort by the absolute value (ABS) of the variance. That way, if I have a list with both large positive AND negative values, those large values will hang out together in the result list.

Our new query looks a lot more complicated due to needing to handle situations with no rows in the Comments table (thus the COALESCE) and the ABS to sort large values together:

Our result set looks quite different now!

Now we’ve found issues where there are no comments at all (which the inner join missed), and variances in both directions. (I especially love the symmetry of rows 10 & 11 in this screenshot to illustrate that problem.)

Alright, part 1 done. We’ve identified that we do indeed have a variety of data problems here. In the comments on the challenge blog post, there was a lot of discussion about creating indexes. I’d say that for Question 1, seeing the scope of the problem, you shouldn’t put in indexes just to solve that problem. It’s a one-time thing, checking to see if we have something to fix or not.

Now, what should we do long term?

Question 2: Solving It Long Term

Your homework assignment specifically skipped writing a one-time update statement. I asked you to focus on how we should keep that Posts.CommentCount column as up to date as practical, reminding you that it wasn’t a transactional system like bank balances.

When we wanna keep data up to date between two tables, we have 3 basic options:

• Synchronously, done by the database server itself – which would make insert transactions longer when people add new comments, plus add workload to the database server to coordinate that transaction. This would typically be accomplished with a trigger on the Comments table so that as soon as rows were deleted, updated, or inserted, the Posts.CommentCount could be updated at the same time, and always in sync. ChatGPT 4 generated me a trigger to do this. (At first glance, I don’t think it handles multi-row deletes or updates correctly, but I didn’t test that – just found it amusing. Whenever I have a coding task to do these days, I tend to ask ChatGPT to give it a shot, just to see how it does.)
• Asynchronously, done by the database server – add a trigger to the comments table, but rather than updating Posts.CommentCount right away, simply add a row to a queue table indicating that the post.id needs its comment count updated. Later, circle back with an async job to run the commentcount updates only for the posts listed in the queue table.
• Asynchronously, done by the app – instead of a trigger, have the front end app (or service layer) add a record to something whenever it inserts a comment. That something doesn’t even have to be a relational database – it could be a queue service or a key/value store. Then, have a service check that queue later and run update queries only for the specific posts listed in the queue.

The advantage of the async approach is that we only have to update a posts row once every X minutes, no matter how many comments have been added to it. If comments come in fast & furious in the first, say, hour of a post’s existence, then we can reduce that workload with a queue.

In fact, if you check out the above screenshot with variances, you’ll notice that the largest variances all seem to have a similar range of ids, and they’re all in the high range – some of the more recently inserted posts, basically. That would seem to suggest an asynchronous process.

Your long term solution might have been just that! You might have recommended a periodic job, say hourly, to update the posts.commentcount column. Depending on a few things, that might be fine:

• If we’re willing to let the data be out of sync on newly created posts
• If we don’t have too many posts columns to update (you saw how slow these queries were – imagine how bad it’d be if we had a dozen similar reporting columns to keep in sync)
• If the regular process doesn’t cause blocking problems when it runs for a long time
• If we have enough database server horsepower to get ‘er done without too much impact to end users

On the other hand, you could also choose the synchronous approach: every time we insert a new comment row, update the posts.commentcount row for the post we’re commenting on. This could either be done with a trigger on comments, or with an added update statement on whatever app code is inserting the comments. A big advantage of the sync approach is that the reporting data is always up to date. Business users like that.

For the sync approach, there are two ways to update the Posts.CommentCount number:

• Either count the exact number of Comments in the Comments table at the time of the data change. This is easier to code because it’s just a quick sum on the Comments table, but it does involve actually querying the Comments table, which introduces more work and more locking.
• Or simply increment the Posts.CommentCount number +1 or -1, depending on the data change. That one’s faster because there’s less locking involved on the Comments table, but it can be trickier to get the logic right.

In a perfect world, I’m a fan of keeping the database simple for scalability purposes: start with the synchronous approach, done in app code. When you hit a performance wall where that stops scaling, and if this particular process is the bottleneck holding you back from going faster, only then do something more complicated like an async batch job, after making sure the business users are okay with the data being out of sync.

If you choose a synchronous solution, you’ll probably need indexes to support the queries to make them fast enough. If you choose an async solution, especially if you offload it to a readable replica, then it’s easier to get away without adding more indexes when the database is already over-indexed.

Something Fun to Think About

For those of you following along with the Postgres version of this homework, you might have noticed that in the original homework assignment posts, when I queried the top 100 posts just to show demo data, I got very different results.

Here’s SQL Server’s top 100:

Note that in this case, it’s sorted by Id. Typically, in SQL Server, you’ll see the first 100 rows sorted by their clustering key. That’s certainly not always the case, and in beginning performance tuning sessions, I have to emphasize to SQL Server people, “Seriously, order isn’t guaranteed unless you ask for it.”

Here’s Postgres’s top 100:

Postgres really doesn’t give you an order by unless you ask for it, and if you haven’t used Postgres before, the reason will rock your world.

How’d you do? What’d you think?

I hope you had fun, enjoyed this exercise, and learned a little something. If you’ve got comments or questions, hit the comment section below. (Keep the comments focused on this exercise, though – we’re not covering the details of the Postgres export just yet. Only so many things I can cover per blog post, hahaha, and this thing’s already way long.)

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.

Montro1981’s thorough answer broke the challenge into two parts: first, check to see if there is ANY bad data, and if so, check to see how pervasive it is. What I really like about this query below is that it recognizes that:

• Each table, and each column, might have different foreign key problems
• Some of those problems might be big, and some might be small
• Some might have only happened a long time ago, and some might still be ongoing

Heres what the results look like for the 2018-06 training version of the Stack Overflow database, and of course they’ll be different depending on which version you use:

We can see that the first two issues (Posts & Comments with invalid UserIds) is pretty rampant, whereas comments without valid PostIds is much less frequent. However, all 3 of the foreign key candidates are still having problems right up til the end of the data set (2018-06), which means whatever’s causing this “bad” data is still happening.

Question 2: What might have caused the data problems?

Let’s examine the posts rows that don’t have matching users rows. You can click on this to zoom in if you like:

We’re trying to join over to the users table using the owneruserid column, but the OwnerUserId is 0 for these rows, and apparently there’s no Users.Id 0. That 0 sounds like it might be a hard-coded magic value.

What are some reasons that a post might not have a valid owneruserid?

• Perhaps users are allowed to delete their accounts, but Stack Overflow has chosen to keep their questions & answers around for posterity.
• Perhaps there were bugs in transaction handling code. Perhaps there was a single stored procedure that was supposed to add a user and add their first post, but something went wrong, and only the post got inserted.
• Perhaps there was database corruption.
• Perhaps someone ran a delete on the users table with an incorrect where clause. (C’mon, we’ve all been there.)
• Or multiple of the above, especially for an app that’s been around for 15+ years.

I’ve seen all of these in various systems over time, but given what I know from Stack (I used to work with ’em), the first option is the likely cause for most (but not all) of the problematic rows we’re seeing here.

Pull up the site to see how it’s handling the data problem.

Normally, when you’re looking at a question or answer on StackOverflow.com, you see the person who wrote it. Let’s take a look at what the web site shows for this post.

One of the things I love about StackOverflow.com is their URL handling. If you know a row’s id, you can pull it up on the site. We’re in the posts table, which is where questions & answers are stored, and let’s say we wanna see id 34, the one about “How to unload a ByteArray”. To see posts.id = 34, put this into your browser’s address bar:

https://stackoverflow.com/questions/34/

Click that, and Stack magically redirects you to:

https://stackoverflow.com/questions/34/how-to-unload-a-bytearray-using-actionscript-3

Isn’t that cool? It works for both questions & answers, too. For example, to see posts.id 18:

https://stackoverflow.com/questions/18/

Which redirects you to:

https://stackoverflow.com/questions/17/binary-data-in-mysql/18#18

Because posts.id 18 is actually an answer for question 17. More about that join structure some other day – for now, let’s go back to the problem at hand, and we’ll look at posts.id 34 about unloading a byte array.

At the bottom right, Stack shows who asked the question. It says “asked Aug 1, 2008 at 12:30, maclema.”

Wait – how does Stack know the user’s name?

How are they getting the name ‘maclema’ if there’s no working join over to the users table? In the SQL Server versions of the Stack Overflow database I’ve provided in the past, there’s no OwnerDisplayName column, but there’s actually one in the current XML data dumps. To see it, let’s look at another version of the database that includes the OwnerDisplayName in the Posts table:

And look over at the far right column:

Ah-ha! It looks like Stack de-normalized the data a little. Perhaps, when a user deletes their account, Stack goes through all of the posts with their owneruserid, and sets the ownerdisplayname value for historical purposes. Then, they can set the posts.owneruserid to null, and delete the users row.

Just for curiosity’s sake, let’s look at a few posts rows where the owneruserid is NOT null:

It kinda matches our hypothesis: they might not be populating the ownerdisplayname column until a user deletes their row. Some of the rows do have an ownerdisplayname populated though. This lines up with my real-world experience with decade-old production databases: the data is all over the place, in varying levels of explanations and quality. Perhaps Stack’s handling of deleted user accounts changed over time, too.

Question #3: any changes we want to make?

The last part of your query exercise was to think about any changes you might want to make to the app, processes, or database.

I’ll be honest with you, dear reader: I laid that as a trap for professional database administrators. When a DBA sees a database without foreign key referential integrity implemented, their first knee-jerk reaction is usually, “WE HAVE TO PUT IN FOREIGN KEYS RITE NAO TO FIX THIS PROBLEM!!1!!ONE!”

I agree with that in the beginning of an application’s lifecycle, but now that we’ve got a 15-year-old database with a ton of data that violates foreign key constraints, what are we supposed to do?

Could/should we add a hard-coded “Unknown User” row, and link the data to that? You’ll often see this with a “magic number” users row with an id of, say, 0 or -1. If we take that approach, we also have to modify our users-deletion process to assign posts.owneruserid to that magic value, or we have to implement triggers on tables to do that work. There are performance and concurrency issues associated with putting that work on the database tier, though.

In Martin’s excellent answer, another problem with the magic-number approach came up. “Magic numbers are bad because they’re pretty much always undocumented, they make the code hard to read and because new team members aren’t aware of them.” (I feel Martin’s pain, but in my experience, everything in the application is undocumented, so… there’s that.)

Could/should we set columns to null? If a User or Post is deleted, should we set all the referring columns to be null? And what should do that work, the app or a trigger? Foreign keys could be implemented in that case, but… our reports might not produce the data we expect if we’re doing inner joins. (Inner joins would work with the magic number approach above.)

Could we delete the data that doesn’t have valid relationships? If we did that, we would lose questions & answers on the site, stuff that’s valuable and helpful to looking for solutions. The business might be okay with that, though – that’s a business question, not a data professional’s question.

Even if we do either of those, we still can’t just implement foreign keys safely. We would need to do more investigation to find out if there are other causes for the missing users rows.

How’d you do? What’d you think?

I hope you had fun, enjoyed this exercise, and learned a little something. If you’ve got comments or questions, hit the comment section below. (Keep the comments focused on this exercise, though – we’re not covering the details of the Postgres export just yet. Only so many things I can cover per blog post, hahaha, and this thing’s already way long.)