Query Performance Improvement ‐ Create Template

Data Specifications

• 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 randomly generated per template)

Test Conditions

• Executed 100 times with 10 threads
• Total of 1000 requests executed

Template Generation Conditions

• Tags used: 20 (10 of which are non-existent tags)
• Source codes: 2

Before Improvement

Speed Measurement

• Total request count: 1000
• Total elapsed time: 81,569 ms
• Average elapsed time: 81 ms

Query Analysis

1. Retrieve Category to Store Template

• Repository: CategoryRepository
• 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. Store Template Content

• Repository: TemplateRepository
• Method: save

INSERT  
INTO  
    template  
(category_id, created_at, description, member_id, modified_at, title)  
VALUES  
    (?, ?, ?, ?, ?, ?) 

• Call Count: 1

3. Retrieve Existing Tag Names for Template

• Repository: TagRepository
• Method: findNameByNamesIn (custom query)

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

• Call Count: 1

4. 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: (Count of existing tags)

5. Save Existing Tags to Template

• Repository: TemplateTagRepository
• Method: saveAll

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 ((?, ?))

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

• Call Count: (Count of existing tags)

6. Save New Tags

• Repository: TagRepository
• Method: saveAll

INSERT  
INTO  
    tag  
(created_at, modified_at, name)  
VALUES  
    (?, ?, ?)

• Call Count: (Count of new tags)

7. Save New Tags to Template

• Repository: TemplateTagRepository
• Method: saveAll

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 ((?, ?))

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

• Call Count: (Count of new tags)

8. Save Source Code

• Repository: SourceCodeRepository
• Method: saveAll

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

• Call Count: Count of source codes

9. Retrieve Thumbnail Source Code

• Repository: SourceCodeJpaRepository
• Method: fetchByTemplateAndOrdinal

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.template_id = ?  
  AND sc1_0.ordinal = ?

• Call Count: 1

10. Save Thumbnail

• Repository: ThumbnailRepository
• Method: save

INSERT  
INTO  
    thumbnail  
(created_at, modified_at, source_code_id, template_id)  
VALUES  
    (?, ?, ?, ?)

• Call Count: 1

Required Improvements for Optimization

Index Improvement Suggestions

Tag Table

• Index Proposal:
  • name
    • Reason: Tags used in templates are retrieved by their names.
    • Addition Status: Yes, CREATE INDEX idx_tag_name ON tag(name);

EXPLAIN
    SELECT t1_0.id,
           t1_0.created_at,
           t1_0.modified_at,
           t1_0.name
    FROM tag t1_0
    WHERE t1_0.name IN ('newTag0', 'newTag1', 'newTag2');

id	select_type	type	possible_keys	key	Extra
1	SIMPLE	range	idx_tag_name	idx_tag_name	Using index condition

Query Optimization

Improvement of TemplateTag Creation Logic

As-is

After querying the existing tags by names into a List<String> existingTags, the TagRepository.fetchByName() is called for each name.
This results in calling TagRepository.fetchByName() as many times as there are existing tags.

    @Transactional
    public void createTags(Template template, List<String> tagNames) {
        List<String> existingTags = tagRepository.findNameByNamesIn(tagNames);

        templateTagRepository.saveAll(
                existingTags.stream()
                        .map(tagRepository::fetchByName) 
                        .map(tag -> new TemplateTag(template, tag))
                        .toList()
        );

        List<Tag> newTags = tagRepository.saveAll(
                tagNames.stream()
                        .filter(tagName -> !existingTags.contains(tagName))
                        .map(Tag::new)
                        .toList()
        ); 

        templateTagRepository.saveAll(
                newTags.stream()
                        .map(tag -> new Template

Tag(template, tag))
                        .toList()
        );
    }

• Total Call Count: (Count of existing tags) + (Count of new tags)

To-be

Using tagRepository.findNameByNamesIn(tagNames) will retrieve all existing tags in a single call, significantly reducing the total call count.

@Transactional
public void createTags(Template template, List<String> tagNames) {
    Map<String, Tag> existingTags = tagRepository.findByNameIn(tagNames).stream()
            .collect(Collectors.toMap(Tag::getName, Function.identity()));

    List<TemplateTag> templateTags = new ArrayList<>();
    
    for (String tagName : tagNames) {
        Tag tag = existingTags.get(tagName);
        if (tag != null) {
            templateTags.add(new TemplateTag(template, tag));
        } else {
            tag = new Tag(tagName);
            templateTags.add(new TemplateTag(template, tag));
        }
    }

    templateTagRepository.saveAll(templateTags);
}

• Total Call Count: 2 (One for fetching existing tags, one for saving template tags)

After Improvement

Speed Measurement

• Total request count: 1000
• Total elapsed time: 42,205 ms
• Average elapsed time: 42 ms