BugOfTheDay: How I tuned a procedure to find reservations

This time, one of the searching features for reservations was timing out. The appropiate store procedure took ~5 minutes to finish. This is how I tuned it.

To tune a store procedure, start by looking for expensive operators in its Actual Execution plan. Reduce the number of joining tables and stay away from common bad practices like putting functions around columns in WHERE clauses.

After opening the actual exection plan with SentryOne Plan Explorer, the most-CPU expensive and slowest statement looked like this:

DELETE res
FROM #resTemp res
WHERE reservationID NOT IN (
        SELECT res1.reservationID
        FROM #resTemp res1
        JOIN dbo.reservations res
            ON res.reservationID = res1.reservationID
        JOIN dbo.accounts a
            ON (a.accountID = res.accountID
                  OR a.accountID = res.columnWithAccountID
                  OR a.accountID = res.columnWithAccountIDToo)
                AND a.clientID = @clientID
        WHERE ISNULL(a.accountNumber, '') + ISNULL(a.accountNumberAlpha, '') LIKE @accountNumber + '%'
        );

This query belonged to a store procedure to search reservations by a bunch of filters. Among its filters, a hotelier can find all reservations assigned to a client’s internal account number.

From the above query, the #resTemp table had reservations from previous queries in the same store procedure. The DELETE statement removes all reservations without the given account number.

Inside SQL Server Management Studio, the store procedure did about 193 millions of logical reads to the dbo.accounts table. That’s a lot!

For SQL Server, logical reads are the number of 8KB pages that SQL Server has to read to execute a query. Generally, the fewer logical reads, the faster a query runs.

1. Remove extra joins

The subquery in the DELETE joined the found reservations with the dbo.reservations table. And then, it joined the dbo.accounts table checking for any of the three columns with an accountID. Yes, a reservation could have an accountID in three columns in the same table. Don’t ask me why.

This subquery performed an Index Scan on the dbo.reservations table. It had a couple of millions of records. That’s the main table in any Reservation Management System.

To remove the extra join to the dbo.reservations table in the subquery, I added the three referenced columns (accountID, columnWithAccountID, columnWithAccountIDToo) inside the ON joining the dbo.accounts to the #resTemp temporary table. By the way, those aren’t the real names of those columns.

After this change, the store procedure took ~8 seconds. It read about 165,000 pages for the dbo.accounts table. Wow!

DELETE res
FROM #resTemp res
WHERE reservationID NOT IN (
        SELECT res1.reservationID
        FROM #resTemp res1
        /* We don't need the extra JOIN here */
        INNER JOIN dbo.accounts a
            ON (a.accountID = res1.accountID
                  OR a.accountID = res1.columnWithAccountID
                  OR a.accountID = res1.columnWithAccountIDToo)
                AND a.clientID = @clientID
        WHERE ISNULL(a.accountNumber, '') + ISNULL(a.accountNumberAlpha, '') LIKE @accountNumber + '%'
        );

2. Use NOT EXISTS

Then, instead of NOT IN, I used NOT EXISTS. This way, I could lead the subquery from the dbo.accounts table. Another JOIN gone!

After this change, the store procedure finished in about 5 seconds.

DELETE res
FROM #resTemp res
WHERE NOT EXISTS (
        SELECT 1/0
        /* Again, we got rid of another JOIN */
        FROM dbo.accounts a
        WHERE (a.accountID = res.accountID
                OR a.accountID = res.columnWithAccountID
                OR a.accountID = res.columnWithAccountIDToo)
            AND a.clientID = @clientID
            AND ISNULL(a.accountNumber, '') + ISNULL(a.accountNumberAlpha, '') LIKE @accountNumber + '%'
        );

Those ~4-5 seconds were good enough. But, there was still room for improvement.

3. Don’t use functions in WHERE’s

The ISNULL() functions in the WHERE look weird. Using functions around columns in WHERE clauses is a common anti-pattern.

In this case, a computed column concatenating the two parts of account numbers would help. Yes, account numbers were stored splitted into two columns. Again, don’t ask me why.

ALTER TABLE dbo.accounts
    ADD AccountNumberComplete
    AS ISNULL(accountNumber, '') + ISNULL(accountNumberAlpha, '');

I didn’t use a persisted column. The dbo.accounts table was a huge table, creating a persisted columns would have required scanning the whole table. I only wanted SQL Server to have better statistics to run the DELETE statement.

