Пакетная вставка с помощью Spring JDBC API

24 ноября 2021

Тэги: Kotlin, PostgreSQL, Spring, Spring Boot, SQL, YouTube.

Содержание

Модель данных
Вариант 1. Вставка по очереди
Вариант 2. Вставка по очереди в транзакции
Вариант 3. Пакетная вставка batchUpdate
Выводы

Давайте рассмотрим, как обеспечить пакетную вставку (batch insert) большого количества данных с помощью Spring JDBC API. Допустим, нам нужно вставить 100 000 строк в таблицу в postgres. В качестве драйвера для работы с БД используем стандартный org.postgresql:postgresql.

Мы рассмотрим несколько вариантов, начиная с самого простого и заканчивая самым быстрым, постепенно улучшая нашу реализацию. Если вам нужен финальный вариант, листайте сразу к последнему.

Данный материал доступен также в формате видео на YouTube.

Модель данных

Допустим у нас есть такая таблица для хранения истории каких-то заказов:

create table amount_history
(
    id       serial
        constraint amount_history_pk
            primary key,
    username varchar   not null,
    amount   numeric   not null,
    created  timestamp not null
);

В Kotlin ей будет соответствовать структура:

data class AmountItem(
    val id: Int = 0,
    val username: String,
    val amount: BigDecimal,
    val created: LocalDateTime,
)

Вариант 1. Вставка по очереди

Наиболее простой вариант вставки с помощью NamedParameterJdbcTemplate:

@Repository
class AmountRepositoryImpl(private val jdbcTemplate: NamedParameterJdbcTemplate) : AmountRepository {
    override fun saveAll(items: List<AmountItem>) {
        items.forEach { item ->
            jdbcTemplate.update(
                "insert into amount_history (username, amount, created) values (:username, :amount, :created)",
                MapSqlParameterSource()
                    .addValue("username", item.username)
                    .addValue("amount", item.amount)
                    .addValue("created", item.created)
            )
        }
    }
}

Здесь мы на каждую из 100 тысяч записей делаем отдельный insert. На моём ноуте с SSD такая реализация при работе с локальной БД выполняется 2 с лишним минуты – это очень долго.

Вариант 2. Вставка по очереди в транзакции

Мы можем значительно улучшить предыдущий вариант, если добавим аннотацию @Transactional. Поскольку запросы будут выполняться в транзакции, это позволит движку СУБД не делать лишних действий.

@Transactional
override fun saveAll(items: List<AmountItem>) {
    items.forEach { item ->
        jdbcTemplate.update(
            "insert into amount_history (username, amount, created) values (:username, :amount, :created)",
            MapSqlParameterSource()
                .addValue("username", item.username, Types.VARCHAR)
                .addValue("amount", item.amount, Types.NUMERIC)
                .addValue("created", item.created, Types.TIMESTAMP)
        )
    }
}

Также небольшой прирост производительности даст явное указание типов данных с помощью третьего параметра в методе addValue(). Тогда драйвер postgres не будет запрашивать метаданные таблицы.

В итоге данная реализация выполняется у меня где-то за 7 секунд. Прирост скорости на порядок, без значительных изменений кода.

Вариант 3. Пакетная вставка batchUpdate

Однако наш драйвер помимо обычной вставки поддерживает также и пакетную. То есть умеет вставлять все данные за один запрос. Для этого мы сначала формируем набор значений в виде типизированного массива для каждой из 100 тысяч строк, а затем вызываем метод batchUpdate():

override fun batchInsert(items: List<AmountItem>) {
    val batchValues = items.map { item ->
        MapSqlParameterSource()
            .addValue("username", item.username, Types.VARCHAR)
            .addValue("amount", item.amount, Types.NUMERIC)
            .addValue("created", item.created, Types.TIMESTAMP)
    }.toTypedArray()

    jdbcTemplate.batchUpdate(
        "insert into amount_history (username, amount, created) values (:username, :amount, :created)",
        batchValues
    )
}

Данная реализация выполняется ещё быстрее: примерно за 2.5 – 3 секунды. По сравнению с предыдущим, этот вариант даёт прирост скорости более чем в 2 раза. Наличие транзакции в данном варианте прироста особо не даёт, что вполне ожидаемо.

Выводы

Я допускаю, что на скорость вставки также могут влиять различные настройки самой БД, поэтому замеры производил на стандартной версии postgres, которую вообще никак не настраивал.

Исходя из сказанного выше можно рекомендовать использовать batchUpdate() при вставке большого количества строк в таблицу. Если же этот вариант по каким-то причинам не подходит, тогда можно обернуть обычный update() в транзакцию – это также даст существенный прирост в скорости. Дополнительный прирост может дать явное указание типов данных для каждого параметра запроса.

Облако тэгов

Kotlin, Java, Spring, Spring Boot, Spring Data, SQL, PostgreSQL, Oracle, Linux, Hibernate, Collections, Stream API, многопоточность, файлы, Nginx, Apache, maven, gradle, JUnit, YouTube, новости, руководство, ООП, алгоритмы, головоломки, rest, GraphQL, Excel, XML, json, yaml.