[Solved] Paging very slow

[Gerard Eikelboom]

Hi ,
In our grid we load our data via an AJAX call.
At the moment this is a collection of aprox 2300 rows.
When I set my PageSize to 50 it takes 2-3 seconds to go from 1 page 2 the next page.
When I set my gridsize to 200 it takes 7 seconds to go from 1 page 2 the other page.
And this is local testing.

I tested it also on a test server.
The grid has 182 rows.
When I set the pagesize to 100. It takes 2-3 sec to go from page 1 to page 2.
That is very slow, isn't it?

Can we improve this speed?

Regards,
Gerard

[Atanas Korchev]

Hello Gerard Eikelboom,

Which browser are you using? FireFox and Chrome do not work as expected on localhost due to a well known issue with IPv6 DNS resolution. If this is not the case please send us your test project so we can try it locally.

Regards,
Atanas Korchev
the Telerik team

[Gerard Eikelboom]

Hi Atanas,
I tested it locally on IE8 pageSize 200 takes aprox 7 seconds
                             FF pagesize 200 takes aprox 3 seconds. is faster but still to slow for the amount af data I think.
I will prepare a test project and come back to you.
Is there a way you can keep paging client side instead of going back to the (SQL) server to load the data everytime you klick on another page?

Regards,
Gerard

[Atanas Korchev]

Hi Gerard Eikelboom,

 No, currently the grid requests only one page of data. Client-side paging requires all data to be loaded in one request which we currently not do.

 We are looking forward to try the sample project.

Regards,
Atanas Korchev
the Telerik team

[Gerard Eikelboom]

Hi Atanas,
I am back. And this time I have a nice MVC2 sample project with a grid in it.
This Grid as it is we use in our project.
I have 2300 records in it and paging is very slow.
If you can take a look at it
You can start it up in VS2010 The data is put at the data layer.
There is no difference if we test it locally with this project or our real project. It takes approx 7 secs to page from 1 to another page.
It is enclosed.

Thanks in advance,

Gerard Eikelboom

[Georgi Krustev]

Hello Gerard Eikelboom,

 
Thank you for the attached sample project.

After I removed this event handler

.ClientEvents(events => events.OnRowDataBound("onRowDataBound"))

paging was significantly faster.

I believe that this performance issue is related with the code which is executed when RowDataBound event is fired. I will suggest you to revise the code in the event handler.

Regards,
Georgi Krustev
the Telerik team

[Gerard Eikelboom]

Hello Georgi,
Let me respond to your suggestion.
Indeed, if i do the test and command that event (onRowDataBound) out it is faster (still 2 sec).
In that event we add features wich we want to see in the grid (eg for amount we put an € sign)
color column we set an image with the color.
When I go over a certain column there is an mouse over event for that column.
Is there a way we can do that in a pre stage?

Otherwise i don't see how to refactor this,

Regards,
Gerard

[Atanas Korchev]

Hello Gerard Eikelboom,

As Georgi said earlier the code in the OnRowDataBound handler affects performance the most. Any update of the table cell (via innerHTML) is causing table layout recalculation which is very slow especially in Internet Explorer. As a performance improvement I suggest you use ClientTemplates. They will be executed all at once and performance will improve. By checking the OnRowDataBound handler I think you can convert all the code to ClientTemplates.

The next thing I can suggest is to try with lower page size.

I am attaching the modified project showing how to use the format in order to apply the euro sign (Format("{0:c}"). In order to enable this I upgraded the project to the current official version and set the Culture to "nl-NL".

I also put some timing code which calculates the time between OnDataBinding/OnDataBound events which is the actual time taken for databinding. The rest of the delay comes from the browser rendering.

Regards,

Atanas Korchev
the Telerik team

[IT]

I'm experiencing something similar with pretty much all grid operations.

Grids with a few rows are OK, however any more than a few hundred rows and things become impossible.

I've removed the reference to OnRowDataBound and it didn't make any difference... I have a table with 500 rows and it takes ~10 seconds to sort, page or reload. Page size is 10.

I've tried remove all the columns but a single Id column... makes no difference.

Consider this code for the AJAX bind...

[GridAction]
public ActionResult AjaxSelectLine(int id)
{
    Log.Write();

    GridModel gridModel = new GridModel(GetAllLines(id));

    Log.Write("after gridmodel");

    return View(gridModel);
}

All the delay happens at the "return View..."  point... getting the data and creating the GridModel happens quickly.

The delay seems to be within GridModel, and seems to be directly related to the size of the dataset... not in terms of number of rows, but number of columns/relationships... if I limit my source data to a single column, it all works quickly, no matter how many rows I'm using. As soon as I switch back to the full table (with related objects) it slows down. Changing the page size to 100 instead of 10 has no impact on load time.

Reviewing what is coming back through AJAX, I'm only getting the JSON for the 10 rows in my page (which I expect) and that is only 24kb; however within GridModel, every row of each table related to the entire object tree is being interrogated (determined by reviewing OA debug)...

Telerik.OpenAccess Information: 25360 : driver.rs.next             [43, DBNull, DBNull, DBNull, DBNull, DBNull, 0, 0, 4, -4, 7.4358, 0.0000, 0.0000, 29.7432, -29.7432, 2011-11-09T09:52:01.0330000, 2011-11-09T09:52:01.0330000, DBNull]

I get 5726 "driver.rs.next" messages similar to the above (I assume this is walking the recordset?).. this is for a data source of only 486 rows, only 10 of which are ever sent back to the browser. I don't have any aggregates enabled (though even if I did, I would expect it shouldn't need to walk every row to calculate the results)

