Tuesday 23 August 2011

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.

Changing the colour of visited links on grid

With release 3.2.1882.4128 of Stadium,  there is a new property 'ApplyVisitedLinkColour' on link column of datagrid control. The default value of this property is False. Set it to 'True' if you want the link to change colour once the link has been visited by user.

Please note below:
  • If this value is set to True, DataKey property on DataGrid must also be set, this is so that each row can be uniquely identified.
  • DataKey property should be the name of column, typically an ID column
  • It only changes the link colour while user remains on the same page.  If user navigates away from the page and returns to the same page,  colour of link will revert to original colour.
When should I change the colour of a visited link?

Depending on the scenario, Stadium designers will have to judiciously use this property.

Stadium3 Release : 3.2.1882.4128

Build 3.2.1882.4128 is available for download on Storytracker. It includes:
  • DataGrid has now server side paging option
  • GridIterator action now allows actions to be performed for all rows in a grid, not just the rows on active page of grid. Use this with caution, if there is lots of rows in grid, it will slow down and have an immediate effect on performance.
  • DetailsView - Formatting of data is possible with new FormatConditions property
  • RefreshControls action now includes DetailsView control
  • Tab order on detailsview, datainput and filter controls  is retained after changing values in dropdowns
  • ApplyVisitedLinkColour property on link column can be used to change colour of already viewed links
  • TreeView has new Expanded property to indicate if a node should be expanded by default or not
  • The text and value of selected node on treeview can be used in actions
  • SetControlProperties action can now be used to display or hide a panel
  • Fix for error that occurred when opening a file in a child grid
Please take note:
  • All sap files will have to be first loaded in designer to upgrade to latest format
Thanks for all the feedback, please keep them coming.