Query Performance Improvement ‐ Category List Retrieval

Data Specifications

• 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 Code: 100,000 to 500,000 records (1 to 5 randomly generated 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

• Executed with 10 threads, 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: 26638 ms
• Average elapsed time: 26 ms

Query Analysis

Total of 2 queries executed

1. Lookup Categories (by Member ID)

• Repository: CategoryJpaRepository
• Method: findAllByMemberIdOrderById

    select
        c1_0.id,
        c1_0.created_at,
        c1_0.is_default,
        c1_0.member_id,
        c1_0.modified_at,
        c1_0.name 
    from
        category c1_0 
    where
        c1_0.member_id=? 
    order by
        c1_0.id

• Number of Calls: 1

2. Lookup Members (by ID)

• Executed during CategoryJpaRepository.findAllByMemberIdOrderById

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

• Number of Calls: 1

---

Required Tasks for Improvement

Index Improvement

• All five commands currently being queried automatically generate indexes on primary keys and foreign keys.

Category Table

• Index Suggestion:
  • member_id
    • Reason: An index is needed because it is joined on category_id in the join.
    • Additional Requirement: Yes, CREATE INDEX idx_member_id ON category(member_id);

---

After Improvement

Speed Measurement

• Total request count: 1000
• Total elapsed time: 22197 ms
• Average elapsed time: 22 ms

Query Analysis

Total of 2 queries executed

1. Lookup Categories (by Member ID)

• Repository: CategoryJpaRepository
• Method: findAllByMemberIdOrderById

    select
        c1_0.id,
        c1_0.created_at,
        c1_0.is_default,
        c1_0.member_id,
        c1_0.modified_at,
        c1_0.name 
    from
        category c1_0 
    where
        c1_0.member_id=? 
    order by
        c1_0.id

• Number of Calls: 1

2. Lookup Members (by ID)

• Executed during CategoryJpaRepository.findAllByMemberIdOrderById

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

• Number of Calls: 1

---

Performance Improvement Results

Before Improvement

• Total request count: 1000
• Total elapsed time: 26638 ms
• Average elapsed time: 26 ms

After Improvement

• Total request count: 1000
• Total elapsed time: 22197 ms
• Average elapsed time: 22 ms