الربيع: تسريع كتابة قاعدة البيانات باستخدام XML

مرحبا!



ستناقش هذه المقالة كيفية تسريع كتابة كميات كبيرة من المعلومات في قاعدة بيانات علائقية للتطبيقات المكتوبة باستخدام Spring Boot. عند كتابة عدد كبير من الصفوف في المرة الواحدة ، يقوم Hibernate بإدراجها واحدة تلو الأخرى ، مما يؤدي إلى انتظار كبير في حالة وجود العديد من الصفوف. دعنا نفكر في حالة كيفية الالتفاف على هذا.



نستخدم تطبيق Spring Boot. باعتباره DBMS -> MS SQL Server ، كلغة برمجة - Kotlin. بالطبع ، لن يكون هناك اختلاف في Java.



كيان البيانات التي نحتاج إلى كتابتها:

@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 و 50000 سجل لكل منهما.



لنقم بإنشاء وحدة تحكم تقوم بتوليد البيانات ونقلها للتسجيل وتسجيل الوقت:

@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 الخاصة بنا لعدد 20000 سجل و 50000 سجل.



وحدة التحكم:

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 حاول إدراج كل صف في استعلام منفصل ، أي 20000 مرة. واستغرق الأمر 63 ثانية على جهازي.



لـ 50000 إدخال 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 للجدول في قاعدة البيانات

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. وهذا يعني أنه لن يكون هناك 20000 إدراج ، ولكن فقط 1.



وبعد ذلك نقوم بنقل البيانات من المخزن المؤقت إلى جدول 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 الخاصة بنا لـ 20000 سطر و 50000 سطر:

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

كما ترى من النتائج ، حصلنا على تسريع كبير في تسجيل البيانات.

بالنسبة إلى 20000 سجل ، كانت 13 ثانية هي 63.

بالنسبة إلى 50000 سجل ، كانت 27 ثانية هي 166.



رابط لاختبار المشروع