Spring: Ускоряем запись в базу данных с помощью XML

Всем привет!

В этой статье пойдет речь о том как ускорить запись большого объема информации в реляционную базу данных для приложений, написанных с использованием Spring Boot. При записи большого количества строк за раз Hibernate вставляет их по одному, что приводит к существенному ожиданию, если строк много. Рассмотрим кейс как это обойти.

Используем Spring Boot приложение. В качестве СУБД -> MS SQL Server, в качестве языка программирования- Kotlin. Разумеется для Java разницы не будет.

Entity для данных, которые нам необходимо записывать:

@Entity
@Table(schema = BaseEntity.schemaName, name = GoodsPrice.tableName)
data class GoodsPrice(

        @Id
        @Column(name = "GoodsPriceId")
        @GeneratedValue(strategy =  GenerationType.IDENTITY)
        override val id: Long,

        @Column(name = "GoodsId")
        val goodsId: Long,

        @Column(name = "Price")
        val price: BigDecimal,

        @Column(name = "PriceDate")
        val priceDate: LocalDate
): BaseEntity(id) {
        companion object {
                const val tableName: String = "GoodsPrice"
        }
}

SQL:

CREATE TABLE [dbo].[GoodsPrice](
	[GoodsPriceId] [int] IDENTITY(1,1) NOT NULL,
	[GoodsId] [int] NOT NULL,
	[Price] [numeric](18, 2) NOT NULL,
	[PriceDate] nvarchar(10) NOT NULL,
 CONSTRAINT [PK_GoodsPrice] PRIMARY KEY(GoodsPriceId))

В качестве демонстрационного примера будем предполагать, что нам необходимо записывать по 20 000 и по 50 000 записей.

Создадим контроллер, который будет генерировать данные и передавать их на запись и логгировать время:

@RestController
@RequestMapping("/api")
class SaveDataController(private val goodsPriceService: GoodsPriceService) {

    @PostMapping("/saveViaJPA")
    fun saveDataViaJPA(@RequestParam count: Int) {
        val timeStart = System.currentTimeMillis()
        goodsPriceService.saveAll(prepareData(count))
        val secSpent = (System.currentTimeMillis() - timeStart) / 60
        logger.info("Seconds spent : $secSpent")
    }

    private fun prepareData(count: Int) : List<GoodsPrice> {
        val prices = mutableListOf<GoodsPrice>()
        for (i in 1..count) {
            prices.add(GoodsPrice(
                    id = 0L,
                    priceDate = LocalDate.now().minusDays(i.toLong()),
                    goodsId = 1L,
                    price = BigDecimal.TEN
            ))
        }
        return prices
    }
    companion object {
        private val logger = LoggerFactory.getLogger(SaveDataController::class.java)
    }
}

Так же создадим сервис для записи данных и репозиторий GoodsPriceRepository

@Service
class GoodsPriceService(
        private val goodsPriceRepository: GoodsPriceRepository
) {

    private val xmlMapper: XmlMapper = XmlMapper()

    fun saveAll(prices: List<GoodsPrice>) {
        goodsPriceRepository.saveAll(prices)
    }
}

После этого последовательно вызовем наш метод saveDataViaJPA для 20 000 записей и 50 000 записей
Консоль:
Hibernate: insert into dbo.GoodsPrice (GoodsId, Price, PriceDate) values (?, ?, ?)
Hibernate: insert into dbo.GoodsPrice (GoodsId, Price, PriceDate) values (?, ?, ?)
Hibernate: insert into dbo.GoodsPrice (GoodsId, Price, PriceDate) values (?, ?, ?)
Hibernate: insert into dbo.GoodsPrice (GoodsId, Price, PriceDate) values (?, ?, ?)
Hibernate: insert into dbo.GoodsPrice (GoodsId, Price, PriceDate) values (?, ?, ?)
Hibernate: insert into dbo.GoodsPrice (GoodsId, Price, PriceDate) values (?, ?, ?)
Hibernate: insert into dbo.GoodsPrice (GoodsId, Price, PriceDate) values (?, ?, ?)
Hibernate: insert into dbo.GoodsPrice (GoodsId, Price, PriceDate) values (?, ?, ?)
Hibernate: insert into dbo.GoodsPrice (GoodsId, Price, PriceDate) values (?, ?, ?)
Hibernate: insert into dbo.GoodsPrice (GoodsId, Price, PriceDate) values (?, ?, ?)
Hibernate: insert into dbo.GoodsPrice (GoodsId, Price, PriceDate) values (?, ?, ?)
Hibernate: insert into dbo.GoodsPrice (GoodsId, Price, PriceDate) values (?, ?, ?)
Hibernate: insert into dbo.GoodsPrice (GoodsId, Price, PriceDate) values (?, ?, ?)
Hibernate: insert into dbo.GoodsPrice (GoodsId, Price, PriceDate) values (?, ?, ?)
2020-11-10 19:11:58.886 INFO 10364 --- [ restartedMain] xmlsave.controller.SaveDataController : Seconds spent : 63
Проблема заключается в том что Hibernate пытался встававить каждую строку отдельным запросом, то есть 20 000 раз. И на моей машине это заняло 63 сек.
Для 50 000 записей 166 сек.