To take things even further, an index leading on the ClientId followed by that computed column could make things even faster.

CREATE INDEX ClientID_AccountNumberComplete
    ON dbo.accounts(ClientID, AccountNumberComplete);

I didn’t need to include the accountId on the index definition since it was the primary key of the table.

Voilà! That’s how I tuned this query. The lesson to take home is to reduce the number of joining tables and stay away from functions in your WHERE’s. Often, a computed column can help SQL Server to run queries with functions in the WHERE clause. Even, without rewriting the query to use the new computed column.

For more content about SQL Server, check Six SQL Server tuning tips and Two free tools to format your SQL queries.

Happy coding!

TIL: EXISTS SELECT 1 vs EXISTS SELECT * in SQL Server

EXISTS is a logical operator that checks if a subquery returns any rows. EXISTS works only with SELECT statements inside the subquery. Let’s see if there are any differences between EXISTS with SELECT * and SELECT 1.

There is no difference between EXISTS with SELECT * and SELECT 1. SQL Server generates similar execution plans in both scenarios. EXISTS returns true if the subquery returns one or more records, even if it returns NULL or 1/0.

Let’s use a local copy of the StackOverflow database to find users from Antartica who have left any comments. Yes, the same StackOverflow we use everyday to copy and paste code.

Let’s check how the execution plans look like when using SELECT * and SELECT 1 in the subquery with the EXISTS operator.

1. EXISTS with “SELECT *”

This is the query to find all users from Antartica who have commented anything. This query uses EXISTS with SELECT *.

SELECT *
FROM dbo.Users u
WHERE u.Location = 'Antartica'
AND EXISTS(SELECT * FROM dbo.Comments c WHERE u.Id = c.UserId);
--         ^^^^^^^^

To make things faster, let’s add one index on Location and another one on UserId on the dbo.Users and dbo.Comments tables, respectively.

CREATE INDEX UserId ON dbo.Comments(UserId);
CREATE INDEX Location ON dbo.Users(Location);
GO

Let’s check the execution plan. Notice the “Left Semi Join” operator and the other operators.

2. EXISTS with “SELECT 1”

Now, let’s change the subquery inside the EXISTS to use SELECT 1.

SELECT *
FROM dbo.Users u
WHERE u.Location = 'Antartica'
AND EXISTS(SELECT 1 FROM dbo.Comments c WHERE u.Id = c.UserId)
--         ^^^^^^^^

Again, let’s see the execution plan.

Voilà! Notice, there is no difference between the two execution plans when using EXISTS with SELECT * and SELECT 1. We don’t need to write SELECT TOP 1 1 inside our EXISTS subqueries. We can even rewrite our queries to use SELECT NULL or SELECT 1/0 without any division-by-zero error.

If you want to read more SQL and SQL Server content, check how to write Dynamic SQL and three differences between TRUNCATE and DELETE.

Happy SQL time!

TIL: How to compare DateTime without the time part in SQL Server

If you use DATEDIFF() or CAST() to filter a table by a DATETIME column using only the date part, there’s a better way. Let’s find it out.

To compare dates without the time part, don’t use the DATEDIFF() or any other function on both sides of the comparison in a WHERE clause. Instead, put CAST() on the parameter and compare using >= and < operators.

Let’s use a local copy of the StackOverflow database to find all user profiles created on a particular date.

Inside StackOverflow database, there’s dbo.Users table with a CreationDate column. Let’s use that column to find all users who created their profiles today.

Before we get started, let’s create an index on CreationDate to make things faster.

CREATE INDEX CreationDate ON dbo.Users(CreationDate);

Probably, we would write a query like this one,

SELECT * FROM dbo.Users
WHERE DATEDIFF(DAY, CreationDate, GETDATE()) = 0;

But, SQL Server has to scan the entire index. Notice the “Index Scan” operator in the execution plan. SQL Server doesn’t have any statistics on CreationDate wrapped in a DATEDIFF() function.

An Index Scan by itself in an execution plan isn’t good or bad. It depends on the number of rows read.

In this case, SQL Server read all the records on the dbo.Users table. When we hover over the row next to the “Index Scan” operator, we notice the number of rows read. It scanned the whole index, more than 2 millions of records.

Let’s stop using DATEDIFF() or CAST() to filter a table on a DATETIME column.

To filter a table on a DATETIME column comparing only the date part, use CAST() only around the parameter, and >= and < with the desired date and the day after.

