Slow performance - can offset and limit be used

[Jon]

Hi,

I've looked at a few examples of performance improvement for grids that deal with lots (160K) records.  I haven't seen anything yet that looks at using the OFFSET/LIMIT functionality that you often see with web services.  Why is that?

It strikes me that if you can do it for web services then the standard grid should be able to cope with this,  performance would be dramatically improved.

Anyway if anyone has a good example of using a radgrid with OFFSET/LIMIT interfacing to a stored procedure I'd be very grateful to get a pointer to it!

Regards

Jon

[Marin]

Hi,

Different types of services support different format of the parameters, that's why there's no universal approach to pass the same parameters and retrieve only a portion of the data.
RadClientDataSource is flexible enough to expose suitable client-side events where you can pass the necessary parameters in the preferred format by the service and have the expected result. 
This can be easily achieved using the OnCustomParameterEvent of the RadClientDataSource. Here is an example:

function OnCustomParameter(sender, args) {
                        args.get_type() // - returns the type of the request "create", "read", "update" or "destroy"
                        args.get_data() // - object containing the parameters that will be sent to the service (e.g paging parameters, filter parameters, etc)

                        //to set a new parameter you can use this code:
                        args.set_parameterFormat({ parameterName: "parameterValue" });

                    }

Using the approach you can set offset/limit parameters as required by your service.

I hope this helps.

Regards,
Marin
Telerik

[Jon]

Hi Marin,

Thanks for that.  I'm finally getting back onto that side of things. 

Before I get started with this I would like to check to see if I am looking at the best approach for this.  Given that I currently use stored procedures on a MS SQL (2008R2 migrating to 2014) database along with standard sqldatasource objects to get data out.

For most queries this is fast enough but on the tables that are growing fast I find that using the out of the box mechanism a query takes 5 seconds to get 64K records (yes I can speed that up a bit) and a total of 15 seconds to render the page (locally).  Plainly if I was just getting an limit controlled set of records (say 100) that time frame would drop to nothing.

With factors such as ease of maintenance and speed of response taken into account what would you say would be the best datasource mechanism to work with RadGrid?  

Regards

Jon

[Marin]

Hi,

Regardless of the type of databinding RadGrid will not make a direct query to the database. When the grid is using paging we make sure to pass the correct parameters to the datasource (SqlDataSource, EntityDataSource) or the web service (with RadClientDataSource any kind of web service is supported)
Then the actual datasource or web service proceeds to query the database so we do not have control over how exactly the query is created and if it will use the LIMIT and OFFSET parameters. That's left to the backend logic of the application.
So if you have a datasource control, data access layer or web service that can perform this optimized query to the database then this can be used to populate the grid and it will show the results without the need to use all records from the database. When using paging in the grid you only need to return the records for the current page in the control. For best performance in rendering we recommend that a page in the grid contains up to 50 records. With larger number of records per page the output HTML grows significantly and older browser may take more time to render the page.
Another important part of the paging setup is to ensure the grid can also access the total number of records in order for the paging to work correctly. Most datasource controls (SqlDataSource, EntityDataSource) can return the total number of records by default, so no additional setup is required there. When using custom binding to web service or data access layer you have to make sure the returned result contains this information. For example most WCF services return response in the form of JSON in the following format:

{
    data: [array of records]
    count: <total number of records>
}

This format can be correctly picked up by the RadClientDataSource and it will request only records for the current page and setup the paging automatically (when you set EnableServerPaging = "true")

If you bind the grid directly to a collection of objects in the NeedDataSource event. The best approach is to return IQueryable<> where the deferred LINQ execution will allow the grid to retrieve total row count and request records only for the current page without unnecessary overhead of processing all data from the database.

As you can see regardless of the type of databinding the grid can process data and use paging in the most optimized way, without the need to deal with large amounts of data coming from the database.

Here is a demo showing one of the approaches for such optimized databinding:
http://demos.telerik.com/aspnet-ajax/grid/examples/performance/linq/defaultcs.aspx

Another example of optimized databinding is the virtualization feature of the grid shown here: here: http://demos.telerik.com/aspnet-ajax/grid/examples/performance/virtualization/defaultcs.aspx
which also request only records for the current page and thus provides better performance and user experience.

Regards,
Marin
Telerik

[Jon]

Many thanks for the detailed response Marin.  I've had a quick read and will now look into it in more depth.  I think that it's becoming apparent that continuing to use a stored procedure may not be the best move and that I'd possibly be better off integrating the user filtering at a different level.

I think that maybe I'll try the linq approach on a simpler set of data and work up to this one once I have that mastered.

[Marin]

I'm glad that this was helpful. Do not hesitate to contact us again if you have any other questions.

Regards,
Marin
Telerik

[Jon]

Hi Marin,

Well I finally got the new system launched and after that I have upgraded SQL Server to 2014 so I now have had the chance to look into this.

I have looked into the various options and have decided to use the NeedDataSource approach.  I now have a few questions.

1) I assume that the need data source approach and the client data source approach are mutually exclusive owing to what they do?

2) I have had an issue with the filtering, one column is a checkbox column and the generated filter has True and False as the values, as it's sql server I am using a bit column so need 1 and 0 instead.  At the moment with only 1 column on my test page I cheat and use a replace function to switch them over.  Is there a better way to do this?  Worst case I suppose I can write a function to process all checkbox columns replacing when necessary.

