Query Performance Improvement Template Update

Data Specification

Members: 10 entries
Categories: 100 entries (10 entries per member)
Tags: 2000 entries (200 entries per member)
Templates: 100,000 entries (10,000 entries per member)
Source Codes: 100,000 to 500,000 entries (1 to 5 random generated per template)

Test Conditions

Execute 100 times with 10 threads
Total of 1000 requests executed

Template Modification Conditions

Tags used: 10
Source Codes: 5 -> 5 (1 code modified / 2 codes created / 2 codes deleted)

Before Improvement

Speed Measurement

Total request count: 1000
Total elapsed time: 75215ms
Average elapsed time: 75ms

Query Analysis

1. Retrieve Categories of the Template

Repository: CategoryJpaRepository
Method: fetchById

SELECT  
    c1_0.id,  
    c1_0.created_at,  
    c1_0.is_default,  
    c1_0.member_id,  
    m1_0.id,  
    m1_0.created_at,  
    m1_0.modified_at,  
    m1_0.name,  
    m1_0.password,  
    m1_0.salt,  
    c1_0.modified_at,  
    c1_0.name  
FROM  
    category c1_0  
    JOIN member m1_0 ON m1_0.id=c1_0.member_id  
WHERE  
    c1_0.id=?

Call Count: 1

2. Retrieve the Template to be Modified

Repository: TemplateJpaRepository
Method: fetchById

SELECT  
    t1_0.id,  
    t1_0.category_id,  
    t1_0.created_at,  
    t1_0.description,  
    (SELECT COUNT(*) FROM likes WHERE likes.template_id = t1_0.id),  
    t1_0.member_id,  
    t1_0.modified_at,  
    t1_0.title  
FROM  
    template t1_0  
WHERE  
    t1_0.id=?

Call Count: 1

3. Remove Previously Connected Tags

Repository: TemplateTagRepository
Method: deleteAllByTemplateId

SELECT  
    tt1_0.tag_id,  
    tt1_0.template_id,  
    tt1_0.created_at,  
    tt1_0.modified_at  
FROM  
    template_tag tt1_0  
WHERE  
    tt1_0.template_id=?

Call Count: 1

SELECT  
    t1_0.id,  
    t1_0.created_at,  
    t1_0.modified_at,  
    t1_0.name  
FROM  
    tag t1_0  
WHERE  
    t1_0.id=?

Call Count: (Number of tags stored in the template)

4. Retrieve the Names of the Tags to be Used

Repository: TagJpaRepository
Method: findNameByNamesIn

SELECT  
    t1_0.name  
FROM  
    tag t1_0  
WHERE  
    t1_0.name IN (?, ?, ?, ?, ?)

Call Count: 1

5. Retrieve Existing Tags

Repository: TagRepository
Method: fetchByName

SELECT  
    t1_0.id,  
    t1_0.created_at,  
    t1_0.modified_at,  
    t1_0.name  
FROM  
    tag t1_0  
WHERE  
    t1_0.name=?

Call Count: (Number of existing tags from the total tags)

6. Update Template Content

The domain update query is sent late.

UPDATE  
    template  
SET  
    category_id=?,  
    description=?,  
    member_id=?,  
    modified_at=?,  
    title=?  
WHERE  
    id=?

Call Count: 1

7. (3. Tag Removal) Deletion Query is Sent Late

DELETE  
FROM  
    template_tag  
WHERE  
    tag_id=?  
    AND template_id=?

Call Count: (Number of tags stored in the template)

8. Save Existing Tags to Template

SELECT  
    tt1_0.tag_id,  
    tt1_0.template_id,  
    tt1_0.created_at,  
    tt1_0.modified_at,  
    t1_0.id,  
    t1_0.created_at,  
    t1_0.modified_at,  
    t1_0.name,  
    t2_0.id,  
    t2_0.category_id,  
    t2_0.created_at,  
    t2_0.description,  
    (SELECT COUNT(*) FROM likes WHERE likes.template_id = t2_0.id),  
    t2_0.member_id,  
    t2_0.modified_at,  
    t2_0.title  
