[SQL Server] What mattered when we tuned batch performance

A batch job I wrote at work was slow, so I spent time improving it. This post is about what I would do differently next time, and what actually mattered.

This article talks about SQL Server indexes. For an overview, please see this post.

[SQL Server] Indexes and execution plans

The rest of this post assumes you need to process large volumes of data. If the row counts are small, extra indexes and more complex queries can hurt more than they help. The real premise is that you design against the non-functional requirements.

Batch overview

Architecture

FW: Spring Batch, DB: SQL Server

Processing overview

I built two batch jobs. Both assume you join tables with millions to tens of millions of rows while processing.

① Join many tables (7 tables), fetch the data, aggregate and transform it, then insert into another table
② Fetch a large set of rows (hundreds of thousands to millions), call an external service, and update the table after the call completes

Prerequisites and assumptions

The walkthrough assumes tables and data like the following as test data.

-- カテゴリー
CREATE TABLE blog.category (
    category_id INT NOT NULL
    , category_name NVARCHAR(20) NOT NULL
    , PRIMARY KEY(category_id)
)

-- 店舗
CREATE TABLE blog.shop (
    shop_id INT NOT NULL
    , shop_name NVARCHAR(20) NOT NULL
    , category_id INT NOT NULL
    , status VARCHAR(20) NOT NULL
    , start_date DATE NOT NULL
    , end_date DATE NOT NULL
    , PRIMARY KEY(shop_id)
    , FOREIGN KEY(category_id) REFERENCES blog.category(category_id) 
)

-- ユーザ
CREATE TABLE blog.my_user (
    user_id INT NOT NULL
    , first_name NVARCHAR(20) NOT NULL
    , last_name NVARCHAR(20) NOT NULL
    , age INT NOT NULL
    , status VARCHAR(20) NOT NULL
    , PRIMARY KEY(user_id)
)

-- 取引
CREATE TABLE blog.sales (
    sales_id INT NOT NULL
    , amount INT NOT NULL
    , transaction_date DATE NOT NULL
    , status VARCHAR(20) NOT NULL
    , cancel_flg BIT NOT NULL
    , shop_id INT NOT NULL
    , user_id INT NOT NULL 
    , PRIMARY KEY(sales_id)
    , FOREIGN KEY(user_id) REFERENCES blog.my_user(user_id) 
    , FOREIGN KEY(shop_id) REFERENCES blog.shop(shop_id) 
)

• Shop (shop) data
  Tens of thousands of rows, and more than 99% of the status values are “active”.
  Status values: active, suspended, withdrawn

• User (my_user) data
  Tens of thousands of rows, and more than 99% of the status values are “active”.
  Status values: active, suspended, withdrawn

• Sales (sales) data
  Hundreds of thousands of rows. Transaction dates fall between 2022-01-01 and 2022-12-31, and more than 99% of the status values are “success”.
  Status values: success, failure

What I actually did

Redesigning and rewriting queries

At a high level, this is what I did. Removing unused join conditions is exactly what it sounds like. I will walk through the rest.

• Remove unused join conditions
• Filter in a subquery, then join
• Select only the columns you need
• Do not join everything in one query
• Do not process the entire dataset in one pass; work in smaller units

Filter in a subquery, then join

For example, if you want people aged 30 or older who transacted on 2022-01-01, the straightforward query looks like this.

SELECT *
FROM blog.sales 
    LEFT OUTER JOIN 
        blog.my_user 
        on sales.user_id = my_user.user_id
WHERE
    sales.transaction_date = '2022-01-01'
    and my_user.age >= 30

Written this way, you join all sales rows to all user rows and only then filter in the WHERE clause. You pay the cost of joining rows you never needed.

In that situation, it helps to filter sales by transaction date first.

SELECT *
FROM (
        SELECT * 
            FROM blog.sales
            WHERE transaction_date = '2022-01-01'
    ) sales
    LEFT OUTER JOIN 
        blog.my_user 
        on sales.user_id = my_user.user_id
