Sheets won't Load

[etrotter]

Hello,

I have a huge application, there are tables with a billion rows in it. It is extremely slow so I have been taking steps to optimize:

-Using Auto number

-Only necessary Fields in the data model

-Using flags

- Using Apply map when possible instead of Joins

- star shaped schema

-Incremental Loads

I cannot break up the app or eliminate any historical data.

The sheets still take a long time to load, and the load time fluctuates greatly. For example for one sheet the load time will be 10 minutes at times, but can be up to an hour and a half. We have to restart the server regularly to get it back to the 10 minute load times. The strange part is that there are other apps on the servers of similar size but they don't experience these massive slowdowns.

Please let me know if you have any ideas on what could be causing this.

[tresesco]

All the optimizations you talked about are at the script level. That is good. However, if you have charts in the UI with many columns or/and complex expressions (like using aggr()...), this slowness is expected with such big application. Therefore, I would suggest you to look at the UI optimization as well.

     - if you are using too many number of columns, try to break the table into multiple

     - if your chart is using complex expressions, try to optimize it, may be by using set analysis wherever possible; or, get rid of unnecessary aggr() or so..   

[marcus_sommer]

I agree with Tresesco to simplify the UI objects and calculations as much as possible. Further calculation- and visibility-conditions on the objects/dimensions/expressions could help to reduce the waiting times.

But there could be further potential within the datamodel. A star-scheme is recommended as the best compromise to build a datamodel but it mustn't be the most performing way from a UI point of view - which is often a single table to avoid the need to create large temporary tables for the calculations.

Further helpful could be to remove formattings like date(DATE) and similar which could be much more heavy from a RAM point of view than floor(DATE) and defining them within the document properties. Also important are the distinctness of the values, see: The Importance Of Being Distinct.

- Marcus

[Peter_Cammaert · Accepted solution]

Maybe and before you touch anything, it might be a good idea to try to figure out what is different between the badly-performing document and those that do ok. Doctors also try to diagnose before applying remedies...

The fluctuations (sometimes only 10mins, sometimes more than 1 hour for a sheet to update) you are talking about lead me to believe that it may be a server issue and not so much a document issue. For example, is your server able to load / display all those documents at the same time, or do the resource usage indicators (cpu & RAM) go nuts when trying to navigate to another sheet in the problem document? Or just when that document is loaded after all others? Does the document behave if you disable the pre-loading of those other documents and try to load the problem document first?