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

Grid Paging Performance Issue with Kendo UI for MVC

6 Answers 251 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Mauricio
Top achievements
Rank 1
Mauricio asked on 16 Oct 2018, 11:53 PM

Hi,

I have some nasty performance issues  when paging through grid control, since we have lot of registers (300k or more), we are using the server-side aproach, but for some reason the last page takes forever but if you go to page 2,3,4 etc. is fast, its seems when you are close to last pages the time increases why? it not should be the same or similar amount time going to page 4 or last page?

So when we bring a lot of registers, go to second page can take a second or seconds and the last page takes sometimes minutes! I see a big load in the database server when click on last pages. I tested this on version 2014 and 2016.

Is there some parameter or configuration to improve this or is it a kind of bug? or why this could happen?

Thank you.

6 Answers, 1 is accepted

Sort by
0
Tsvetina
Telerik team
answered on 18 Oct 2018, 12:05 PM
Hello Mauricio,

When server paging is performed, the Grid and DataSource do not participate in the actual paging. The DataSource only sends the needed page (index) and pageSize parameters to the server and then returns the result from the server to the Grid. This being said, as long as the page size in the Grid is not too large, there shouldn't be a visible slowdown, no matter what page the Grid is on.

This being said, the problem is most probably in the logic that retrieves the page in question from the database. You can read a discussion about performance problems with paging large data in Entity Framework (if you are using it) here:
Entity Framerowk Skip/Take is very slow when number to skip is big

There are a lot of other threads discussing slow handling of paging with large data sets and I would advise you to do such a search based on the specific technologies used in your project backend.

If you want us to take a look to confirm that all else is properly configured as far as the Grid and DataSource are concerned, you can paste the relevant code and I will check it for any visible issues.


Regards,
Tsvetina
Progress Telerik
Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
0
Mauricio
Top achievements
Rank 1
answered on 22 Oct 2018, 08:56 PM

Hello Tsvetina,

Thank you for your response, in my case we create an  IQueryable and build the query, after that we use Kendo.Mvc.Extension
and call the method ToDataSourceResult.

  public ActionResult JsonMedidores([DataSourceRequest] Kendo.Mvc.UI.DataSourceRequest request, FiltrosMedidor filtros)
        {
            IQueryable<TableMedidorViewModel> lstMedidores = GetList(filtros);
            try
            {
                //filtros de las columnas se realizan de manera manual
                if (request.Filters.Any())
                {
                    List<FilterDescriptor> lstItemFiltros = new List<FilterDescriptor>();

                    lstItemFiltros = FiltrosGrid.ObtenerFiltros(request);

                    //se filtran los datos por los filtros del grid
                    lstMedidores = (IQueryable<TableMedidorViewModel>)FiltrosColumnasGrid.FiltrarM(lstMedidores, lstItemFiltros);

                    //se limpian los filtros para que el ToDataSourceResult no filtre nuevamente
                    request.Filters.Clear();
                }
            }
            catch (Exception ex)
            {
                LogError.Add("WEB:SIGAMI-TEST: Error en lista de filtros : " + ex, AbstractLog.IdTipoErrorCatch);
                return Json(lstMedidores.ToDataSourceResult(request), JsonRequestBehavior.AllowGet);
            }

            if(lstMedidores.Count() ==0 )
            {
                return Json(lstMedidores);
            }

            return Json(lstMedidores.ToDataSourceResult(request), JsonRequestBehavior.AllowGet);
        }

 

0
Tsvetina
Telerik team
answered on 24 Oct 2018, 01:55 PM
Hi Mauricio,

If you are already doing custom filtering, you should consider using custom binding altogether. This will give you better control over the exact way the filtering, sorting and paging are applied. You can read more about configuring custom binding here:
Custom Binding documentation 
and see a demo here:
Custom Ajax Binding

Also, just in case, can you show the code in the FiltrarM method? Could you confirm that the logic inside it is not reading the entire data from the database before filtering it?

Regards,
Tsvetina
Progress Telerik
Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
0
Mauricio
Top achievements
Rank 1
answered on 25 Oct 2018, 07:39 PM

Hi Tsvetina,

Here is the class with the method FiltrarM

