Enabling server side paging on grids

With the release of version 3.2.1882.4128 of Stadium3, we have server side paging in Stadium3. It took lots of hard work to implement this and I am hoping it will be widely adopted by Stadium designers when designing data intensive applications.

What is server side paging?When displaying data for a grid, Stadium will fetch all the data from the database server and load a specific number of records depending on the value of 'pagesize' property of the grid.

Assume that a grid has 10,000 records and the page size for grid is 50. When the user clicks on Page 2, the application will fetch all the 10,000 records from the database server and then load records 51 to 100 to the grid control on the web page. This means that  we are fetching a lot of records which we really do not use. By fetching only the records that we need (in the above example, records 51 from 100) from the database server, we can gain better performance at the database server level as well as at the application level. This concept is called server side paging.

How do I enable server side paging?

Special Tags in query

There are some special tags that need be part of the SelectQuery of the grid:

• {%PagingDefaultSortColumn%}
• {%PagingStartIndex%}
• {%PagingEndIndex%}
• {%FilterSearchCondition%}

{%PagingDefaultSortColumn%} - This tag indicates which column to sort on

{%PagingStartIndex%} - This tag indicates the starting record number

{%PagingEndIndex%} - This tag indicates the ending record number

{%FilterSearchCondition%} - This tag indicates where the filter criteria will be placed.  If there is no filter it is simply replaced with '1 = 1'.

Stadium will replace these tags whenever the query is executed. These tags serve as placeholders so Stadium knows where to replace appropriate values.

New DatabasePagingDefaultSortColumn property

This is a new property on the grid.  It indicates the default sort column on the grid when it is initially displayed. This field is required for server side paging.

Query structure

The query has to be written in a specific manner,  so yes, you have to sweat it out! My simple query to return all employees changed from:

'Select * from employees'

to

'WITH EmployeeRows AS (   SELECT ROW_NUMBER() OVER (ORDER BY {%PagingDefaultSortColumn%}) AS EmployeeRowNumber, *   from       ( select e.employeeid, t.territoryid, e.firstname  FROM Employees e   left join EmployeeTerritories t ON e.employeeid = t.employeeid) AllData  where {%filtersearchcondition%} )

SELECT (SELECT Count(*) FROM EmployeeRows) AS StadiumRowCount, EmployeeID, TerritoryID, FirstName FROM EmployeeRows WHERE EmployeeRowNumber BETWEEN {%PagingStartIndex%} AND {%PagingEndIndex%} '

Stadium designers are SQL experts, so this should be easy.

Note:

• The query itself may not execute faster on database server. In some cases it maybe slower than before as sql server is doing more work now.  Speed of query execution will depend on the query and data.
• Performance and memory usage will be better on the web server as there is less data,  so if you are getting out of memory errors,  server side paging should be used.
• Less data will be transmitted over the network.
• Does not work with stored procedures.