Query Performance Improvement Single Template Retrieval

Data Specification

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

Computer Specifications

• Operating System: Windows 11
• Processor: AMD Ryzen 9 4900HS with Radeon Graphics, 3.00 GHz
• Installed RAM: 16.0 GB
• System Type: 64-bit operating system, x64-based processor

Test Conditions

• 10 threads executing 100 times each
• Total of 1000 requests executed
• Maximum test wait time: 60 seconds

---

Before Improvement

Speed Measurement

• Total request count: 1000
• Total elapsed time: 51,851 ms
• Average elapsed time: 51 ms

Query Analysis

Total of [5 + number of tags] queries executed.

1. Template Retrieval (by ID)

• 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=?

• Invocation Count: 1 time

2. Tag List Retrieval for Template (by Template ID)

• Repository: TemplateTagJpaRepository
• Method: findAllByTemplate

    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=?

• Invocation Count: 1 time

3. Tag Retrieval (by ID)

• (Derived SQL command from TemplateTagJpaRepository.findAllByTemplate)

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

• Invocation Count: 3 times (for the number of tags)

4. Source Code List Retrieval for Template (by Template ID)

• Repository: SourceCodeJpaRepository
• Method: findAllByTemplate

    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=?

• Invocation Count: 1 time

5. Member Retrieval (by Template ID)

• Repository: MemberJpaRepository
• Method: fetchByTemplateId

    SELECT
        m1_0.id,
        m1_0.created_at,
        m1_0.modified_at,
        m1_0.name,
        m1_0.password,
        m1_0.salt 
    FROM
        member m1_0 
    WHERE
        m1_0.id=?

• Invocation Count: 1 time

6. Creation of FindTemplateResponse

    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=?

• Invocation Count: 1 time

Required Improvements

Index Improvement Suggestions

• Currently, all five queries being executed automatically generate indices on primary keys and foreign keys.

Category Table

• Index Suggestion:
  • member_id
    • Reason: An index is required since the join uses category_id. This comparison is made between category.member_id and member.id during the basic template information retrieval.
    • Additional Status: Yes, CREATE INDEX idx_member_id ON category(member_id);

Query Optimization

Change in TemplateTag Retrieval Logic

Consolidate the retrieval of the template's tag list and tag information (by ID) into a single query.

@Query("""
    SELECT t  
    FROM Tag t  
    JOIN TemplateTag tt ON t.id = tt.id.templateId  
    WHERE tt.template = :template
""")  
List<Tag> findAllByTemplate(Template template);

Template Tag List Retrieval

• Repository: TemplateTagJpaRepository
• Method: findAllByTemplate

SELECT
    t1_0.id,
    t1_0.created_at,
    t1_0.modified_at,
    t1_0.name 
FROM
    tag t1_0 
JOIN
    template_tag tt1_0 
        ON t1_0.id=tt1_0.tag_id 
WHERE
    tt1_0.template_id=?

• Invocation Count: 1 time

After Improvement

Speed Measurement

• Total request count: 1000
• Total elapsed time: 40,136 ms
• Average elapsed time: 40 ms

Query Analysis

Total of 5 queries executed.

1. Template Retrieval (by ID)

• 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=?

• Invocation Count: 1 time

2. Template Tag List Retrieval (by Template ID)

• Repository: TemplateTagJpaRepository
• Method: findAllTagsByTemplate

Tag Template List Retrieval

    SELECT
        t1_0.id,
        t1_0.created_at,
        t1_0.modified_at,
        t1_0.name 
    FROM
        tag t1_0 
    JOIN
        template_tag tt1_0 
            ON t1_0.id=tt1_0.tag_id 
    WHERE
        tt1_0.template_id=?

• Invocation Count: 1 time

3. Source Code List Retrieval for Template (by Template ID)

• Repository: SourceCodeJpaRepository
• Method: findAllByTemplate

    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=?

• Invocation Count: 1 time

4. Member Response Creation

    SELECT
        m1_0.id,
        m1_0.created_at,
        m1_0.modified_at,
        m1_0.name,
        m1_0.password,
        m1_0.salt 
    FROM
        member m1_0 
    WHERE
        m1_0.id=?

• Invocation Count: 1 time

FindTemplateResponse Creation

    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=?

• Invocation Count: 1 time

---

Conclusion

• The total elapsed time improved from 51.851 seconds to 40.136 seconds.
• Average elapsed time decreased from 51 ms to 40 ms.
• This was achieved by optimizing query logic and improving the indexing strategy.