fetch a new page in table records


I have two table records. One is fed by aggregate (simple query) and the table has around 90 thousand records. It is fast to renderize the webscreen but very slow to fetch the second page of the table (if we do not apply any search with the several fields to filter the results). It takes around 20 s to fetch and renderize the table. Mind you this is in development (not quality neither production). I recall that in another app even with 10 thousand records it took 10 seconds to renderize , but that table was fed by  SQL. 

Another table records is fed by sql (advanced query) and it can has 30 thousand records. It takes some time, much more than 5 s to renderize all the records. I suppose a table records will fetch every single record with SQL, BUT with an aggregate it will only fetch the results needed for the first page. I am thinking not run the sql query at first time whenever someone enters in the webscreen and does the needed search with the several filters available. 

With such reasonable number of records is there a way to speed up the process of the fetching? With 30 thousand records being fetched with SQL, how much time is normal to get the results in the table records? 

The problem arises too when we go to the second page of the results - it takes time in both cases.

ps I only use SQL when it is not possible to do what is desired with the aggregates, otherwise it will come at cost to be slower than with the aggregate that does the same thing. 

Hi Jorge,

Aggregates/Simple queries bound to a TableRecords will only fetch the records (and attributes) needed to render the TableRecords (so with the default of a TableRecords it will fetch the first 50 records, and for those, only the attributes/columns) that are being displayed (or used in screen actions).

If you do the same thing with SQL/Advanced Query, you will have to:

  • implement the pagination mechanism yourself (it's not standard, each engine has their own keywords: TOP, LIMIT, ROWNUM);
  • only SELECT the attributes you're going to display or use on your screen actions. Create one or more structures with what you need and use them as the output parameters of your SQL.

Why the second page of your screen is so slow I can't really tell without seeing the query being used, what you're doing with the data on your screen actions...

"With 30 thousand records being fetched with SQL, how much time is normal to get the results in the table records?"... well depends on too many factors, from machine capacity, complexity of queries, size of records, network, etc... I don't think anyone can give you a good answer there