If GridModel is aware of paging, why does it need to read all the data when it's only returning 10 rows?

Where can I look to further isolate where the delays are occurring?

[Atanas Korchev]

Hi Aleks,

What does GetAllLines return? If this is an IQueryable the grid will create such an expression that only a single page of data will be retrieved from the database. If it is an IEnumerable then all records would be retrieved. In such cases you should probably try custom binding.

Greetings,
Atanas Korchev
the Telerik team

[IT]

It returns IQueryable....

protected IQueryable<Lima.Models.Inventory.StocktakeLineModel> GetAllLines(int id)
{
    Meta2.Log.Write();

    // TODO - need to do something here, grid sort performance is AWFUL

    Telerik.OpenAccess.FetchOptimization.FetchStrategy strategy = new Telerik.OpenAccess.FetchOptimization.FetchStrategy();

    strategy.LoadWith<Meta2.Inventory.StocktakeLine>(o => o.Product);
    strategy.LoadWith<Meta2.Inventory.StocktakeLine>(o => o.Scans);
    strategy.LoadWith<Meta2.Inventory.StocktakeLine>(o => o.Stocktake);

    strategy.LoadWith<Meta2.Catalogue.Style>(o => o.Category);
    strategy.LoadWith<Meta2.Catalogue.Style>(o => o.Group);
    strategy.LoadWith<Meta2.Catalogue.Style>(o => o.Images);
    strategy.LoadWith<Meta2.Catalogue.Style>(o => o.Supplier);

    strategy.LoadWith<Meta2.Catalogue.Product>(o => o.Barcodes);
    strategy.LoadWith<Meta2.Catalogue.Product>(o => o.Style);
    strategy.LoadWith<Meta2.Catalogue.Product>(o => o.Range);
    strategy.LoadWith<Meta2.Catalogue.Product>(o => o.Colour);
    strategy.LoadWith<Meta2.Catalogue.Product>(o => o.Size);
    strategy.LoadWith<Meta2.Catalogue.Product>(o => o.Finish);

    meta.Context.FetchStrategy = strategy;

    var result = from o in meta.GetAll<Meta2.Inventory.StocktakeLine>()
                 where o.Stocktake != null && o.Stocktake.Id == id
                 select Models.Inventory.StocktakeLineModel.Map(o);

    return result.OrderBy(o => o.Reference);
}

[IT]

OK... it seems I've introduced this issue by using the OrderBy:-

return result.OrderBy(o => o.Reference);
// instead of...
return result;

Which was put there due to the other grid issue I'm having...

http://www.telerik.com/account/support-tickets/view-ticket.aspx?threadid=477345

Removing the OrderBy stops the entire resultset and associated tables from being iterated, and speeds up paging/sorting... I just can't use aggregates

[Atanas Korchev]

Hi Aleks,

 This is strange as OrderBy should have returned an IQueryable as well. Could you please try ordering before making the select? Something like this:

var result = from o in meta.GetAll<Meta2.Inventory.StocktakeLine>()

                 where o.Stocktake != null && o.Stocktake.Id == id
                 orderby o.Reference

                 select Models.Inventory.StocktakeLineModel.Map(o);

     

Regards,
Atanas Korchev
the Telerik team

[Munish]

Hi All, 
I am facing the same issue Paging is set to10 and its taking approx. 6 sec to load.
This is the code

