0045702: Optimisation in query in EDL

Description

More detailed information:
https://docs.google.com/document/d/1zkFyolgmkhN3iVXfHtXmJ6eZemmfEYrHEY9fScXGkPE/edit?usp=sharing [^]

In ProcessRequest class, there exists this query. The “Or” condition is slowing down processing of “EDL Request” and CPU/mem consumption under heavy load with a lot of data in “OBEDL_Request_Line“ table.

hql = new StringBuilder();
    hql.append(" select count(*) from ").append(OBEDLRequestLine.ENTITY_NAME);
    hql.append(" where ").append(OBEDLRequestLine.PROPERTY_EDLREQUEST).append(" = :request");
    hql.append(" and (");
    hql.append(OBEDLRequestLine.PROPERTY_STATUS)
        .append(" = '")
        .append(REQUEST_STATUS_PROCESSING)
        .append("'");
    hql.append(" or ")
        .append(OBEDLRequestLine.PROPERTY_STATUS)
        .append(" = '")
        .append(STATUS_SCHEDULED)
        .append("'");
    hql.append(" )");

Steps To Reproduce

N/A

Proposed Solution

StringBuilder sql = new StringBuilder();
    sql.append("SELECT sum(CASE WHEN ")
        .append(OBEDLRequestLine.PROPERTY_STATUS)
        .append(" = '")
        .append(REQUEST_STATUS_PROCESSING)
        .append("'")
        .append(" THEN 1 ")
        .append(" WHEN ")
        .append(OBEDLRequestLine.PROPERTY_STATUS)
        .append(" = '")
        .append(STATUS_SCHEDULED)
        .append("'")
        .append(" THEN 1 ")
        .append(" ELSE 0 END) as nbvalue")
        .append(" FROM ")
        .append(OBEDLRequestLine.ENTITY_NAME)
        .append(" where ")
        .append("obedl_request_id").append(" = '").append(request.getId()).append("'");

Issue History
Date Modified	Username	Field	Change
2021-01-08 09:53	javietxe	New Issue
2021-01-08 09:53	javietxe	Assigned To	=> platform
2021-01-08 09:53	javietxe	Resolution time	=> 1611874800
2022-02-01 08:07	alostale	Assigned To	platform => Triage Platform Base

Relationships [ Relation Graph ] [ Dependency Graph ]

Notes
There are no notes attached to this issue.