Loading...

XML

Word

Printable

The selectExternalTasksForTopics query uses the LIKE instead of the = operator for the topic name query criterion [1]

The LIKE operator is meant to be used in conjunction with placeholders like % or _ whereas the = operator only checks the equality of two strings
From a product perspective, there is no reason to use a LIKE instead of the = operator in this specific query; the topic name criterion was initially designed to check for equality
1. This is a regression which was introduced with ~~CAM-8722~~; see the previous implementation => [2]
2. When using a placeholder in the topic name filter, a NullPointerException occurs since the fetch instruction cannot be correlated against queried external tasks [3]
This is a bug since an external task topic cannot contain reserved wildcard characters

The query consumes a high amount of CPU time, which blocks the execution of other queries.

Since LIKE compares against a parametrized value, the query optimizer does not know if the parameter contains wildcards or not
The parameter's value could contain a wildcard at the beginning of the string which is non-"Search ARGumentable Query" (SARGabale), which means the database cannot specifically traverse the index but has to read each row and compare it with the predicate
This leads to a high amount of reads (basically all table rows need to be scanned) and a high amount of CPU time the query claims

Changing the LIKE operator to = reduces the reads by ~ 97 % and the CPU time goes down from 26 ms to 1 ms (please find attached the query plans)
- => = operator
- => LIKE operator

External task "Fetch and Lock" query uses LIKE instead of = operator for the topic name