WHERE 
    my_user.age >=30

Written this way, the sales rows are filtered by date first. If you assume a full year of data, you join users to a table that is already reduced to 1/365 of the original.
You can get the same result by also filtering users to age 30 or older in a subquery and switching to an INNER JOIN.

Select only the columns you need

* keeps the query short and easy to read, but it costs more to fetch. With many joins, you pull every column from every joined table.

SELECT 
    sales.sales_id
    , sales.amount
    , my_user.first_name
FROM (
        SELECT sales_id,amount,user_id,shop_id
            FROM blog.sales
            WHERE transaction_date = '2022-01-01'
    ) sales
    LEFT OUTER JOIN 
        (
            SELECT user_id,first_name,age
                FROM blog.my_user
        ) my_user
        on sales.user_id = my_user.user_id
WHERE 
    my_user.age >=30

Keeping subquery columns to a minimum also makes it possible to list them in the INCLUDE clause when you create a covering index. (If INCLUDE gets too wide, the index bloats, so a fat SELECT list is not a good idea.)

Do not join everything in one query

Take the previous example and add shop and category data.

SELECT 
    sales.sales_id
    , sales.amount
    , my_user.first_name
    , shop.shop_name
    , category.category_name
FROM (
        SELECT sales_id,amount,user_id,shop_id
            FROM blog.sales
            WHERE transaction_date = '2022-01-01'
    ) sales
    LEFT OUTER JOIN 
        (
            SELECT user_id,first_name,age
                FROM blog.my_user
        ) my_user
        on sales.user_id = my_user.user_id
    LEFT OUTER JOIN -- 店舗データを追加
        (
            SELECT shop_id,shop_name,category_id
                FROM blog.shop 
        ) shop
        on sales.shop_id = shop.shop_id
    LEFT OUTER JOIN -- カテゴリーデータを追加
        blog.category 
        on shop.category_id = category.category_id
WHERE 
    my_user.age >=30

The straightforward version looks like that, but more joins make the search slower and can even time out. In that case, I think you should consider fetching in stages instead of insisting on one join.

If joining the shop table makes the query heavy, fetch the sales data in the first stage,

SELECT 
    sales.sales_id
    , sales.amount
    , my_user.first_name
    , sales.shop_id -- 店舗IDを追加
FROM (
        SELECT sales_id,amount,user_id,shop_id
            FROM blog.sales
            WHERE transaction_date = '2022-01-01'
    ) sales
    LEFT OUTER JOIN 
        (
            SELECT user_id,first_name,age
                FROM blog.my_user
        ) my_user
        on sales.user_id = my_user.user_id
WHERE 
    my_user.age >=30

then build a unique shop ID list in the program from the first-stage result, and in the second stage look up shops and categories with an IN list.

SELECT 
    shop.shop_id
    , shop.shop_name
    , category.category_name
FROM (
        SELECT * 
            FROM blog.shop
            WHERE shop_id IN (<作成した店舗IDリスト>)
    ) shop
    LEFT OUTER JOIN
        blog.category 
        ON shop.category_id = category.category_id

You can turn the second-stage shop rows into a map keyed by shop ID, then join them in the program to the first-stage rows.

Measuring queries at a small grain

While you redesign the query, measure the deepest nested part and the query that produces the driving rowset.

SELECT 
    sales.sales_id
    , sales.amount
    , my_user.first_name
    , shop.shop_name
    , category.category_name
FROM (
        SELECT sales_id,amount,user_id,shop_id
            FROM blog.sales
            WHERE transaction_date = '2022-01-01'
    ) sales
    LEFT OUTER JOIN 
        (
            SELECT user_id,first_name,age
                FROM blog.my_user
        ) my_user
        on sales.user_id = my_user.user_id
    LEFT OUTER JOIN -- 店舗データを追加
        (
            SELECT shop_id,shop_name,category_id
                FROM blog.shop 
        ) shop
        on sales.shop_id = shop.shop_id
    LEFT OUTER JOIN -- カテゴリーデータを追加
        blog.category 
        on shop.category_id = category.category_id
