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.

Ссылка на тестовый проект