Hey all, we're having an issue with the grid loading a little slow as our data grew more and more. The linq query that's being generated to pull the total record count is taking 6 seconds to pull while the query for the grid itself is only taking 1 second since it's a select top 50. Is there a way to manually set the query for total record count?
edit: Forgot to mention, we're using the older NetAdvantage jQuery 2012.1 build, haven't had time to redo the code for ignite ui.
Thanks,
Slin
Hello Slin,
Thank you for posting in our forum.
If you’re using the grid in a MVC project then when you have remote paging you need to define a DataSourceUrl from which the grid should retrieve the data. Usually in that url points to a an MVC action in which the whole data is returned and the grid builds the returned set based on the current page and
page size defined for the grid. The grid by default return the total count based on the data source returned in that action and generates a value in the response (TotalRecordsCount). In this case the grid is handling the records count in its internal logic based on the data you return in the action related to the DataSourceUrl ( the one marked with the GridDataSourceAction attribute) in which case it’s not needed to set any additional properties related to the TotalRecordCount.
Otherwise generally in the paging features definition you can set the TotalRecordsCount property manually or define a different property from the resposne to be used as the total record count using the RecordCountKey property of the Paging feature. For example:
features.Paging().PageSize(10).Type(OpType.Remote).RecordCountKey("YourKey");
There’s an example with the grid with remote operations (sorting and paging) here:
http://es.infragistics.com/products/jquery/sample/grid/remote-sorting
Are you doing something similar on your side?
I’m looking forward to your reply.
Best Regards,
Maya Kirova
Developer Support Engineer II
Infragistics, Inc.
http://es.infragistics.com/support
Hi Maya thanks for the quick reply,
I already have the datasourceurl set to an action on the controller and I have features.Paging().Type(OpType.Remote) set as well.
The problem is, is there anyway to have a different query be used for the totalRecordCount rather than have the infragistics do everything itself. The way it handles the select top pagesize works fine but the query that's being used to generate the count is taking up quite a bit of time because of all the joins it left in place and I can only imagine it will get worse as our database grows.
Generally you could remove the GridDataSourceAction attribute. By setting it on the action you enable the grid to use its own logic to query the data based on the request send from the client. If you remove it you can manually handle the remote requests and based on what is passed in the query string manipulate your data manually and return the result you want to see in the grid. For example for paging you can get the current page and the page size from the query:
int currentPageIndex = Convert.ToInt32(this.Request.QueryString["page"]);
int pageSize = Convert.ToInt32(this.Request.QueryString["pageSize"]);
And then based on that build your own Json result and pass the data you want to send as json to the client.
Here’s an example action result that would return the data for the current page and the total records count to the grid when the GridDataSourceAction attribute is removed:
[ActionName("GridGetData")]
public ActionResult GridGetData()
{
//lets assume this is the whole data source we want to bind to
List<Products> list = new List<Products>();
for (int i = 0; i < 100; i++)
list.Add(new Products() { ProductID = i, Name = "Product " + i, ListPrice = i, ModifiedDate = DateTime.Now.AddDays(i)});
}
//We'll need to manually get the data we want to pass to the client for the current page
List<Products> currentData = list.Skip(currentPageIndex).Take(pageSize).ToList();
DataModel model = new DataModel() { Records = currentData, TotalRecordsCount = 100 };
JsonResult result = new JsonResult();
result.Data = model;
result.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
return result;
Note that in this case you’ll need to set:
ResponseDataKey("Records") and RecordCountKey("TotalRecordsCount") so the grid would be able to find those properties in the response json data.
I’ve attached a sample for your reference. Let me know if you have any questios.