We are facing the following issue:
Many fields in an Oracle external database we access through Connectors and Integration Studio, have leading or trailing spaces.
We need to create aggregates and use the TRIM() function in conditions for that data. This makes the queries very slow and creates performance issues.
For example, the following SQL query (which is the SQL that an aggregate creates) took 10 seconds to run on the DEV environment
SELECT NULL o0, NULL o1, NULL o2, NULL o3, NULL o4, NULL o5, NULL o6,
NULL o7, NULL o8, NULL o9, NULL o10, NULL o11, NULL o12, NULL o13,
NULL o14, NULL o15, NULL o16, NULL o17, NULL o18, NULL o19, NULL o20,
NULL o21, "ENCUSTOMER_SERVICE"."FK3_PCAT_CODE" o22,
"ENCUSTOMER_SERVICE"."FK3_PROD_SERIAL" o23, NULL o24, NULL o25, NULL
o26, "ENCUSTOMER_SERVICE"."FK5_CCIR_NUMBER" o27, NULL o28, NULL o29,
NULL o30, NULL o31, NULL o32, NULL o33, NULL o34 FROM
"CORPDB_APP"."CUSTOMER_SERVICE" "ENCUSTOMER_SERVICE" WHERE
((trim("ENCUSTOMER_SERVICE"."RECORD_STATUS")) = 'CU') AND
((trim("ENCUSTOMER_SERVICE"."SERVICE_STATUS")) = 'W') AND
((trim("ENCUSTOMER_SERVICE"."FK4_PCAT_CODE")) = 'OP') AND
(to_char("ENCUSTOMER_SERVICE"."FK4_CCIR_NUMBER") =
:qpstFK4_CCIR_NUMBER) AND
(((trim("ENCUSTOMER_SERVICE"."FK5_PCAT_CODE")) = 'TF') OR
((trim("ENCUSTOMER_SERVICE"."FK5_PCAT_CODE")) = 'IS'))
So, I'd like to ask whether there is a way to get the data from the Connectors trimmed (perhaps using Integration Studio) or use another function or utility that we can use instead of TRIM or to_char.
Regards
Marios
Hi Marios,
I will leave the question about the trim on the Integration Studio to any other community member that might have more experience with that.
However, since the main issue is about performance, have you considered the data replication and synchronization on the OutSystems side? Or this is not a hypothesis?
Regards,
PZ
Hi Paulo,
Data Replication and Sync in Outsystems is not an option. We need real-time data from the external database. Syncing frequently will add more stress to the external database.
Thanks!
Alright, it was just to understand if it was a possibility.
Then I'm sure someone will provide you with some help related to the Integration Studio.
Hi @Marios Andreas Tofarides ,
are these leading or trailing spaces consistent ? I.e. always the same for any given code ? If so, maybe it is an option to just accept them as they are while formulating your filter.
This would get rid of all the trims.
If you also want to present them to your end users (like for example a dropdown for them to pick a desired record status) maybe have a static with a trimmed and untrimmed value, showing the trimmed to the end user, and using the untrimmed in your aggregate filter.
Also, are you sure that it is the trimming that is making your queries slow ?
Dorine
Thanks, @Dorine Boudry
Although we can use the same code for specific queries, this is not feasible for all. There are queries where the value (and the length) of the trimmed field is not constant. This is a solution we have tried, but it does not apply to all cases.
We use that mostly for queries and conditions, not that much for showing data
We do believe that it is the Trim function that causes the performance degradation, especially when there is an index on any of these fields, this is not optimally used.
ok,
Do you have any influence on the original database. Surely, if they have these random spaces in their data, their own queries or other consumers of their data, also suffer from the same problem, right ?
If they can not clean their data for backward compatibility, you could ask them to add function-based indexes on trimmed values of some codes. You can then just keep using your queries with trim.
Hi Marios Andreas Tofarides,
Two suggestions in the side of the oracle database if possible to do changes.
1.Have additional fields for that tables where the values are without spaces. I For previous values a update should populate the data.
For new records you could have a trigger to populate the new field without spaces.
Only OutSystems side take into consideration these new fields.
2.Have a views for that tables which trim is used on the select to retrieve the values without spaces
On outsystems side use these views instead of tables.
Hope that it helps you
@Dorine Boudry and @Alexandre Yip We are not the DBAs, so, I'll see what I can do with your suggestions - thanks for your time and effort!
@Everyone
I was wondering whether there was a way to get the values trimmed, through Integration Studio. If anyone has a clue about that, please let me know.