I have been investigating a performance issue in a Visualforce page, with the help from Salesforce support. After a couple of months info exchange, the issue is narrowed down to the DB query time on Tasks. Querying 239 tasks took 2.64 seconds in DB. It has more than 1 million tasks in total but this query is selective (see the following screenshot):
This is the result of the first run loading the page. Subsequent loading of the same page takes only 75 milli-seconds DB time on Tasks. According to Salesforce support,
the first run of the query is still expected to be slower than the subsequent runs, as the later takes advantage of the warm cache. This is the expected behavior.
We tried to use a Skinny table but it still didn't improve the performance of the first page load. The reason I need to squeeze the DB time as much as possible is that this page involves several SOQLs of other objects. Previously the DB time took more than 10 seconds. Such a transaction is treated as a long running process which could log users out. Although there is the method to refactor the page to lazy load as many objects as possible, that is out of this question's concern. Is this an acceptable performance we have to live with? Is there anything we missed?