WHERE 
    my_user.age >=30

When you run a query like that, measure the sales query first.

SELECT 
    sales_id
    , amount
    , user_id
    , shop_id
    FROM blog.sales
    WHERE transaction_date = '2022-01-01'

If this query is slow, joining more tables afterward will definitely be slow. You need to improve it by adjusting indexes, search conditions, and the spec. If you create an index, a covering index like the following is likely to be the fastest search.

CREATE NONCLUSTERED INDEX IX_sales_01 
    ON blog.sales(transaction_date) 
    INCLUDE (sales_id,amount,user_id,shop_id)

Do the same for the other subqueries: make each subquery fast on its own, then join, and the combined search will be fast.

Index tuning

As in the example above, use covering indexes, clustered indexes, and nonclustered indexes to make searches fast. For index details, see the SQL Server index post.

What matters

Think about performance from the design stage

Indexes can only do so much, so you still need to consider performance in the design. What unit do you fetch, update, or insert? Which joins are best? I found it important to judge those together and write both the program and the queries accordingly. With tens of thousands of rows, or even a few hundred thousand at most, you can often get away with less care. With millions or tens of millions of rows, design matters a lot.

Measure performance from the design stage

That said, if you do not yet have a feel for performance, an optimal design at the design stage may be hard. Even if you do have a feel, measurement often disagrees with the plan. So I think it is important to stand up a performance-test environment early and measure. In particular, if the driving subquery or the smallest nested query is already slow, later joins will almost certainly be very slow. If you measure from the small units and add joins gradually, you can still deal with it even without a strong instinct.

Caveats

Queries issued through an ORM are not guaranteed to get an optimal plan

At work we issue queries through an ORM library called jOOQ. Through an ORM, the plan can differ from the plan you get when you run the same query from a DB client tool. If the tool search is fast but the program is slow, you need to compare execution plans.
In my case I ended up assembling the query by string concatenation so the plan matched the one from the tool. Prepared statements no longer applied, which is not great for security, but the search did not use user input, so I accepted it.

Keep indexes to the minimum that are actually optimal

Indexes speed up reads, but updates also have to maintain index pages, so the cost goes up. Watch the following.

• Keep the number of indexes to a minimum
• Keep ON-clause columns to a minimum
• Keep INCLUDE-clause columns to a minimum

Keep the number of indexes to a minimum

Create only the indexes you actually need. If they do not speed up reads and only slow down writes, you should drop them.

Keep ON-clause columns to a minimum

Do not add ON-clause columns casually either. For example, we assume the sales status column is “success” for more than 99% of rows. Putting status in the index ON clause to fetch “success” rows barely filters anything. The index just gets larger. I think it is better to nest a subquery, filter on other conditions first, and then filter by status.

Keep INCLUDE-clause columns to a minimum

In SQL Server, INCLUDE lets you create a covering index that can search faster than a normal nonclustered index. A covering index is also larger than a nonclustered index and slows writes, so you need to keep this minimal as well.

Watch the data distribution in the performance environment

To stand up a performance-test environment with a large volume of data, bulk import with the BCP command works well. See the following post for how to use it.

[SQL Server] Bulk-importing large data with BCP

When you build the data, the caution is to spread out values in columns that are likely to become search conditions or index ON-clause columns, so the data looks realistic. If every row has the same value, an index may not be used and the performance test will not tell you anything.

Takeaways

I think the important things are to design with performance in mind from the start, and to actually measure.

There is a limit to how much indexes can fix, and as above, creating too many indexes also hurts. First, consider how many rows you are working from, how much of that data you actually handle, what unit you should process in, and what the performance target is. If performance is bad after the other tests are done and you have to rebuild, you also have to redo the tests, so the rework gets large.

Measuring is an important action too. You may find the plan is nothing like what you pictured and performance is poor, so measure early. While you still lack a feel for performance, running things and measuring as you go is how you build that feel and get better at improving it.