public class FiltrosColumnasGrid
 
    {
 
         
 
        public static IQueryable Filtrar(IQueryable lstFiltrar,string filtrosGrid)
 
        {
 
            try
 
            {
 
                var filtrosDes = JsonConvert.DeserializeObject<FilterList>(filtrosGrid);
 
  
 
                foreach (var filtro in filtrosDes.filters)
 
                {
 
                    if (filtro.Field == "medidores_cliente_from_xml" || filtro.Field == "estatusMedidor" || filtro.Field == "ruta_from_cuenta")
 
                    {
 
                        switch (filtro.Field) {
 
                            case "medidores_cliente_from_xml":
 
                                filtro.Field = "medidores_cliente";
 
                                filtro.Value = Convert.ToString(filtro.Value).ToUpper();
 
                                break;
 
                            case "estatusMedidor":
 
                                filtro.Field = "idEstatusMedidor";
 
                                switch (Convert.ToString(filtro.Value))
 
                                {
 
                                    case "Activo":
 
                                        filtro.Value = 1;
 
                                        break;
 
                                    case "Inactivo":
 
                                        filtro.Value = 2;
 
                                        break;
 
                                    case "Baja":
 
                                        filtro.Value = 3;
 
                                        break;
 
                                }
 
                                break;
 
                        }
 
                    }
 
  
 
                    string exp = "";//expresion que se utilizara segun el caso
 
  
 
                    switch (filtro.Operator)
 
                    {
 
                        case "contains"://FilterOperator.Contains:
 
                            exp = filtro.Field + ".Contains(" + "@0)";
 
                            break;
 
                        case "doesnotcontain":
 
                                exp = "!" + filtro.Field + ".Contains(" + "@0)" + " || " + filtro.Field + " == null";
 
                            break;
 
                        case "endswith"://FilterOperator.EndsWith:
 
                            exp = filtro.Field + ".EndsWith(" + "@0)";
 
                            break;
 
                        case "eq"://FilterOperator.IsEqualTo:
 
                            exp = filtro.Field + "=@0";
 
                            break;
 
                        case "gt": //FilterOperator.IsGreaterThan:
 
                            exp = filtro.Field + ">@0";
 
                            break;
 
                        case "gte"://FilterOperator.IsGreaterThanOrEqualTo:
 
                            exp = filtro.Field + ">=@0";
 
                            break;
 
                        case "lt"://FilterOperator.IsLessThan:
 
                            exp = filtro.Field + "<@0";
 
                            break;
 
                        case "lte"://FilterOperator.IsLessThanOrEqualTo:
 
                            exp = filtro.Field + "<=@0";
 
                            break;
 
                        case "neq"://FilterOperator.IsNotEqualTo: // si se usa XD
 
                            exp = filtro.Field + "!=@0";
 
                            break;
 
                        case "startswith"://FilterOperator.StartsWith:
 
                            exp = filtro.Field + ".StartsWith(" + "@0)";
 
                            break;
 
                        case ""://FilterOperator.IsContainedIn:
 
                            break;
 
                    }
 
  
 
                    lstFiltrar = lstFiltrar.Where(exp, filtro.Value);
 
                }
 
                return lstFiltrar;
 
            }
 
            catch(Exception ex)
 
            {
 
                LogError.Add("Error al realizar filtros de grid en lista para la creación del exel: " + ex , AbstractLog.IdTipoErrorCatch);
 
                return lstFiltrar;
 
            }
 
        }
 
        
 
         
 
        public static string Crearexpresion(string Field, FilterOperator Operator, string operatorLogic, bool numValoresExpresion)
 
        {
 
            switch (Operator)
 
            {
 
                case FilterOperator.Contains:
 
                    if (numValoresExpresion)
 
                        return Field + ".Contains(@0)" + operatorLogic + Field + ".Contains(@1)";
 
                    else
 
                        return Field + ".Contains(" + "@0)";
 
                case FilterOperator.DoesNotContain:
 
                    return "!" + Field + ".Contains(" + "@0)";
 
                case FilterOperator.EndsWith:
 
                    return Field + ".EndsWith(" + "@0)";
 
                case FilterOperator.IsContainedIn:
 
                case FilterOperator.IsEqualTo:
 
                    return Field + "=@0";
 
                case FilterOperator.IsGreaterThan:
 
                    return Field + ">@0";
 
                case FilterOperator.IsGreaterThanOrEqualTo:
 
                    return Field + ">=@0";
 
                case FilterOperator.IsLessThan:
 
                    return Field + "<@0";
 
                case FilterOperator.IsLessThanOrEqualTo:
 
                    return Field + "<=@0";
 
                case FilterOperator.IsNotEqualTo: // si se usa XD
 
                    return Field + "!=@0";
 
                case FilterOperator.StartsWith:
 
                    return  Field + ".StartsWith(" + "@0)";
 
                default:
 
                    return "";
 
            }
 
        }
 
  
 
        public static IQueryable FiltrarM(IQueryable lstFiltrar, List<FilterDescriptor> filtrosGrid)
 
        {
 
            try
 
            {
 
                Log.Add("WEB:SIGAMI-TEST: llego a  filtros " + DateTime.Now);
 
                foreach (var filtros in filtrosGrid)
 
                {
 
                    string exp = "";//expresion que se utilizara segun el caso
 
                    //bool isAreaZona = false;
 
                    switch (filtros.Operator)
 
                    {
 
                        case FilterOperator.Contains:
 
                            exp = filtros.Member + ".Contains(" + "@0)";
 
                            break;
 
                        case FilterOperator.DoesNotContain:
 
                            exp = "!" + filtros.Member + ".Contains(" + "@0)" + " || " + filtros.Member + " == null";
 
                            break;
 
                        case FilterOperator.EndsWith:
 
                            exp = filtros.Member + ".EndsWith(" + "@0)";
 
                            break;
 
                        case FilterOperator.IsEqualTo:
 
                            exp = filtros.Member + "=@0";
 
                            break;
 
                        case FilterOperator.IsGreaterThan:
 
                            exp = filtros.Member + ">@0";
 
                            break;
 
                        case FilterOperator.IsGreaterThanOrEqualTo:
 
                            exp = filtros.Member + ">=@0";
 
                            break;
 
                       case FilterOperator.IsLessThan:
 
                            exp = filtros.Member + "<@0";
 
                            break;
 
                        case FilterOperator.IsLessThanOrEqualTo:
 
                            exp = filtros.Member + "<=@0";
 
                            break;
 
                        case FilterOperator.IsNotEqualTo: // si se usa XD
 
                            exp = filtros.Member + "!=@0";
 
                            break;
 
                        case FilterOperator.StartsWith:
 
                            exp = filtros.Member + ".StartsWith(" + "@0)";
 
                            break;
 
                        default: //FilterOperator.IsContainedIn:
 
                            break;
 
                    }
 
  
 
                    bool isDecimal = false;
 
                    switch (filtros.Member)
 
                    {
 
                        case "kwh":
 
                        case "latitud":
 
                        case "longitud":
 
                        case "p1_kwhEntregado":
 
                        case "p1_kwhRecibido":
 
                        case "p1_kvarh_i":
 
                        case "p1_kvarh_ii":
 
                        case "p1_kvarh_iii":
 
                        case "p1_kvarh_iv":
 
                        case "p1_voltaje":
 
                        case "p1_corriente":
 
                        case "p1_potenciaAparente":
 
                        case "p1_potenciaReactiva":
 
                        case "p1_potenciaActiva":
 
                        case "p1_factorPotencia":
 
                        case "p1_frecuencia":
 
                        case "mp_kwhEntregado":
 
                        case "mp_kwhRecibido":
 
                        case "mp_kvarh_i":
 
                        case "mp_kvarh_ii":
 
                        case "mp_kvarh_iii":
 
                        case "mp_kvarh_iv":
 
                        case "mp_voltaje":
 
                        case "mp_corriente":
 
                        case "mp_potenciaAparente":
 
                        case "mp_potenciaReactiva":
 
                        case "mp_potenciaActiva":
 
                        case "mp_factorPotencia":
 
                        case "mp_frecuencia":
 
                        case "kwhUltimaLecturaReal":
 
                            isDecimal = true;
 
                            break;
 
                        default:
 
                            break;
 
  
 
                    }
 
                   
 
                    // conversion para cordenadas
 
                    if (isDecimal)
 
                    {
 
                        decimal valueConvert = Convert.ToDecimal(filtros.Value);
 
                        lstFiltrar = lstFiltrar.Where(exp, valueConvert);
 
                    }
 
                    else
 
                    {
 
                        lstFiltrar = lstFiltrar.Where(exp, filtros.Value);
 
                    }
 
  
 
                }
 
                return lstFiltrar;
 
            }
 
            catch (Exception ex)
 
            {
 
                return lstFiltrar;
 
            }
 
        }
 
  
 
 
    }
0
Accepted
Tsvetina
Telerik team
answered on 29 Oct 2018, 03:19 PM
Hi Mauricio,

Indeed, it looks like you are already implementing custom filtering, so it is best to also add sorting and paging to it as shown in the Custom Ajax Binding demo and remove the ToDataSourceResult() call. This will allow you the freedom to apply optimizations of your own to the way the data is queried.
If the usage of IQueryable does not provide good enough results in terms of performance, you could consider executing a stored procedure passing the parameters extracted from the request, so you can control the exact way the database is queried.

Regards,
Tsvetina
Progress Telerik
Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
0
Mauricio
Top achievements
Rank 1
answered on 31 Oct 2018, 03:53 PM

Hello Tsvetina,

Thank you so much for your help and advices.

Best regards,

Tags
Grid
Asked by
Mauricio
Top achievements
Rank 1
Answers by
Tsvetina
Telerik team
Mauricio
Top achievements
Rank 1
Share this question
or