OnColumnSorting event is ex post facto.....the sort has already been done.....
SLX Faster Column Header/Caption grid sorts
Using the SLX ActiveX grid control, severe performance issues were encountered when a user clicked on a grid column header to sort the grid…..with x,000 rows in the grid.
How SLX sorts the grid:
1. Executed on the SQL Server as a SQL Select statement with an appended ORDER BY clause based on the column clicked upon and the sort glyph in the column caption header.
2. The SQL Select statement comes from the GRID.SQL.TEXT property.
3. It used the Grid’s .ConnectionString property to fetch the data into the grid’s recordset, sorted. If the connection is the SLX OLE DB Provider, then ACCOUNTID based SECCODE checks will be made for non ADMIN user row and field accessibility/visibility.
Note that this is on data that already has been provided to the user (it is IN the data grid after all) and already has been checked for user access (when the grid was loaded!). It is this duplicate round trip and duplicate SECCODE access checking that slows a simple sort down to a crawl for large recordsets.
Why do the rows disappear when the user clicks on a column header to sort?
If you are using a RecordSet to fill/refresh the data grid:
SET datagrid.Recordset = MyRS
The SQL used for the grid sort is whatever is in grid.sql.text NOT what was in MyRS.The SQL may be wrong (or not even used by you). This is why grid sort’s on extra column’s of data built by hand data grids return no rows, it’s running the bogus SQL in the grid.SQL.TEXT (SELECT CONTACTID FROM CONTACT WHERE 1 =2 “, not your XML or recordset data that you’ve so carefully crafted.
Grid.Sortable
Yes it must be set to True, otherwise not all of the data is in the grid and user selection’s of multiple grid rows is bogus.
No, it can’t be set at design time unless you have the SQL property filled in at design Time.
Yes you can set it at run time to True [grid.sortable = True] without any ‘speed’ penalty.
Grid Refreshssss.
Since 7.20 the following Grid Property changes automatically included a call to method grid.REFRESH. If you are doing this twice that’s two trips to the server, and possibly two trips to the SECCODE check timeout corner (which may be further exacerbated if you are trying to show an Account’s Parent Account….).
.BindID
.ConnectionString
.RecordSet
.SQL.TEXT
Got it? If you set any of those 4 property’s to a different value, the grid does a .REFRESH.
So
Grid.SQL.TEXT = “SELECT * FROM ADDRESS WHERE…)
Grid.REFRESH
Executes a .Refresh TWICE (two round trips to the Server, and two SECCODE table checks) and tick’s off the Users….
Enough of the primer, let’s get down to business!
Speeding up Grid Sorts.
Eoghan Byrt, Discovery Education, Evanston, IL came up with a solution to the sort issue in a 40 hour marathon of trial and error research into the very dark abyss’ of the grid control quirks.
The short answer:
1. Trap the user’s mouse click on the caption header in the grid’s mouse down event handler.
2. Sort the grid using an ADO RecordSet sort locally on the machine where the grid data is already in memory: RS.SORT “Field1 Asc, Field2 Desc….”
Refresh the grid with the sorted recordset:
SET gridcontrol.recordset = RS
The Long Answer:
1. add a Mouse down event handler to your grid on the local form (if you thought of going after the OnColumnSorting event you’d be too late, the column has already been sorted….):
'**************************************************************
' Name: dgRIGHTMouseDown
' Purpose: handle user mouse clicks on the data grid
' Assumptions: global variable rGridRS is the record set used to populate the grid.
' Effects: traps user caption header initiated grid sorts.
' Inputs: Sender is the grid control itself.
' Button is 0 for Left Mouse Button, 1 for Right Mouse Button.
' X,Y are the cell and row coordinates for the mouse down click.
' global variable rGridRS is the record set used to populate the grid.
' Returns: None.
' Dev Notes:
' ** For Developer Reference **
' Sender.GetHitInfo(X,Y).HitTYpe apparent values:
' 1 : ColumnHeader - A Column Header in the DataGrid control.
' 8 : None - The background area, visible when the control contains no table, few rows, or when a table is scrolled to its bottom.
' N/A : ColumnResize - The column border, which is the line between column headers. It can be dragged to resize a column's width.
' 7: Cell - A cell in the DataGrid control.
' ? : Caption - The caption of the DataGrid control.
' ? : ParentRows - The parent row section of the System.Windows.Forms.DataGrid control. The parent row displays information from or about the parent table of the currently displayed child table, such as the name of the parent table, column names and values of the parent record.
' ? : RowHeader - A row header in the System.Windows.Forms.DataGrid control.
' ? : RowResize - The row border, which is the line between grid row headers. It can be dragged to resize a row's height.
'**************************************************************
Sub dgRIGHTMouseDown (Sender, Button, X, Y)
Dim sortColIndex
DIM sortColHitType
' Due to uncertainty over the Sender.GetHitInfo, set On Error command to handle unpredicted situations.
On Error Resume Next
' Get index for the column clicked
sortColIndex = Sender.GetHitInfo(X,Y).Column.Index
' Get hit type. 1 is a column\caption header.
sortColHitType = Sender.GetHitInfo(X,Y).HitType
' If user clicked on Header, Set Sortable = False
CALL GridCommon_GridMouseDownEventHandler (Sender, rGridRS, sortColIndex, sortColHitType)
' Reset On Error command
On Error GoTo 0
End Sub
2. CALL GridCommon_GridMouseDownEventHandler which: Sorts the grid by recordset, ascending descending based on consecutive clicks on a column, and sort the grid based on the column clicked upon. The grid is sorted in a developer supplied rs object (which may be the original rs used to fill the grid).
Here’s the code:
'**************************************************************
' Name: GridMouseDownEventHandler
' Purpose: passed trapped mouse down on a grid event to here.
' If user clicked on a column Header, Set Sortable = False
' Assumptions: 7.22 Datagrid ActiveX control.
' Effects: handles trapped user caption header initiated grid sorts.
' Inputs: TheGrid is the grid control itself, by reference.
' TheRS is a grid to do the sorting in, by reference.
' sortColIndex is the column clicked upon by the user for sorting.
' sortColHitType is the type of grid object clicked upon by the user.
' Returns: None.
' Dev Notes: see below.
'**************************************************************
Sub GridMouseDownEventHandler (ByRef TheGrid, ByRef TheRS, ByVal sortColIndex, ByVal sortColHitType)
' ** For Developer Reference **
' Sender.GetHitInfo(X,Y).HitTYpe apparent values:
' 1 : ColumnHeader - A Column Header in the DataGrid control.
' 8 : None - The background area, visible when the control contains no table, few rows, or when a table is scrolled to its bottom.
' N/A : ColumnResize - The column border, which is the line between column headers. It can be dragged to resize a column's width.
' 7: Cell - A cell in the DataGrid control.
' ? : Caption - The caption of the DataGrid control.
' ? : ParentRows - The parent row section of the System.Windows.Forms.DataGrid control. The parent row displays information from or about the parent table of the currently displayed child table, such as the name of the parent table, column names and values of the parent record.
' ? : RowHeader - A row header in the System.Windows.Forms.DataGrid control.
' ? : RowResize - The row border, which is the line between grid row headers. It can be dragged to resize a row's height.
' Due to uncertainty over the Sender.GetHitInfo, set On Error command to handle unpredicted situations.
On Error Resume Next
' If user clicked on Header, Set Sortable = False
SELECT CASE sortColHitType
CASE 1 ' Column Header
' Call SortGridbyRS to sort the grid based on a recordset sort based on a specific column index
' SortGridbyRS (byRef theGrid, byRef theRS, byVal theSortColIndex)
SortGridbyRS TheGrid, TheRS, sortColIndex
' Call SetSortGlyph to display the Up or Down glyph on the grid column header. NOTE: SetSortGlyph sets sorting = False.
' SetSortGlyph (byRef theGrid, byVal theSortColIndex)
SetSortGlyph TheGrid, sortColIndex
Case Else
' User clicked somewhere other than the Header, Reset Sorted column first, if necessary, then Set Sortable = True for correct multi-select functionality.
' Turn sortable back on.
If TheGrid.Sortable = False Then
' Reset any previously sorted column before setting sortable = True so that grid control sort is not triggered.
' SetSortGlyph (byRef theGrid, byVal theSortColIndex)
SetSortGlyph TheGrid, 0
' Set Sortable = True so that multi-select functions correctly.
TheGrid.Sortable = True
End If
End Select
' Reset On Error command
On Error GoTo 0
End Sub
'**************************************************************
' Name: SortGridbyRS
' Purpose: Sort the data display in a Grid control by
' first sorting a recordset and then placing
' that RS into the grid.
' Assumptions: The grid column object clicked on has the current sort properties for the column to be sorted.
' Effects: Client Side memory sort of an ADO Record Set (RS.SORT), sorted on the provided Sort Column Idex.
' If previous click on this column was no sort or Descending sort, then the sort will be Ascending.
' If previous click on this column was a Ascending Sort, then the sort will be Descending.
' Inputs: by Reference theGrid - Grid control itself
' by Referehce theRS - the ADO Recordset to put into Grid
' TheSortColIndex - integer - the column to be sorted.
' Returns:
' Dev Notes: Calls the RefreshGridByRS subroutine to
' refresh the grid.
'**************************************************************
Sub SortGridbyRS(byRef theGrid, byRef theRS, byVal theSortColIndex)
' Sort the recordset based on the grid column index provided.
' Test to see if the column has already been sorted (i.e. the currently sorted column name is in the RS sort property).
' If the same column was the last sort, then reverse sort direction Asc/ Desc.
DIM sortFieldName
DIM lastRSsort
DIM sortString
DIM incomingSortableValue
' Turn sortable off if True. Record the original value so that it can be reset after the sort (if it came in as True).
If theGrid.Sortable = True Then
incomingSortableValue = True
theGrid.Sortable = False
Else
incomingSortableValue = False
End If
' Set the On Error command to handle exceptions where a required object does not exist.
On Error Resume Next
' Get the Fieldname for the Column Index provided
sortFieldName = theGrid.Columns.Item(theSortColIndex).FieldName
' Get value for previous sort (if exists), expected format: FIELDNAME Asc / FIELDNAME Desc
lastRSsort = "" & theRS.Sort
' Test if same column as previous sort?
If InStr(lastRSsort, sortFieldName) Then
' Test if previous sort was Ascending
If InStr(lastRSsort, "Asc") Then
' Set current sort to Descending
sortString = sortFieldName & " Desc"
Else
' Previous sort was Descending, set current sort to Ascending
sortString = sortFieldName & " Asc"
End If
Else
' Not the same solumn as previous sort, set current sort to Ascending
sortString = sortFieldName & " Asc"
End If
' Sort the recordset based on the sortString
theRS.Sort = sortString
' Refresh the grid so that the newly sorted data is displayed.
RefreshGridByRS theGrid, theRS
' If sortable was set to True when the sub was called, then turn it back on.
If incomingSortableValue = True Then
theGrid.Sortable = True
End If
' Reset On Error command
On Error GoTo 0
End Sub
'**************************************************************
' Name: SetSortGlyph
' Purpose: Update the grid control by setting the .Sorted
' property for a specific column in order to display
' the correct sort glyph (Ascending, Descending,
' or none).
' Assumptions: Developer will call: SetSortGlyph theGrid, 0
' before setting the grid control sortable property
' to True (in order to avoid a client-side grid
' control sort).
' Effects: - The grid control sortable property will be set to False.
' - A column in the grid will be set as "Is Sorted"
' - A sort glyph will be displayed in the grid
' control header column based on which column has
' been set to "Is Sorted".
' Inputs: the grid control
' the sorted column index - integer
' Returns:
' Dev Notes: As a column.Sorted property has been set it will
' be necessary to reset Sorted to none before
' setting the grid control sortable property = True
' in order to avoid a client-side grid control sort.
'**************************************************************
Sub SetSortGlyph (byRef theGrid, byVal theSortColIndex)
' ** For Developer Reference **
' Set the grid column headerglyph to display as sorted up or down based on the grid column index provided.
' Test to see if the column has already been sorted (i.e. the currently sorted column name is in the RS sort property).
' If the same column was the last sort, then reverse sort direction Asc/ Desc.
' ** For Developer Reference **
' DataGridColumn object Sorted property - TxDataGridColumnSort enum.
'csNone = 0
'csDown = 1
'csUp = 2
DIM lastSortColIndex
DIM lastSortDirection
' Set the On Error command to handle exceptions where a required object does not exist.
On Error Resume Next
' Reset any existing Sorted value
If Not theGrid.SortedColumn.Sorted Is Nothing Then
lastSortColIndex = theGrid.SortedColumn.Index
lastSortDirection = theGrid.SortedColumn.Sorted
' Reset existing sorted value to not sorted
theGrid.SortedColumn.Sorted = csNone
End If
' if theSortColIndex is defined then set the sort glyph accordingly
If theSortColIndex >0 Then
' Turn sortable off if True.
If theGrid.Sortable = True Then
theGrid.Sortable = False
' Else Do Nothing, Sortable is already False
End If
' Test if the last sorted column is being resorted
If lastSortColIndex = theSortColIndex Then
' Same column is being sorted again, test for sort direction. csUp = 2
If lastSortDirection = 2 Then
' Set current sort indicator to Down
theGrid.Columns.Item(theSortColIndex).Sorted = csDown
Else
' Existing sort indicator is set to Down, set sort indicator to Up
theGrid.Columns.Item(theSortColIndex).Sorted = csUp
End If
Else
' Existing sort indicator is not set or is set to Down, set sort indicator to Up
theGrid.Columns.Item(theSortColIndex).Sorted = csUp
End If
End If
' Reset On Error command
On Error GoTo 0
End Sub
Glyph’s?!
Those little icons that show up in the column caption to indicate the grid column and sort direction. You can actually see the changes to the grid based on setting the column header caption sort glyph’s, as well as sortable = true or false. Eoghan spent a ton of time on this. If the glyph’s are left ON, then when you load the record set (say by Data Grid!) this is a clue for SLX to run a SQL.Text with appended order by clause on the server….oops, that was the original problem! So you need to set the grid glyph’s off when you finish up the sort and reload the record set.
In Summary
Thanks to Eoghan’s brain storm we have a proven method to speed up grid sort’s AND sort hand built recordset grids (the one’s without valid SQL.TEXT statements).
About the Reporter:
RJ Samp is a certified SLX Developer that has been working with SLX since its inception (inaugural Partner Conference in February 1997, including golf with Pat Sullivan). He’s been working at Discovery Channel’s Discovery Education Division since May 1997. Eoghan Byrt is his boss and project manager of the conversion from SLX 6.10 to 7.22 HF 21.
RJ Samp, Wheaton IL 2010-01-28
rjsamp@comcast.net
http://www.rjsamp.com
|