2
\$\begingroup\$

This AjaxCompound is invoked every time when the user types a key for search or click to sort. It contains 1million data 10 distinct rows by compound name. It takes 1.1 minutes and return only 1-5 KB data to search (general and column specific) or sort or pagination. Any way to reduce the execution time? Any wrong practice that I should correct in following code?

[OutputCache(Duration = 10, VaryByParam = "none")]
    public ActionResult AjaxCompound(JQueryDataTableParamModel param)
    {
        IQueryable<Compound> allCompounds = _context.Compounds.GroupBy(x => x.CompoundName).Select(x => x.FirstOrDefault());
        IEnumerable<Compound> filteredCompounds;
        if (!string.IsNullOrEmpty(param.sSearch))
        {
            filteredCompounds = allCompounds
                     .Where(c => c.CompoundName.Contains(param.sSearch)
                                 ||
                                 c.RI.Contains(param.sSearch)
                                  ||
                                 c.MolecularWeight.Contains(param.sSearch)
                                 ||
                                 c.MolecularFormula.Contains(param.sSearch)
                                 );
        }
        else
        {
            filteredCompounds = allCompounds;
        }

        var sortColumnIndex = Convert.ToInt32(Request["iSortCol_0"]);
        Func<Compound, string> orderingFunction = (c => sortColumnIndex == 1 ? c.CompoundName :
                                                            sortColumnIndex == 2 ? c.RI :
                                                            sortColumnIndex == 3 ? c.MolecularFormula :
                                                            c.MolecularWeight);




        var name = Convert.ToString(Request["sSearch_1"]);
        var rI = Convert.ToString(Request["sSearch_2"]);
        var mW = Convert.ToString(Request["sSearch_4"]);
        var mF = Convert.ToString(Request["sSearch_3"]);
        //var areaPercent = Convert.ToString(Request["sSearch_6"]);



        if (!string.IsNullOrEmpty(name))
        {
            filteredCompounds = filteredCompounds.Where(c => c.CompoundName.Contains(name));

        }

        if (!string.IsNullOrEmpty(rI))
        {
            filteredCompounds = filteredCompounds.Where(c => c.RI.Contains(rI));

        }

        if (!string.IsNullOrEmpty(mF))
        {
            filteredCompounds = filteredCompounds.Where(c => c.MolecularFormula.Contains(mF));

        }

        if (!string.IsNullOrEmpty(mW))
        {
            filteredCompounds = filteredCompounds.Where(c => c.MolecularWeight.Contains(mW));

        }

        //if (!string.IsNullOrEmpty(areaPercent))
        //{
        //    filteredCompounds = filteredCompounds.Where(c => c.AreaPercent.Contains(areaPercent));

        //}

        var sortDirection = Request["sSortDir_0"];
        if (sortDirection == "asc")
            filteredCompounds = filteredCompounds.OrderBy(orderingFunction);
        else
            filteredCompounds = filteredCompounds.OrderByDescending(orderingFunction);

        var displayedCompounds = filteredCompounds
                           .Skip(param.iDisplayStart)
                           .Take(param.iDisplayLength).ToList();
        var result = from c in displayedCompounds
                     select new[] { c.PaperId, c.CompoundName, c.RI, c.MolecularWeight, c.MolecularFormula };
        return Json(new
        {
            sEcho = param.sEcho,
            iTotalRecords = allCompounds.Count(),
            iTotalDisplayRecords = filteredCompounds.Count(),
            aaData = result
        },
                         JsonRequestBehavior.AllowGet);
    }
\$\endgroup\$
  • \$\begingroup\$ The performance bottleneck is likely in the backend database rather than in the C# code. Have you measured the time it takes to perform just the database query? \$\endgroup\$ – 200_success Aug 26 '18 at 20:04
  • \$\begingroup\$ I measured the time in the developer tool of a chrome. Should I post the database here? \$\endgroup\$ – Ujwal Neupane Aug 27 '18 at 3:58
  • \$\begingroup\$ Chrome's Developer Tool would likely not provide a breakdown of how the server spends its time. \$\endgroup\$ – 200_success Aug 27 '18 at 4:55
  • \$\begingroup\$ Can you answer which one is the fast approach? To retrieve data from the database or from a session? \$\endgroup\$ – Ujwal Neupane Aug 27 '18 at 5:27
  • \$\begingroup\$ What is _context? Or: which ORM do you use? \$\endgroup\$ – Gert Arnold Aug 29 '18 at 9:15

Your Answer

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

Browse other questions tagged or ask your own question.