FROM  
    template_tag tt1_0  
    JOIN tag t1_0 ON t1_0.id=tt1_0.tag_id  
    JOIN template t2_0 ON t2_0.id=tt1_0.template_id  
WHERE  
    (tt1_0.tag_id, tt1_0.template_id) IN ((?, ?))

Call Count: (Number of tags stored in the template)

9. Retrieve Thumbnail

SELECT  
    t1_0.id,  
    t1_0.created_at,  
    t1_0.modified_at,  
    t1_0.source_code_id,  
    t1_0.template_id  
FROM  
    thumbnail t1_0  
WHERE  
    t1_0.template_id=?

SELECT  
    sc1_0.id,  
    sc1_0.content,  
    sc1_0.created_at,  
    sc1_0.filename,  
    sc1_0.modified_at,  
    sc1_0.ordinal,  
    sc1_0.template_id  
FROM  
    source_code sc1_0  
WHERE  
    sc1_0.id=?

Call Count: 1

10. Retrieve Source Code to be Modified

SELECT  
    sc1_0.id,  
    sc1_0.content,  
    sc1_0.created_at,  
    sc1_0.filename,  
    sc1_0.modified_at,  
    sc1_0.ordinal,  
    sc1_0.template_id  
FROM  
    source_code sc1_0  
WHERE  
    sc1_0.id=?

Call Count: 1

11. Save Tags to Template

INSERT  
INTO  
    template_tag  
(created_at, modified_at, tag_id, template_id)  
VALUES  
    (?, ?, ?, ?)

Call Count: (Number of tags used)

12. Save Source Code

INSERT   
INTO  
    source_code  
(content, created_at, filename, modified_at, ordinal, template_id)  
VALUES  
    (?, ?, ?, ?, ?, ?)

Call Count: (New source codes)

13. Delete Source Code

SELECT  
    sc1_0.id,  
    sc1_0.content,  
    sc1_0.created_at,  
    sc1_0.filename,  
    sc1_0.modified_at,  
    sc1_0.ordinal,  
    sc1_0.template_id  
FROM  
    source_code sc1_0  
WHERE  
    sc1_0.id=?

Call Count: (Source codes to be deleted)

14. Update Source Code

UPDATE  
    source_code  
SET  
    content=?,  
    filename=?,  
    modified_at=?,  
    ordinal=?,  
    template_id=?  
WHERE  
    id=?

Call Count: (Source codes to be updated)

15. Delete Source Code (Later Executed Query)

DELETE  
FROM  
    source_code  
WHERE  
    id=?

Call Count: (Source codes to be deleted)

16. Count Source Codes

SELECT  
    COUNT(sc1_0.id)  
FROM  
    source_code sc1_0  
WHERE  
    sc1_0.template_id=?

Call Count: 1

Necessary Actions for Improvement

Index Improvement Suggestions

Tag Table

Index Suggestion:
- name
  - Reason: Tags to be used in the template are queried by name.
  - Additional: Yes, CREATE INDEX idx_tag_name ON tag(name);

Query Optimization

Improvement of `TemplateTag` Creation Logic

as-is

Existing tags are retrieved by name in `

tag. As this operation does not require data and is only for modification, the name` field is used.

to-be

If there are existing tags to be connected to the template, they can be added to template_tag.

Instead of retrieving existing tags, check if the tag already exists and, if not, add it to tag.
After that, you can add it to template_tag.

Summary

A performance improvement plan was prepared for the template modification process in the template registration function of the template service.
The performance test results indicated that the average processing time for 1000 requests was 75ms.
Query execution was analyzed, and it was confirmed that excessive queries were being executed, especially when retrieving existing tags.
An index was recommended for the tag table to reduce search time for tag names, and the logic for creating template_tag was proposed to avoid redundant queries.