Performance issue with filter “Contains” on report definition.
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?
"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?
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.
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).
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.