Query optimization of paging

Support
#1

At the moment, on browser screens pagination are only for rendering table, would be interesting also run queries in paged mode, to get best performances

#2

What queries do you mean?

You have paging abilities both in DataManager and EntityManager APIs via setFirstResult()/setMaxResults() methods.

#3

I want mean, when you have a browser screen and press search button, there is no query-level optimization. If the query returns thousands of lines all data are loaded, look follow sample:

on my database this query return 298034 rows in 6 seconds:

SELECT    *
FROM      OrdiniClientiDettaglio
WHERE     DataConsegnaPrevista >= '1980-01-01'

follow query is paginate with 20 rows for page obviously it’s speed:

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY DataConsegnaPrevista ) AS RowNum, *
          FROM      OrdiniClientiDettaglio
          WHERE     DataConsegnaPrevista >= '1980-01-01'
        ) AS RowConstrainedResult
WHERE   RowNum >= 1
    AND RowNum < 20
ORDER BY RowNum

so if you are using a DataGrid or Grouptable with pagination component, why not integrate pagination component with query RowNum sql ?

2 Likes
#4

Sorry, I still don’t understand the problem.

Standard browser screen layout includes <table> component with <rowsCount> element inside. It adds paging control on top of the table. If the datasource/data loader is connected to a <filter> component, the filter sets maxRows on the loader, and paging works as expected.