My understanding is that APEX collections are used whenever a data that is repeatedly used in a page or an application , can be stored temporarily , instead of running queries repeatedly , to improve the performance.
Apparently , even APEX_COLLECTIONS is a view and any data query pointed to this view is also performance issue-prone(??) in some cases based on the query and conditions / joins used etc. even if it is a data fetch from the apex collections.
My question is: How to make best use of the apex collections without losing on its capability to retrieve the data faster?
Example: I have a basic query that fetches ten columns of the data form various base tables and there are ten regions in the page that make use of these ten columns of data in various ways by slightly changing the logic and resultant columns in some cases. The data when stored in the apex collections has around 7 lakh records. This page takes around 2 minutes to load. If I had used the original query (that was used for temporarily storing the data in the apex collections ) with modifications needed for each region's report, would it have been faster to load? Or, since I used apex collections, it is expected to be faster than just using the query repeatedly (with most of the query being the same). Anyway final data displayed is a fetch from the database, be it using collections or using a query that doesn't involve collection.
Please advise me or give me some pointers or guidelines.