Query Performance Improvement Template Deletion

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 Code: 100,000 to 500,000 entries (1 to 5 random 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

---

Testing Conditions

• Executed with 10 threads for 100 iterations each
• Total of 1000 requests executed
• Maximum test wait time is 60 seconds

---

Before Optimization

Speed Measurement

• Total request count: 1000
• Total elapsed time: 89103 ms
• Average elapsed time: 89 ms

Query Analysis

• Total queries executed: [8 + (number of tags * 2) + (number of source codes)]

1. Retrieve Member Information (by Name)

• Repository: MemberJpaRepository
• Method: fetchByName

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

• Number of Calls: 1 time

2. Retrieve Thumbnail Information of Template (by Template ID)

• Method: Called from ThumbnailJpaRepository.deleteByTemplateId

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

• Number of Calls: 1 time

3. Retrieve Source Code List of Template (by ID)

• Method: Called from ThumbnailJpaRepository.deleteByTemplateId

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

• Number of Calls: 1 time

4. Retrieve Template Information

• Method: Called from ThumbnailJpaRepository.deleteByTemplateId

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

• Number of Calls: 1 time

5. Retrieve Source Code List of Template (by Template ID)

• Repository: SourceCodeJpaRepository
• Method: deleteByTemplateId

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

• Number of Calls: 1 time

6. Retrieve Tag Information of Template (by Template ID)

• Repository: TemplateTagJpaRepository
• 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=?

• Number of Calls: 1 time

7. Retrieve Tag Information (by ID)

• Repository: TemplateTagJpaRepository
• Method: deleteAllByTemplateId

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

• Number of Calls: 5 times (for each tag)

8. Delete Thumbnail

• Repository: ThumbnailJpaRepository
• Method: deleteByTemplateId

    DELETE 
    FROM
        thumbnail 
    WHERE
        id=?

• Number of Calls: 1 time

9. Delete Source Code

• Repository: SourceCodeJpaRepository
• Method: deleteById

    DELETE 
    FROM
        source_code 
    WHERE
        id=?

• Number of Calls: 1 time (for each source code)

10. Delete Template Tag

• Repository: TemplateTagJpaRepository
• Method: deleteAllByTemplateId

    DELETE 
    FROM
        template_tag 
    WHERE
        tag_id=? 
        AND template_id=?

• Number of Calls: 5 times (for each tag)

11. Delete Template

• Repository: TemplateJpaRepository
• Method: deleteById

    DELETE 
    FROM
        template 
    WHERE
        id=?

• Number of Calls: 1 time

---

Actions Required for Improvement

Query Optimization

Improve Delete Queries Using @Modifying

Currently, the elements of templates are being deleted by executing deleteByTemplateId multiple times. This should be changed to a single query that deletes all at once.

Using @Modifying(clearAutomatically = true)

The @Modifying annotation can inform Spring Data JPA that the method will modify data in the database.

• There could be issues with persistence context and data inconsistency.
  • Additional settings may be needed to clear the persistence context.
  • Using the attribute **@Modifying(clearAutomatically=true)** will automatically initialize the persistence context after changes.
• Used for bulk operations.
  • Bulk operations process large amounts of data at once during UPDATE or DELETE in the database.

Reference: What is @Modifying?

---

Deleting Thumbnails

@Modifying(clearAutomatically = true)
@Query("DELETE FROM Thumbnail t WHERE t.template.id IN :templateIds")
void deleteByTemplateIds(List<Long> templateIds);

Deleting Thumbnails (by Template ID)

• Repository: ThumbnailJpaRepository
• Method: deleteByTemplateIds

DELETE t1_0 
FROM
    thumbnail t1_0 
WHERE
    t1_0.template_id IN (?)

---

Deleting Source Code

@Modifying(clearAutomatically = true)  
@Query("DELETE FROM SourceCode s WHERE s.template.id IN :templateIds")  
void deleteByTemplateIds(List<Long> templateIds);

Deleting Source Code (by Template ID)

• Repository: SourceCodeJpaRepository
• Method: deleteByTemplateIds

DELETE sc1_0 
FROM
    source_code sc1_0 
WHERE
    sc1_0.template_id IN (?)

---

Deleting Template Tags

@Modifying(clearAutomatically = true)  
@Query("DELETE FROM TemplateTag t WHERE t.template.id IN :templateIds")  
void deleteAllByTemplateIds(List<Long> templateIds);

Deleting Template Tags (by Template ID)

• Repository: TemplateTagJpaRepository
• Method: deleteByTemplateIds

DELETE tt1_0 
FROM
    template_tag tt1_0 
WHERE
    tt1_0.template_id IN (?)

---

After Optimization

• Total queries executed: 7

Speed Measurement

• Total request count: 1000
• Total elapsed time: 51689 ms
• Average elapsed time: 51 ms

Query Analysis

1. Retrieve Member Information (by Name)

• Repository: MemberJpaRepository
• Method: fetchByName

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

• Number of Calls: 1 time

2. Delete Thumbnails

• Repository: ThumbnailJpaRepository
• Method: deleteByTemplateIds

    DELETE 
    FROM
        thumbnail 
    WHERE
        template_id IN (?)

• Number of Calls: 1 time

3. Delete Source Codes

• Repository: SourceCodeJpaRepository
• Method: deleteByTemplateIds

    DELETE 
    FROM
        source_code 
    WHERE
        template_id IN (?)

• Number of Calls: 1 time

4. Delete Template Tags

• Repository: TemplateTagJpaRepository
• Method: deleteByTemplateIds

    DELETE 
    FROM
        template_tag 
    WHERE
        template_id IN (?)

• Number of Calls: 1 time

5. Delete Template

• Repository: TemplateJpaRepository
• Method: deleteById

    DELETE 
    FROM
        template 
    WHERE
        id=?

• Number of Calls: 1 time

---

Summary

• Elapsed time improved: 37,414 ms (from 89,103 ms to 51,689 ms)
• Queries reduced: 4 queries (from 11 to 7)
• Average elapsed time improvement: 38 ms

Through optimization, we significantly reduced the total time taken to execute the delete requests, as well as decreased the number of queries executed against the database. This leads to more efficient data handling and improved performance overall.