Регистрация

[Программирование, Java, SQL, Kotlin] Spring: Ускоряем запись в базу данных с помощью XML

Ответить на тему
Автор Сообщение
news_bot ®

Стаж: 6 лет 9 месяцев
Сообщений: 27286

Создавать темы news_bot ® написал(а)
10-Ноя-2020 23:30
Цитировать

Всем привет!
В этой статье пойдет речь о том как ускорить запись большого объема информации в реляционную базу данных для приложений, написанных с использованием 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
 ===========
 Источник:
habr.com
===========
Похожие новости: Теги для поиска: #_programmirovanie (Программирование), #_java, #_sql, #_kotlin, #_sql, #_kotlin, #_spring_framework, #_java, #_uskorenie_zapisi (ускорение записи), #_programmirovanie (
Программирование
), #_java, #_sql, #_kotlin
Профиль  ЛС 
Показать сообщения:     
Ответить на тему

Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы

Текущее время: 22-Ноя 18:12
Часовой пояс: UTC + 5