Turbo filtering performance using standard paging on large recordsets

6 posts, 0 answers
  1. Patrick
    Patrick avatar
    4 posts
    Member since:
    Nov 2015

    Posted 03 Nov 2015 Link to this post

    I have devised a way perform several things I'm sure that many Telerik developers have wanted to be able to do, and seperately there are plenty of posts around these forums that assist in one or the other.  I've tried to use many of them myself, but ended up with a completely different solution, which I will descript how to do below:

     

    I have a web application with dozens of screens, with dozens of grids on them, each with dozens of fields that require filtering.  Some of these screens return thousands of rows, and I was relying on client side filtering to make the web app easier to find what the user wanted.

     

    I saw posts on how to use custom paging, but if you modify your code to return, say, only 50 rows per page and you are performing the paging in your query/stored procedure based on page number * page size, the client side filtering will only work on those 50 rows returned - even if there were matches on other pages that did not get returned in your result set.

     

    Also, I wanted to persist my filters across pages, so that if I left that page and then later returned to it, my filters would still be intact and displayed.  I could not get any of the samples I found using google to work.

    With persistent filtering in place, using standard paging so I could filter across my entire dataset (not just the set of pages that were returned via custom paging), with another small tweak I have been able to 'Turbo Charge' the performance of many of my screens.  I was able to take a screen that took 20 seconds to load and reduce it down to about 2 seconds.  The initial load (with no filtering in place) still takes 20 seconds, but once a filter is in place, it screams.

    I will detail how this can be done as best I can as this is my first post to these forums.  I'm sure there are probably better ways to achieve what I have, but I'll post how I did it here anyway so any other people who want to do the same will have a framework to start from and improve upon.

    The main 'trick' I used to boost the performance across an entire dataset was to pass a slightly modified form of the combined radgrid filterexpression to my sql stored procedure and make it part of my where clause, so that the procedure itself would tie it's results directly to the filters the user wanted and only return rows that matched the filters, which results in a HUGE performance boost when you are dealing with large recordsets because the filtering was done on the server side instead of the client side.

    To achieve truly persistent filtering, first, turn off linq expressions in the page load sub and perform your first save and load of the persisted filters.  Note for multiple different pages you are implementing this on, the UniqueKeyForThisPageAndGrid will need to be, of course, unique.

    <P>SessionStorageProvider.StorageProviderKey = "UniqueKeyForThisPageAndGrid"<BR>
           If Session("ProActSuppNegotiation") Is Nothing Then
    RadPersistenceManager1.SaveState()<BR>       
    RadPersistenceManager1.LoadState()</P>
    <P><BR>RadGridBottom.EnableLinqExpressions = False</P>

    Set up the session storage provider in your code behind page:

    <P>Imports Telerik.Web.UI.PersistenceFramework</P>
    <P>Private Sub [yourpage]_Init(ByVal sender As Object, ByVal e As
    System.EventArgs) Handles Me.Init<BR>   
    RadPersistenceManager1.StorageProvider = New SessionStorageProvider()<BR>End
    Sub</P>


    Add this new sub in your page (make sure to use your unique column names, and your radgrid name)

    private sub SetFiltersForSql()<BR>   if not ispostback
    then<BR>      Dim ac As
    GridColumn<BR>      Dim fv As
    String<BR>      Dim newFE As String =
    ""<BR>      ac =
    yourgrid.MasterTableView.GetColumnSafe("ContractNo")<BR>     
    ac.CurrentFilterFunction =
    GridKnownFunction.Contains<BR>      fv =
    ac.CurrentFilterValue<BR>      If Trim(fv) <> ""
    Then newFE = "([ContractNo] LIKE '%" + fv +
    "%')"<BR>      ac =
    yourgrid.MasterTableView.GetColumnSafe("SupplierName")<BR>     
    ac.CurrentFilterFunction =
    GridKnownFunction.Contains<BR>      fv =
    ac.CurrentFilterValue<BR>      If Trim(fv) <> ""
    Then newFE = newFE + "([SupplierName] LIKE '%" + fv +
    "%')"<BR>      ac =
    yourgrid.MasterTableView.GetColumnSafe("StartDate")<BR>     
    ac.CurrentFilterFunction =
    GridKnownFunction.Contains<BR>      fv =
    ac.CurrentFilterValue<BR>      If Trim(fv) <> ""
    Then newFE = newFE + "([StartDate] LIKE '%" + fv +
    "%'<BR>      'etc etc...for each filtered column you
    want to use.<BR><BR>      newFE = Replace(newFE,
    "')([", "') AND ([")<BR>     
    RadGridBottom.MasterTableView.FilterExpression =
    newFE<BR>      'Now that the filter expression is
    stored in the radgrid the way radgrid wants it, we have to tweak it a little bit
    so it will also work as a passed parameter to our sql stored
    procedure:<BR>      'We need to get rid of the open and
    close parenthesis.<BR>      'We need to plug
    'placeholders' in for the quoted left and right side of each filter
    expression.<BR>      '(plugging in the placeholders is
    needed so we can properly quote the actual filter expressions without messing
    with the sql syntax required for it to work as a part of a where clause. 
    The stored procedure will handle returning the placeholders back to
    quotes.)<BR>      newFE = Replace(newFE, "(",
    "")<BR>      newFE = Replace(newFE, ")",
    "")<BR>      newFE = Replace(newFE, "'%",
    "|%")<BR>      newFE = Replace(newFE, "%'",
    "%|")<BR>      newFE =
    FixSql(newFE)<BR>      If newFE Is Nothing
    Then<BR>         newFE =
    "*"<BR>      End If<BR>     
    'set our correctly formed sql expression into the session.  this will
    become part of a where clause.<BR>     
    Session("psnsql") = newFE<BR>   end if<BR>end sub


    In the radgrid_DataBinding function, collect the filters as follows:
    <BR>Private Sub yourgrid_DataBinding(ByVal sender As Object, ByVal e As
    System.EventArgs) Handles RadGridTop.DataBinding<BR>    If Not
    IsPostBack Then<BR>       
    SetFiltersForSql()<BR>    End If<BR>End Sub


    Now, in the radgrid ItemCommand sub, check for filter operations and save the state of the filters and such.  Keep in mind, there is a unique key in the code below that you must set for each and every different grid, on each and every different page you want to persist seperately.

    Private Sub yourgrid_ItemCommand(ByVal sender As Object, ByVal e As
    Telerik.Web.UI.GridCommandEventArgs) Handles
    RadGridBottom.ItemCommand<BR>    If e.CommandName =
    RadGrid.FilterCommandName Or e.CommandName = "ChangePageSize" Or e.CommandName =
    "Sort" Or e.CommandName = "Page"
    Then<BR>       
    SessionStorageProvider.StorageProviderKey =
    "UniqueKeyForThisPageAndGrid"<BR>       
    RadPersistenceManager1.SaveState()<BR>       
    SetFiltersForSql()<BR>       
    yourgrid.Rebind()<BR>    End If<BR>End Sub


    To Retain the saved Page number you were on, this function is needed (and called from the page load).  If you have multiple grids on the same page, use this function to check and set each one.  Remember to use your page name and your grid name followed by "PageIndex", not the hardcoded text below:

    Private Sub SetPageNumber()<BR>    If
    Session("PageNameGridNamePageIndex") = "" Then
    Session("PageNameGridNamePageIndex") = "0"<BR>   
    yourgrid.CurrentPageIndex = CInt(Session("PageNameGridNamePageIndex"))<BR>End
    Sub

    You will also need to implement these:

    Private Sub RadGridBottom_PageIndexChanged(ByVal sender As Object, ByVal e As
    Telerik.Web.UI.GridPageChangedEventArgs) Handles
    RadGridBottom.PageIndexChanged<BR>    Session("PageNameGridNamePageIndex") =
    e.NewPageIndex.ToString()<BR>   
    SessionStorageProvider.StorageProviderKey =
    "UniqueKeyForThisPageAndGrid"<BR>   
    RadPersistenceManager1.SaveState()<BR>End Sub<BR><BR>Private Sub
    RadGridBottom_PageSizeChanged(ByVal sender As Object, ByVal e As
    Telerik.Web.UI.GridPageSizeChangedEventArgs) Handles
    RadGridBottom.PageSizeChanged<BR>    If
    Session("PageNameGridNamePageSize") = e.NewPageSize
    Then<BR>        e.Canceled = True 'cancel
    bubbled event so page variable doesn't get reset<BR>   
    Else<BR>       
    Session("PageNameGridNamePageSize") =
    e.NewPageSize<BR>       
    Session("PageNameGridNamePageIndex"))= "0" 'must reset since old page # won't be
    same as new page # on different page size.<BR>    End
    If<BR>    SessionStorageProvider.StorageProviderKey =
    "UniqueKeyForThisPageAndGrid"<BR>   
    RadPersistenceManager1.SaveState()<BR>    End Sub

    Now, you need to ensure that the datasource your grid is using allows for the special sql expression as a parameter.  In this example code, I have used session variables so that it also persists across pages.

    In your asp page, add the radpersistencemanager for your grid(s):

    <telerik:RadPersistenceManager ID="RadPersistenceManager1" runat="server"
    ><BR>   
    <PersistenceSettings><BR>       
    <telerik:PersistenceSetting ControlID="yourgrid" /><BR>   
    </PersistenceSettings><BR></telerik:RadPersistenceManager>

    In your asp page for the <ASP:SqlDataSourceID> being used by your radgrid, add:

    <asp:SessionParameter Name="clause" SessionField="psnsql" Type="String" />

    Also Add

    "@clause varchar(1024)"
    as a parameter to your stored procedure.

    And finally, the stored procedure will get some rework done to it.  You can't pass a piece of a where clause to a procedure and expect it to work.  What you need to do is convert your sql code to a large string, then add the passed in piece of the where clause to it, and execute the string. There are a couple things to do to the procedure first.  Inside your stored procedure, add these lines above your select statement:

    These lines declare a needed variable and replace the parameters in the string with left and right quotes to surround your LIKE clauses.

    Declare @sql varchar(4096)<BR>set @clause = replace(@clause, '|%', '''%')<BR>set
    @clause = replace(@clause, '%|', '%''')

    Next, since your database schema table fields may not be named exactly like the UniqueID's you specified in your radgrid, we need to replace them with their actual table field counterparts (remember to use the table qualifiers like a. and b. etc if needed by your actual query)

    select @clause = replace(@clause, '[ContractNo]', 'a.contractno')<BR>select
    @clause = replace(@clause, '[SupplierName]', 'b.Company')<BR>select @clause =
    replace(@clause, '[StartDate]', 'a.startdate')

    Now, you need to convert your original query into a string.  Take care to modify any parameters that were passed in to still be used properly by building them into the string similarly as we do below with our new @clause variable.

    set @sql='Select a.CID, a.ContractNo, a.SupplierNo, b.Company as SupplierName,
    '<BR>set @sql = @sql + 'from CMS_Contracts a '<BR>set @sql = @sql + 'join
    vendors b on a.SupplierNo = b.VendNo ' + <BR>set @sql = @sql + 'where
    a.supplierno = b.vendno '

    --and now add your passed in piece of the where clause and execute the code:
    if @clause <> '*' and isnull(@clause, '') <> '' set @sql = @sql + '
    and ' + @clause<BR>exec (@sql)

    And that's it.  I know this mish mash of code in this post might be a bit much to chew on, but I wanted to share my success with persistent filtering and turbo filtered results on large datasets.  Feel free to try and incorporate what I have been able to achieve in your own code.  Even if you choose NOT to use the sql modifications here, this is a really neat way to persist all the filtered columns across pages.

    - Patrick

  2. Patrick
    Patrick avatar
    4 posts
    Member since:
    Nov 2015

    Posted 03 Nov 2015 in reply to Patrick Link to this post

    I apologize for the code snippets being all out of whack.  It's my first time posting here and I tried to use the formatting tool on site...didn't work very well...

    Perhaps a site admin can fix them.

  3. Patrick
    Patrick avatar
    4 posts
    Member since:
    Nov 2015

    Posted 03 Nov 2015 in reply to Patrick Link to this post

    Almost forgot.  One additional class is needed for the persistence framework:

     

    Imports Telerik.Web.UI.PersistenceFramework

    Public Class SessionStorageProvider
    Implements IStateStorageProvider
    Private session As System.Web.SessionState.HttpSessionState = HttpContext.Current.Session
    Shared storageKey As String

    Public Shared WriteOnly Property StorageProviderKey() As String
    Set(ByVal value As String)
         storageKey = value
    End Set
    End Property

    Public Sub SaveStateToStorage(ByVal key As String, ByVal serializedState As String) Implements IStateStorageProvider.SaveStateToStorage
         session(storageKey) = serializedState
    End Sub

    Public Function LoadStateFromStorage(ByVal key As String) As String Implements IStateStorageProvider.LoadStateFromStorage
         Return session(storageKey).ToString()
    End Function

    End Class

  4. Kostadin
    Admin
    Kostadin avatar
    1733 posts

    Posted 06 Nov 2015 Link to this post

    Hi Patrick,

    Thank you for sharing your approach to optimize the grid performance when filtering is used. I would appreciate if you cam create a runnable sample and upload it as a code library in Grid section. You can use Telerik, Northwind or any other database that you prefer. As a token of gratitude for your efforts we will update your Telerik points accordingly.

    Regards,
    Kostadin
    Telerik
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
  5. Patrick
    Patrick avatar
    4 posts
    Member since:
    Nov 2015

    Posted 06 Nov 2015 in reply to Kostadin Link to this post

    If you could do me a favor, delete this entire thread and I will recreate it in a better format, with the full functions listed and not all jumbled up with the <BR>s that got embedded.  I will also create the sample project as you suggested and repost sometime next week.

     

    Thanks!

  6. Kostadin
    Admin
    Kostadin avatar
    1733 posts

    Posted 11 Nov 2015 Link to this post

    Hi Patrick,

    Sounds great. I am looking forward to check you sample.

    Regards,
    Kostadin
    Telerik
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
Back to Top