List for large table loading slow due to "select count(*)" query
Browsing to a list of records within the platform generally causes two initial SQL queries to be triggered against the underlying database:
- A ‘select count(*)’ query (also known as the ‘pagination’ query) – this returns the number of records which match the users filter for display in the user interface (i.e. records 1 to [x] of [result of pagination query])
- A ‘select sys_id’ query which returns the sys_ids of rows which should be displayed in the body of the list
When browsing lists of records in large tables the ‘pagination’ query can often take a significant amount of time to execute. This translates into:
- A slow transaction (results cannot start to be loaded until the ‘pagination’ query completes)
- Degraded end user experience (the platform appears to be operating slowly)
In some cases performance of the ‘pagination’ query can be improved via use of a supporting database index however this may not be feasible due to the combinations of filters users tend to use when working with lists of records.
Steps to Reproduce
- Browse to a table with millions of rows and attempt to load a list of records
- Note that, in general, the corresponding transaction may take many seconds to minutes to complete
- Confirm that the majority of transaction processing time is spent executing the ‘pagination’ (select count(*)) via SQL debug or corresponding application node logs
To receive notifications when more information is available, subscribe to this Known Error article by clicking the Subscribe button at the top right of the article. If you are able to upgrade, review the Fixed In or Intended Fix Version fields to determine whether any versions have a planned or permanent fix.
For immediate assistance please contact our Customer Support. They will be able to assist you.
Related Problem: PRB1240287