We are having a code consisting of 3 Apex Collections. 1st and 2nd are working fine. I am having an inner join between 1st and 3rd. There is an issue with the 3rd collection.
All these collections are created using data from a remote SQL SERVER using DB link. This program is taking more than an hour to execute.
So we created a Materialized view and Indexed it. So now the data is locally (oracle database) so the data is not coming from SQL Server. The performance is slightly better but still taking about 45 minutes to execute the code.
Now what I did was instead of Materialized view, I created a table and indexed the fields that I need. The performance came down to about 30 minutes.
My question over here is what else can be done to improve the performance of my apex collections?