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
0
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
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
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
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
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
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
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
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
Rank 1
answered on 31 Oct 2018, 03:53 PM
Hello Tsvetina,
Thank you so much for your help and advices.
Best regards,