Question
JPMC
US
Last activity: 17 Aug 2016 19:06 EDT
Performance issue with filter “Contains” on report definition.
Hi,
We are not using elastic search due to hardware restrictions, planning to use “Contains” filter(Report Definition) to search a the case(s) from the Work- table, this could result in poor performance, we are looking for a better approach.
This question is to DBAs/SMEs, are there any best design practices to improve “Contains” query on Report definition?
Thank you
"Contains" will translate to "... LIKE '%<string>%' ..." in SQL. This leads to a query that cannot make effective use of any index on the column in question. The performance may be poor if the number of records is large.
If you can use "Starts with" instead, your performance will improve significantly.
Can you provide an example of the scenario you are trying to accommodate?
JPMC
US
For example, assume that work object has a top level property 'CaseTitle' and contains the text "suspicious file content", when I search for a text "file" within the CaseTitle property, system should be able pull all the cases which match the condition.
Thank you
Pegasystems
IN
Right, there is no solution within Pega for full text search without using ElasticSearch. What hardware restrictions does your organization have which prevents you from using this feature?
JPMC
US
Hi Rajiv,
We are planning to go with Elastic Search, our concern is disk usage, could you please clarify below:
- 7.1.9 allows only whole work object indexing inclusive of embedded properties. This would mean that an equivalent representation of entire work object data would need to be stored in disk as well
- 7.2 allows selective indexing of embedded properties, this may help us reduce disk usage.
Thank you,
Srini
Pegasystems
IN
Hi Srini,
There is a small correction to your understanding.
If 7.1.9 or 7.2, the default indexing would index all the content of the work object to provide full text search. In addition to that all the top level scalar properties are also added to the index as filterable fields.
In 7.2, this feature has been extended to provide addition of certain embedded properties as filterable fields. So using this option will only add to the existing index (won't remove anything).
-Rajiv
If you need to do a contains search against a single column, the query will be LIKE "%value%". By default that would be satisfied by a table scan. If you had a DBMS index on the column in question. the query should be satisfied by scanning the index rather than scanning the table. Index scan is expensive but typically much less expensive than a table scan.
What other criteria are you using in your search? Are there additional properties that may be used to narrow down the search range?
If you have complex search criteria that may be combined at run-time (typically from a search process where user may enter one or many search criteria) the 'least worst' solution may be to create a DBMS index that fully covers all of the available searchable columns if there is no built-in scope for the search or to use a fully exposed declare index table with a when clause to create and maintain a child table limited to the sub-set of work records available to the search process. A full index scan, or a table scan on an exposed RDI table, is bad but far preferable to a full table scan on a work table.