Controller :

 public ActionResult Search()
        {
            if (!string.IsNullOrEmpty(Request["CustomerID"]))
            {
                Session.Add("CaseIndexCustomerID", Request["CustomerID"]);
 }
            else { Session.Remove("CaseIndexCustomerID"); }
            if (!string.IsNullOrEmpty(Request["CaseTypeID"]))
            {
                Session.Add("CaseIndexCaseTypeID", Request["CaseTypeID"]);


            }
            else { Session.Remove("CaseIndexCaseTypeID"); }
            if (!string.IsNullOrEmpty(Request["AssignedTo"]))
            {
                Session.Add("CaseIndexAssignedTo", Request["AssignedTo"]);


            }
            else { Session.Remove("CaseIndexAssignedTo"); }
            if (!string.IsNullOrEmpty(Request["DrugID"]))
            {
                Session.Add("CaseIndexDrugID", Request["DrugID"]);


            }
            else { Session.Remove("CaseIndexDrugID"); }
            if (!string.IsNullOrEmpty(Request["PriorityID"]))
            {
                Session.Add("CaseIndexPriorityID", Request["PriorityID"]);


            }
            else { Session.Remove("CaseIndexPriorityID"); }


            var query = GetCaseIndex();
            Session["CaseIndexTotalCases"] = query.Count();
            DateTime tilldate = DateTime.Now.AddDays(-5);
            Session["CaseIndexOverFiveDaysCase"] = query.Where(c => c.EmailDate <= tilldate).Count();


            return PartialView();
        }

  

[GridAction]
        public ActionResult _Search()
        {   return PartialView(new GridModel<vw_Case>
            {
                Data = GetCaseIndex()
            });
        }

private IQueryable<vw_Case> GetCaseIndex()
       {
           var query = (from c in db.vw_Case
                        join e in db.Emails on c.EmailID equals e.EmailID
                        where e.ProcessedStatusID == 2 && c.ProcessedStatusID == 1
                        select c);
           if (Session["CaseIndexCustomerID"] != null)
           {
               int CustomerID = int.Parse(Session["CaseIndexCustomerID"].ToString());
               query = query.Where(c => c.CustomerId == CustomerID);
           }
           if (Session["CaseIndexAssignedTo"] != null)
           {
               Guid AssignedTo = Guid.Parse(Session["CaseIndexAssignedTo"].ToString());
               query = query.Where(c => c.EmailAssignedTo == AssignedTo);
           }
           if (Session["CaseIndexPriorityID"] != null)
           {
               int PriorityID = int.Parse(Session["CaseIndexPriorityID"].ToString());
               query = query.Where(c => c.PriorityID == PriorityID);
           }
           if (Session["CaseIndexCaseTypeID"] != null)
           {
               int CaseTypeID = int.Parse(Session["CaseIndexCaseTypeID"].ToString());
               query = query.Where(c => c.CaseTypeID == CaseTypeID);
           }
           if (Session["CaseIndexDrugID"] != null)
           {
               int DrugID = int.Parse(Session["CaseIndexDrugID"].ToString());
               query = query.Where(c => c.DrugID == DrugID);
           }
           return query;

       }

View:
@(Html.Telerik().Grid<PVDataSmart.ClientDAL.vw_Case>()
        .Name("Grid")
        .Columns(columns =>
        {
            columns.Bound(c => c.PMID).Width(70).Title("PMID")
             .ClientTemplate("<a href=\"Case/Edit/<#= CaseID #>\"><#= PMID #></a>");
            columns.Bound(o => o.DrugName).Width(70);        
            columns.Bound(o => o.CustomerName).Width(100);
            columns.Bound(o => o.CaseType).Width(70);
            columns.Bound(o => o.AssignedUserName).Width(80);
            columns.Bound(o => o.EmailID).Width(70);
            columns.Bound(o => o.EmailDate).Format("{0:MMM dd,yyyy}").Width(70).Title("Email received");
            columns.Bound(c => c.DaysOld).Width(50)
               .ClientTemplate("<label><#= DaysOld #> days</label>");


            columns.Bound(o => o.CasesInEmaill).Width(50);
            columns.Bound(o => o.UnprocessedCasesInEmaill).Width(50);
          
        })
        .DataBinding(dataBinding => dataBinding.Ajax().Select("_Search", "Case"))
                                    .Sortable().Scrollable()                                     
                                     .Pageable(paging =>
                paging.PageSize(10))
                                 .Filterable()                           
)

---------------------------------------------------
So, here we have no orderby and IEnumerable, it is IQuerable<>.
It is very slow.
Can we improve it?

Regards,
Munish Sehgal