Hi all,
We have some read only custom fields that takes some time to calculate (contains the listagg command). That is no problem when receiving a small number of rows.
But when we are searching in the field it takes a long time. Like the one below:
Anyone know a way to speed this this up?
This is the drawback of read only custom fields. You can’t do much more than to optimize the query but you can’t get good performance since you are searching on the result from the custom field method.
As a work around you can create an advanced saved query with an EXISTS or IN statement where you search for the specific value you enter and you use the SQL you have in the custom filed but not with listagg since you don’t need it. By this you can get better performance but it will not be as flexible as just searching for the value in the field.
Thanks for the suggestions,
Unfortunate, we are using this for RMB navigations:
Both read only cf-fields contain the same values. The problem is the one highlighted. It have to be calculated for all rows for every navigation.
For now, I have solved it by making that field persistent instead of read only. At regular intervals I sync the 2 fields.
Not a good solution, but the initial solution was bad and had to be replaced.
Have you tried using an Advanced Search with the INSTR function? It worked pretty well for a test I set up.
instr(UPPER(CF$_LIST_OF_PARTS),UPPER('&SEARCHPART'),1,1) > 0
It is working fine with few data. But this is want I get with only 8000 rows:
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.