How to correctly use the datatable to display a large amount of data?

Question

In apex, we get the data of the object through SOQL, about 10,000 records, and we need to display all of these records on the front end (LWC).

I've tried three solutions:

1. Query all the data through SOQL, and then perform paging display in front end (LWC).

   SELECT ID,NAME FROM ACCOUNT WHERE ……

   Problems faced: Trigger CPU TIME OUT or wait too long.

2. Perform paging query using OFFSET.

   Problems faced: The maximum 2000 limit is triggered.

3. Search in batches according to search conditions

   Problems faced: Users don't accept this solution.

Are there other solutions to implement paging queries?

Answer 1

There is a way to do it through the 2nd approach, but not with offset, here is what I suggest:

• add order by id to your first request, retrieve the first batch of 2000 records (for example), store the last id retrieved
• perform the second soql query not only with order by id and limit 2000, but also with where-clause condition where id > 'last_retrieved_id'

This way you'll be able to retrieve all records just like with offset mechanism and separate them in different transactions (for example, with the help of @RestResource) to avoid CPU TIME OUT.

Example:

SELECT Id FROM User ORDER BY Id LIMIT 10
// retrieved 0051I000000RwQCQA0 ... 0051I000000S2xKQAS
SELECT Id FROM User WHERE Id > '0051I000000S2xKQAS' ORDER BY Id LIMIT 10
// and so on