Решение
Что можно сделать? Главная идея заключается в том, что будем записывать через буфферную таблицу:

@Entity
@Table(schema = BaseEntity.schemaName, name = SaveBuffer.tableName)
data class SaveBuffer(

        @Id
        @Column(name = "BufferId")
        @GeneratedValue(strategy =  GenerationType.IDENTITY)
        override val id: Long,

        @Column(name = "UUID")
        val uuid: String,

        @Column(name = "xmlData")
        val xmlData: String
): BaseEntity(id) {
        companion object {
                const val tableName: String = "SaveBuffer"
        }
}

SQL script для таблицы в базе данных

CREATE TABLE [dbo].[SaveBuffer](
	[BufferId] [int] IDENTITY NOT NULL,
	[UUID] [varchar](64) NOT NULL,
	[xmlData] [xml] NULL,
 CONSTRAINT [PK_SaveBuffer] PRIMARY KEY (BufferId))

В SaveDataController добавим метод

@PostMapping("/saveViaBuffer")
    fun saveViaBuffer(@RequestParam count: Int) {
        val timeStart = System.currentTimeMillis()
        goodsPriceService.saveViaBuffer(prepareData(count))
        val secSpent = (System.currentTimeMillis() - timeStart) / 60
        logger.info("Seconds spent : $secSpent")
    }

Так же добавим в GoodsPriceService метод

@Transactional
    fun saveViaBuffer(prices: List<GoodsPrice>) {
        val uuid = UUID.randomUUID().toString()
        val values = prices.map {
            BufferDTO(
                    goodsId = it.goodsId,
                    priceDate = it.priceDate.format(DateTimeFormatter.ISO_DATE),
                    price = it.price.stripTrailingZeros().toPlainString()
            )
        }
        bufferRepository.save(
                    SaveBuffer(
                            id = 0L,
                            uuid = uuid,
                            xmlData = xmlMapper.writeValueAsString(values)
                    )
            )
        goodsPriceRepository.saveViaBuffer(uuid)
        bufferRepository.deleteAllByUuid(uuid)
    }

Для записи для начала генерим уникальный uuid, чтобы отличить текущие данные, которые записываем. Далее записываем наши данные в созданный буффер текстом в виде xml. То есть будет не 20 000 инсертов, а всего 1.
И после этого перебрасываем одним запросом типа Insert into… select данные из буффера в таблицу GoodsPrice.
GoodsPriceRepository с методом saveViaBuffer:

@Repository
interface GoodsPriceRepository: JpaRepository<GoodsPrice, Long> {
    @Modifying
    @Query("""
    insert into dbo.GoodsPrice(
	GoodsId,
	Price,
	PriceDate
	)
	select res.*
	from dbo.SaveBuffer buffer
		cross apply(select temp.n.value('goodsId[1]', 'int') as GoodsId
			, temp.n.value('price[1]', 'numeric(18, 2)') as Price
			, temp.n.value('priceDate[1]', 'nvarchar(10)') as PriceDate
			from buffer.xmlData.nodes('/ArrayList/item') temp(n)) res
			where buffer.UUID = :uuid
    """, nativeQuery = true)
    fun saveViaBuffer(uuid: String)
}

И в конце для того, чтобы не хранить в базе дублированную информацию удаляем по uuid данные из буффера.

Вызовем наш метод saveViaBuffer для 20 000 строк и 50 000 строк:

Hibernate: insert into dbo.SaveBuffer (UUID, xmlData) values (?, ?)
Hibernate: insert into dbo.SaveBuffer (UUID, xmlData) values (?, ?)
Hibernate: insert into dbo.SaveBuffer (UUID, xmlData) values (?, ?)
Hibernate: insert into dbo.SaveBuffer (UUID, xmlData) values (?, ?)
Hibernate: 
    insert into dbo.GoodsPrice(
	GoodsId,
	Price,
	PriceDate
	)
	select res.*
	from dbo.SaveBuffer buffer
		cross apply(select temp.n.value('goodsId[1]', 'int') as GoodsId
			, temp.n.value('price[1]', 'numeric(18, 2)') as Price
			, temp.n.value('priceDate[1]', 'nvarchar(10)') as PriceDate
			from buffer.xmlData.nodes('/ArrayList/item') temp(n)) res
			where buffer.UUID = ?
    
Hibernate: select savebuffer0_.BufferId as bufferid1_1_, savebuffer0_.UUID as uuid2_1_, savebuffer0_.xmlData as xmldata3_1_ from dbo.SaveBuffer savebuffer0_ where savebuffer0_.UUID=?
Hibernate: delete from dbo.SaveBuffer where BufferId=?
Hibernate: delete from dbo.SaveBuffer where BufferId=?
Hibernate: delete from dbo.SaveBuffer where BufferId=?
Hibernate: delete from dbo.SaveBuffer where BufferId=?
2020-11-10 20:01:58.788  INFO 7224 --- [  restartedMain] xmlsave.controller.SaveDataController    : Seconds spent : 13

Как видим по результатам получили существенное ускорение записи данных.
Для 20 000 записей 13 секунд было 63.
Для 50 000 записей 27 секунд было 166.

Ссылка на тестовый проект: github.com/cerrenesi/xmlSave.git