3
\$\begingroup\$

I am using MVC 4.0/C#/EF4.0 and I have a grid page that grabs results that are filtered, sorted and paged. It's filtering results from a table that has 45,000 records. How do I improve performance? This page takes over 30 seconds to load.

  var filteredresults = _unitOfWork.ModifiedDataRepository.Get();

            NameValueCollection nvc = HttpUtility.ParseQueryString(Request.RequestUri.Query);
            int ID = (String.IsNullOrEmpty(nvc["sSearch_0"]) ? 0 : Convert.ToInt32(nvc["sSearch_0"]));
            int TaxYear = (String.IsNullOrEmpty(nvc["sSearch_1"]) ? 0 : Convert.ToInt32(nvc["sSearch_1"]));
            int AdditionalTax = (String.IsNullOrEmpty(nvc["sSearch_2"]) ? 0 : Convert.ToInt32(nvc["sSearch_2"]));

            filteredresults = filteredresults.Where(i => (ID == 0 || i.ID == ID)
                                                                     && (TaxYear == 0 || i.TaxYear == TaxYear)
                                                                     && (AdditionalTax == 0 || i.AdditionalChildTaxCreditAmount == AdditionalTax)
                                                                     );


            int Count = filteredresults.Count();

            string sortCol = param.sColumns.Split(',')[param.iSortCol_0];
            string sortExpression = sortCol + " " + param.sSortDir_0;

            filteredresults = filteredresults.OrderBy(sortExpression);
            filteredresults = from p in filteredresults
                              orderby (sortExpression)
                              select p;

            var parents = filteredresults.Skip(param.iDisplayStart).Take(param.iDisplayLength);

            var result = from c in parents.ToList()
                         select new MODIFIED_DATA()
                         {
                             ID = c.ID,
                             TaxYear = c.TaxYear,
                             AdditionalChildTaxCreditAmount = c.AdditionalChildTaxCreditAmount,
                             ArrivalDate = c.ArrivalDate,
                             ChildCareCredit = c.ChildCareCredit,
                             DLN = c.DLN,
                             EftCd = c.EftCd,
                             EicAmt = c.EicAmt,
                             ElfSourceCd = c.ElfSourceCd,
                             FilingStatusCd = c.FilingStatusCd,
                             MakingWorkPayCreditAmount = c.MakingWorkPayCreditAmount,
                             PrimPrisonerNmCd = c.PrimPrisonerNmCd
                         };

            var CustomerPaged = new DataTablePager<MODIFIED_DATA>();

            CustomerPaged.sEcho = param.sEcho;
            CustomerPaged.iTotalRecords = Count;
            CustomerPaged.iTotalDisplayRecords = Count;
            CustomerPaged.aaData = result.ToList();

            return CustomerPaged;
\$\endgroup\$
  • \$\begingroup\$ Did you mean 45,000 or 450,000 records? You mis-typed 45,0000 :) \$\endgroup\$ – Phrancis Dec 5 '14 at 21:17
  • \$\begingroup\$ My fault, I meant 45k.. \$\endgroup\$ – Chaka Dec 5 '14 at 21:26
2
\$\begingroup\$

Obvious slowdowns

  • for each property of NameValueCollection which is set, the condition can ommit the ==0 check.
  • you are sorting the results 2 times
  • using .Count(), but this seems to be needed.
  • calling unneccessary .ToList() on parents
  • posibly unneccessary call of .ToList() on result. Here you should check if CustomerPaged.aaData can take an IEnumerable<MODIFIED_DATA>

Naming

  • result is an IEnumerable<MODIFIED_DATA> so the plural should be used -> results
  • the kind of hungarian notation you use is seen as bad practice
  • local variables should be named using camelCase casing

General

Consider to extract the "index" strings, like "sSearch_0", into class const variables

\$\endgroup\$
  • \$\begingroup\$ Thank you so much for the help. Better loadtime now. Any recommendation on the SQL server end? I am basically doing a select on modified_data table here... select using query analayzer takes 30secs to run. Is indexing an option? \$\endgroup\$ – Chaka Dec 6 '14 at 14:34
  • \$\begingroup\$ You can ask a new question about the sql query/stored procedure. Sql is also valid for code review. \$\endgroup\$ – Heslacher Dec 6 '14 at 14:49
  • \$\begingroup\$ Very greatful for the the code review, enjoy your holiday! \$\endgroup\$ – Chaka Dec 6 '14 at 17:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.