Pagination in Asp.net core problems, only getting 10 items not making it faster?

Question

i am trying to improve my pagination in Asp.net core web app... I want only to load 10 records each page. The first time i did this, i was loading all records, and then at the end i was just getting 10 of those and sending to the View, pretty bad since i am getting data twice and the first time im getting all records... (examples below) My approach now is getting actually the 10 items only.. but before this i count them all so that i can have the number of total pages... (examples below)

in another View without pagination i have like 13 records and i get them all, in that i take like 100-300 Ms more or less... So my point here is, with the seconds approach, why my time is about 2,60 seconds? shouldnt it be like 1 seconds max? Also, the difference between the first and seconds approach is minimum, and i have 8000+ records on the table... what am i doing wrong? how to improve this time?

my code for first approach:

ViewModelCentroCusto vmcc = new ViewModelCentroCusto()
            {
                CurrentPage = page,
                FirstPageShow = Math.Max(2, page - NUMBER_OF_PAGES_BEFORE_AND_AFTER),
                Departamentos = selectListdepartamentos,
                Empresas = selectListEmpresas,
                Anos = selectListAnos.GroupBy(x => x.Text).Select(x => x.First()).OrderBy(o => o.Text).ToList()
            };

            //atualizacao do viewModel com base em filtros, pesquisas e paginacoess
            if (!String.IsNullOrEmpty(searchString))
            {

                vmcc.CentroCustos = _context.RH_CentroCusto
                .Where(p => anoSelecionado.Any() ? anoSelecionado.Contains(p.Ano.ToString()) : true)
                .Where(p => departmentoSelecionado.Any() ? departmentoSelecionado.Contains(p.Departamento.Departamento) : true)
                .Where(p => empresaSelecionada.Any() ? empresaSelecionada.Contains(p.Empresa.Codigo) : true)
                .Where(p => p.CentroCusto.Contains(searchString) || p.Descricao.Contains(searchString));
                
            }
            else {


                vmcc.CentroCustos = _context.RH_CentroCusto
               .Where(p => anoSelecionado.Any() ? anoSelecionado.Contains(p.Ano.ToString()) : true)
               .Where(p => departmentoSelecionado.Any() ? departmentoSelecionado.Contains(p.Departamento.Departamento) : true)
               .Where(p => empresaSelecionada.Any() ? empresaSelecionada.Contains(p.Empresa.Codigo) : true);
               
            }

            vmcc.TotalPages = (int)Math.Ceiling((decimal)vmcc.CentroCustos.Count() / NUMBER_OF_PRODUCTS_PER_PAGE);
            vmcc.CentroCustos = vmcc.CentroCustos.Skip((page - 1) * NUMBER_OF_PRODUCTS_PER_PAGE);
            vmcc.CentroCustos = vmcc.CentroCustos.Take(NUMBER_OF_PRODUCTS_PER_PAGE);
            vmcc.LastPageShow = Math.Min(vmcc.TotalPages, page + NUMBER_OF_PAGES_BEFORE_AND_AFTER);
            vmcc.FirstPage = 1;
            vmcc.LastPage = vmcc.TotalPages;
            vmcc.CurrentSearchString = searchString;
            vmcc.DepartmentoSelecionado = departmentoSelecionado;
            vmcc.AnoSelecionado = anoSelecionado;
            vmcc.EmpresaSelecionada = empresaSelecionada;

            return View(vmcc);

this is where i get all the records, then i get 10 of those, makes no sense at least for me, so i changed to this:

ViewModelCentroCusto vmcc = new ViewModelCentroCusto()
            {
                CurrentPage = page,
                FirstPageShow = Math.Max(2, page - NUMBER_OF_PAGES_BEFORE_AND_AFTER),
                Departamentos = selectListdepartamentos,
                Empresas = selectListEmpresas,
                Anos = selectListAnos.GroupBy(x => x.Text).Select(x => x.First()).OrderBy(o => o.Text).ToList()
            };

            //atualizacao do viewModel com base em filtros, pesquisas e paginacoess
            if (!String.IsNullOrEmpty(searchString))
            {
                number = _context.RH_CentroCusto
                .Where(p => anoSelecionado.Any() ? anoSelecionado.Contains(p.Ano.ToString()) : true)
                .Where(p => departmentoSelecionado.Any() ? departmentoSelecionado.Contains(p.Departamento.Departamento) : true)
                .Where(p => empresaSelecionada.Any() ? empresaSelecionada.Contains(p.Empresa.Codigo) : true)
                .Where(p => p.CentroCusto.Contains(searchString) || p.Descricao.Contains(searchString))
                .Count();

                vmcc.CentroCustos = _context.RH_CentroCusto
                .Where(p => anoSelecionado.Any() ? anoSelecionado.Contains(p.Ano.ToString()) : true)
                .Where(p => departmentoSelecionado.Any() ? departmentoSelecionado.Contains(p.Departamento.Departamento) : true)
                .Where(p => empresaSelecionada.Any() ? empresaSelecionada.Contains(p.Empresa.Codigo) : true)
                .Where(p => p.CentroCusto.Contains(searchString) || p.Descricao.Contains(searchString))
                .Skip((page - 1) * NUMBER_OF_PRODUCTS_PER_PAGE)
                .Take(NUMBER_OF_PRODUCTS_PER_PAGE); 
            }
            else {
                number = _context.RH_CentroCusto
               .Where(p => anoSelecionado.Any() ? anoSelecionado.Contains(p.Ano.ToString()) : true)
               .Where(p => departmentoSelecionado.Any() ? departmentoSelecionado.Contains(p.Departamento.Departamento) : true)
               .Where(p => empresaSelecionada.Any() ? empresaSelecionada.Contains(p.Empresa.Codigo) : true)
               .Count();

                vmcc.CentroCustos = _context.RH_CentroCusto
               .Where(p => anoSelecionado.Any() ? anoSelecionado.Contains(p.Ano.ToString()) : true)
               .Where(p => departmentoSelecionado.Any() ? departmentoSelecionado.Contains(p.Departamento.Departamento) : true)
               .Where(p => empresaSelecionada.Any() ? empresaSelecionada.Contains(p.Empresa.Codigo) : true)
               .Skip((page - 1) * NUMBER_OF_PRODUCTS_PER_PAGE)
               .Take(NUMBER_OF_PRODUCTS_PER_PAGE);
            }

            vmcc.TotalPages = (int)Math.Ceiling(number / NUMBER_OF_PRODUCTS_PER_PAGE);
            //vmcc.CentroCustos = vmcc.CentroCustos.Skip((page - 1) * NUMBER_OF_PRODUCTS_PER_PAGE);
            //vmcc.CentroCustos = vmcc.CentroCustos.Take(NUMBER_OF_PRODUCTS_PER_PAGE);
            vmcc.LastPageShow = Math.Min(vmcc.TotalPages, page + NUMBER_OF_PAGES_BEFORE_AND_AFTER);
            vmcc.FirstPage = 1;
            vmcc.LastPage = vmcc.TotalPages;
            vmcc.CurrentSearchString = searchString;
            vmcc.DepartmentoSelecionado = departmentoSelecionado;
            vmcc.AnoSelecionado = anoSelecionado;
            vmcc.EmpresaSelecionada = empresaSelecionada;

            return View(vmcc);

