This is a migrated thread and some comments may be shown as answers.

Slow performance - can offset and limit be used

9 Answers 616 Views

Grid

This is a migrated thread and some comments may be shown as answers.

9 Answers, 1 is accepted

answered on 16 Feb 2016, 08:47 AM

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

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

answered on 22 Mar 2016, 08:35 AM

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

answered on 23 Mar 2016, 04:42 PM

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

Do you need help with upgrading your ASP.NET AJAX, WPF or WinForms projects? Check the Telerik API Analyzer and share your thoughts.

answered on 08 Apr 2016, 09:58 AM

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

answered on 12 Apr 2016, 10:14 AM

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

Do you need help with upgrading your ASP.NET AJAX, WPF or WinForms projects? Check the Telerik API Analyzer and share your thoughts.

answered on 12 Apr 2016, 10:23 AM

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