3) I have the code below, could you give it a once over to see if you can suggest any improvements to it?

Many thanks

Jon

Private Sub uxRadGrid_NeedDataSource(sender As Object, e As GridNeedDataSourceEventArgs) Handles uxRadGrid.NeedDataSource
    Dim _connectionString As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
    Dim _sqlConnection As SqlConnection = New SqlConnection(_connectionString)
    Dim _sqlDataAdapter As SqlDataAdapter = New SqlDataAdapter
    _sqlDataAdapter.SelectCommand = New SqlCommand("ausp_COM_Companies_SelectWithPaging", _sqlConnection)
    _sqlDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure

    _sqlDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@PageNo", SqlDbType.Int))
    _sqlDataAdapter.SelectCommand.Parameters("@PageNo").Value = uxRadGrid.CurrentPageIndex + 1

    _sqlDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@RecordsPerPage", SqlDbType.Int))
    _sqlDataAdapter.SelectCommand.Parameters("@RecordsPerPage").Value = uxRadGrid.PageSize

    ' Setup the filter expression if there is one needed
    If uxRadGrid.MasterTableView.FilterExpression <> "" Then
        _sqlDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@FilterText", SqlDbType.NVarChar))
        ' TODO: This needs some work the checkbox column needs a manual fix to change the True to 1 and False to 0
        _sqlDataAdapter.SelectCommand.Parameters("@FilterText").Value = uxRadGrid.MasterTableView.FilterExpression.Replace("[Enabled] = False", "[Enabled] = 0").Replace("[Enabled] = True", "[Enabled] = 1")
    End If

    ' If there are any sort expressions then add them to the sort order string
    If uxRadGrid.MasterTableView.SortExpressions.Count > 0 Then
        Dim _sortOrderString As String = ""
        For Each _gridSortExpression As GridSortExpression In uxRadGrid.MasterTableView.SortExpressions
            _sortOrderString += _gridSortExpression.FieldName
            If _gridSortExpression.SortOrder = GridSortOrder.Descending Then _sortOrderString += " DESC"
            _sortOrderString += ","
        Next
        _sqlDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@SortOrder", SqlDbType.NVarChar))
        _sqlDataAdapter.SelectCommand.Parameters("@SortOrder").Value = _sortOrderString.Trim(","c)
    End If

    ' Now fill the datatable from the database
    Dim _dataTable As DataTable = New DataTable
    _sqlConnection.Open()
    Try
        _sqlDataAdapter.Fill(_dataTable)
    Finally
        _sqlConnection.Close()
    End Try

    Try
        ' If rows present then grab the total overall number of rows and assign to the grid
        If _dataTable.Rows.Count > 0 Then
            uxRadGrid.VirtualItemCount = CInt(_dataTable.Rows(0).Item("TotalOverallRows"))
        End If
    Catch ex As Exception

    End Try
    uxRadGrid.DataSource = _dataTable
End Sub

[Marin]

Hi,

Thank you for getting back to us.

1) Yes the NeedDataSource and the ClientDataSource are mutually exclusive approaches.

2), 3) When you have a checkbox column the grid will pass "True" and "False" by default for boolean fields. In this case you need to convert the value to a suitable one if the database requires it. 
If you are binding the grid directly to SqlDataSource or EntityDataSource the control will perform this conversion automatically so no additional code is required there. The paging, filtering and sorting will also work by default and you won't need to assign the SelectCommand.Parameters manually, the grid will take care of this.
If you prefer to call your own custom stored procedure with custom parameters - then you will need some more addition code as the one you currently have in the NeedDataSource. The drawback to this approach is that you need to manually pass the parameters and do the filter conversion, while the binding to datasource controls will perform these automatically.
You do not need to set the VirtualItemCount in the NeedDataSource - this property is only necessary if you use the virtualization feature of the grid. For standard paging you do not need to set it.

Converting the filter expression from True / False to 1/0 requires some custom code in all cases. There are different approaches to perform this conversion - but they are all pretty much equally viable, including the one you are using in the NeedDataSource event also looks fine.
Additional approaches can be found in the following help articles:
http://docs.telerik.com/devtools/aspnet-ajax/controls/grid/how-to/Filtering/custom-filter-options-with-handling
http://docs.telerik.com/devtools/aspnet-ajax/controls/grid/how-to/Filtering/custom-option-for-filtering#example2-replacing-the-filter-expression
they are all pretty similar expect for the time when exactly the replacement of the filter expression will be performed.
You can choose any of them or you can stick with the approach in the NeedDataSource - in any case, calling custom stored procedure will require some additional code to setup parameters and filter expressions.

I hope this helps. Let me know if you have any other questions.

Regards,
Marin
Telerik

[Jon]

Hi Marin,

Many thanks for the comprehensive post - very helpful.

As it is in the NeedDataSource event I think I'll just write a basic function to do the replace for me on all instances.

This is all working now although having both page level filtering and radgrid filtering does present a degree of complexity in the sql stored procedure.  It also doesn't help that when using a calculated column that column ends up needing to be in some nested select queries with the net result that they get processed on all rows, not just the 50 or so.  I think I may need to have a second look at the pages to further refine the mechanisms.

Still the end of it is in sight now :)

Thanks for the help with this.

Best Regards

Jon

 

 

[Marin]

Hi,

I'm glad to hear this was helpful. Do not hesitate to contact us again if you have any other questions.

Regards,
Marin
Telerik