Now i only get 10 i think, then why isn´t getting wayy faster? Any help is appreciated

Which version of EF core are you using? On previous versions of EF, when a query could not be translated to be run on the database side, it would be run it on the client side, which means downloading a lot of data! Nowadays, you need to specificaly query AsEnumerable to run a query on the client side, and non-convertible syntax throws an Exception to prevent this behavior.Can you check which queries are runnning on the DB Side when you run your snippet? — XavierAM, Oct 16 '20 at 12:11
My Version of EF core is 3.1.8. ! I am gona be straigh honest with you, but how do i do that? What i normally do is Click f12 and see my network and there i get all data i normally need.. Is it there? — Ricardo Figueiredo, Oct 16 '20 at 13:30
Hmm, no, this is the latest version, you should not be able to run a non-db query without explicitly specifying that you want it on the client side. You should look somewhere else. Depending on the db you use, you should look for a monitoring tool which gives you access to DB perf monitoring. F12 only gives you data going over the network without any detail of what's happening inside. — XavierAM, Oct 16 '20 at 14:00

score 2 · Accepted Answer · answered Oct 16 '20 at 10:42

My guess without knowing your database, row count, your system specs and the rest of your implementation, would be because of Count

                number = _context.RH_CentroCusto
                .Where(p => anoSelecionado.Any() ? anoSelecionado.Contains(p.Ano.ToString()) : true)
                .Where(p => departmentoSelecionado.Any() ? departmentoSelecionado.Contains(p.Departamento.Departamento) : true)
                .Where(p => empresaSelecionada.Any() ? empresaSelecionada.Contains(p.Empresa.Codigo) : true)
                .Where(p => p.CentroCusto.Contains(searchString) || p.Descricao.Contains(searchString))
                .Count();

This query, would need to be evaluated and scan all your database rows for the total. It will take, pretty much the same amount of time as your original:

                vmcc.CentroCustos = _context.RH_CentroCusto
                .Where(p => anoSelecionado.Any() ? anoSelecionado.Contains(p.Ano.ToString()) : true)
                .Where(p => departmentoSelecionado.Any() ? departmentoSelecionado.Contains(p.Departamento.Departamento) : true)
                .Where(p => empresaSelecionada.Any() ? empresaSelecionada.Contains(p.Empresa.Codigo) : true)
                .Where(p => p.CentroCusto.Contains(searchString) || p.Descricao.Contains(searchString));

In my mind i was like "counting all items should take less time than actually getting all items"... I will have try to improve this in another way, thanks for the help!! — Ricardo Figueiredo, Oct 16 '20 at 10:54
@RicardoFigueiredo Your counting the items on the database, so your not actually transfering all the data from the database to your webserver, if that was what you were doing before there should be a little improvement. To speed up those actions you could cache that information or create indexes for those columns, although caching is impossible due to the aparent dinamicity of that query. Did you created indexes for 'anoSelecionado', 'departmentoSelecionado','empresaSelecionada','CentroCusto','Descricao' ? — Mario Figueiredo, Oct 16 '20 at 14:06
I am not working with Indexes at all... There was an improvement, but it it was so low that almost doesn´t make any difference.. Also, i have no idea how to use indexes. — Ricardo Figueiredo, Oct 16 '20 at 14:28
Indexes are applied to your database. Interesting read on wikipedia to expand your knowledge: https://en.wikipedia.org/wiki/Database_index — Athanasios Kataras, Oct 16 '20 at 15:13

Pagination in Asp.net core problems, only getting 10 items not making it faster?

1 Answers1