SELECT * FROM dbo.Users
 WHERE CreationDate >= CAST(GETDATE() AS DATE)
   AND CreationDate < DATEADD(day, 1, CAST(GETDATE() AS DATE));

Voilà! That’s how to compare dates on the WHERE clauses. Don’t use DATEDIFF() or CAST() on both sides of the comparison. In general, don’t put functions around columns in the WHERE clause.

For more content about SQL Server, check the difference between EXISTS SELECT 1 and EXISTS SELECT *, T-SQL doesn’t have constants and variables aren’t a good idea, and how LIKE handle NULL values.

Source: Optimized date compare in WHERE clause

Show your work. Takeaways

Show your work is a New York Times bestseller by Austin Kleon. He describes his book as “a book for people who hate the very idea of self-promotion”. This book tells you how and why you should show your work online. These are my takeaways.

“Show your work” teaches that your work has to be out there. And, if your work isn’t online, it doesn’t exist. Good work isn’t enough.

“Be so good they can’t ignore you” summarizes the purpose of the book. To not be ignored, you have to be findable. Build a routine of sharing. Take advantage of your network. Imagine you don’t need a resume because your next boss already reads your blog.

“Be so good they can’t ignore you.”

-Steve Martin

You don’t have to be a genius

Creativity is about collaboration, too. Find a place where you can share your ideas and flourish your creativity. Find your creativity circle or “scenius”. Famous musicians and artists were surrounded by other artists to share, copy, acknowledge and sparkle their ideas.

Ask yourself what you want to learn. And make a commitment to learning in front of others. Share what you love, and the people who love the same thing will find you.

Share something small every day

There is no such a thing as overnight success. At the end of the day, see what you can share: inspiration, progress, or learning. Share what you think it’s helpful or entertaining.

Don’t be afraid of sharing your work. 90% of everything is crap. But don’t turn into human spam.

“Stop worrying, start sharing”

-From Show your work official trailer

Open up your cabinet of curiosities

Have a cabinet of curiosities. Before museums, people had a place to put what they loved, usually rare and weird things. Think of yourself as a collector.

Before you can share your work, you can share your taste. You can share what you read, who you follow, what inspires you. Credit your sources. Don’t share things you can’t credit.

Voilà! These are my takeaways from Show Your Work. This isn’t a programming book, but it has inspired me to continue writing, even when I think nobody is reading.

If you want to read other takeaways, check Clean Coder and Pragmatic Thinking and Learning.

#showyourwork

TIL: How to add gzip compression to ASP.NET Core API responses

Today, I got the report that one API endpoint took minutes to respond. It turned out that it returned hundreds of large complex objects. Those objects contained branding colors, copy text, and hotel configurations in a reservation system. This is how to add response compression in ASP.NET Core 6.0.

To compress responses with ASP.NET Core, register the default compression providers into the dependencies container with the UseResponseCompression() method.

Something like this,

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddControllers();
builder.Services.AddResponseCompression();
//               ^^^^^

var app = builder.Build();
app.UseResponseCompression();
//  ^^^^^
app.MapControllers();
app.Run();

If we don’t specify any compression provider, ASP.NET Core uses a default one.

If we want gzip compression, then let’s register the GzipCompressionProvider inside AddResponseCompression() and set its compression level by configuring the GzipCompressionProviderOptions,

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddControllers();

builder.Services.AddResponseCompression(options =>
{
    options.Providers.Add<GzipCompressionProvider>();
    //                ^^^^^
});

builder.services.Configure<GzipCompressionProviderOptions>(options => 
{
    options.Level = CompressionLevel.Fastest;
    //      ^^^^^
});

var app = builder.Build();
app.UseResponseCompression();
// ^^^^^
app.MapControllers();
app.Run();

For my slow endpoint, the easiest solution to speed it up was mapping my huge complex object to a new view model that only contained the properties the client side needed. I rolled a simple extension method MapToMyNewSimplifiedViewModel() for that.

Voilà! That’s how to add gzip compression to responses with ASP.NET Core 6.0. That’s what I learned today.

UPDATE (Oct 2023): In previous versions of ASP.NET Core, we needed the Microsoft.AspNetCore.ResponseCompression NuGet package. It’s deprecated. ASP.NET Core has response compression built in now. We don’t need NuGet packages for this.

For more ASP.NET Core content, check how to read configuration values, how to create a caching layer, and how to use background services with Hangfire.

Source: Response compression in ASP.NET Core