> Thank you so much. I need to get back on the exact number of such queries which can hit the database. However, as 1000 of users will be there, so the possibility of all logging into the system on the same page at same time needs to be found out. Will double check on this. > > However, when you said caching :- The results on the base tables are going to be ~30-50 million. This landing page has filters on it so it may be of 30+ different
I know I read OP’s earlier descriptions to suggest that each login saw the same data. I was wrong and I suspect the suggestion to cache goes out the window.
The need for more resources now comes centre stage, right beside query tuning. You won’t get much help here on the latter problem without more DDL on the tables involved. Help on the hardware is just money - though most desktops these days are more powerful than that vert described up-thread
Won't , the materialized view having a minimum Delta refresh frequency(5-10 minutes?) help in such scenarios? As the overhead of the query complexity will lie within the materialized view and it can be indexed as per the dynamic incoming filter conditions.
