TotalCount takes a lot of time to compute.

[Catalin Radoi]

Hi!

So I have this grid, loading through an Url Adaptor.

The Action from Controller provides a DataTable (first page of the grid) and a TotalCount.

The data is stored in SQL. The TotalCount is necessary for the grid to display the pages for pagination, I get this, but... in sql, the count is taking a lot of time. If the DataTable takes like 1s, the Count takes 4s => the user has to wait 5s for the grid to load.

Is there a method, a trick, a best-practice, call it whatever, to load the data faster into the grid and maybe get the TotalCount later?

[Thiyagu Subramani]

Hi Catalin, 

 

Thanks for contacting Syncfusion forum. 

 

Based on your reported information your requirement is “Taking more time to compute data count using SQL”. For this case we suggest to  IQueryable data source to increase the performance. 

 

When you define the PageSize(10), On initial loading the Grid sends skip and take values along with the data request to the server. Based on the Skip and Take values, the paging operation is performed. Skip denotes that the number of records need to skipped and the Take denoted that the number of records needs to be taken from server. So based on these values you can return the required records back to the Grid. Please refer the below screenshot for more information. 

 

Screenshot: 

 

 

 

 

By default paging is depends on the grid data source’s total records count. In return Grid expects an Object with result and count as the total records count. So you have to send the datasource count along with the skip and take query. Please refer the below code example and sample for more information. 

 

By default we have option for sorting, filtering, paging , searching in server side. Please refer to the below code snippet to get those parameters.  

 

public ActionResult UrlDatasource(DataManagerRequest dm)   

{   

           IQueryable DataSource = db.C30000Records.AsQueryable();   

   

            QueryableOperation operation = new QueryableOperation();   

            if (dm.Where != null)   

            {   

                DataSource = operation.PerformFiltering((IQueryable<C30000Records>)DataSource, dm.Where, dm.Where[0].Condition);   

               if (!dm.RequiresCounts)   

                {   

                    return Json(DataSource);   

                }   

            }   

            if (dm.Search != null)   

            {   

                DataSource = operation.PerformSearching((IQueryable<C30000Records>)DataSource, dm.Search);   

            }   

            int count = DataSource.Cast<C30000Records>().Count();   

            if (dm.Sorted != null)   

            {   

                DataSource = operation.PerformSorting((IQueryable<C30000Records>)DataSource, dm.Sorted);   

            }   

            if (dm.Skip != 0)   

            {   

                DataSource = operation.PerformSkip((IQueryable<C30000Records>)DataSource, dm.Skip);   //Paging   

            }   

           if (dm.Take != 0)   

            {   

                DataSource = operation.PerformTake((IQueryable<C30000Records>)DataSource, dm.Take);   

            }   

            var countData = Json(new { result = DataSource, count = count }, JsonRequestBehavior.AllowGet);   

            var orgData = Json(DataSource, JsonRequestBehavior.AllowGet);   

            countData.MaxJsonLength = int.MaxValue;   

            orgData.MaxJsonLength = int.MaxValue;   

            return dm.RequiresCounts ? countData : orgData;   

        }   

}   

 

Please get back to us if you need further assistance.  

 

Regards,  

Thiyagu S 

[Catalin Radoi]

Hi Thiyagu,

Maybe I wasn't clear enough :)

I am already doing the pagination on the server side (I send the skip/take params). The datatable only has the rows for the current page. That's not my issue.

The issue is:

* the query for the datatable takes 1 s (15 rows) -> This is not a problem for me.

* the query for CountTotal takes 4 times more 4s (e.g. 6999 rows whatever) - THIS IS MY ISSUE. On the server side, before pagination, I also have to compute the total number.... this takes a lot. If I provide int.MaxValue, the grid will look like it has a lot of pages.... 

[Thiyagu Subramani]

Hi Catalin, 

Thanks for your update. 

By default in EJ2 Grid the pagination will be displayed based on the total data count from the server side. So, while fetching total count from dataSource it takes some time based on the total number of records and it is not related to the Syncfusion control it depends on the C# Linq method.  

In previous update we have provided solution in IQueryable which will take less time to get the total count compare to IEnumerable or if you know data count then you can define its value in manually to avoid time delay. 

Please get back to us, if you need any further assistance. 

Regards, 

Thiyagu S.