В этой статье пойдет речь о том как ускорить запись большого объема информации в реляционную базу данных для приложений, написанных с использованием 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
@Serviceclass 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:
@Repositoryinterface GoodsPriceRepository: JpaRepository<GoodsPrice, Long> { @Modifying @Query(""" insert into dbo.GoodsPrice(GoodsId,Price,PriceDate)select res.*from dbo.SaveBuffer buffercross 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 PriceDatefrom buffer.xmlData.nodes('/ArrayList/item') temp(n)) reswhere 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 buffercross 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 PriceDatefrom buffer.xmlData.nodes('/ArrayList/item') temp(n)) reswhere 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.
Ссылка на тестовый проект
