0

I'm facing an issue where DefaultIfEmpty() causes slow response. The code is like:

rslt = (
    from p in P.Where(p => p.fcode == fCode && p.year == year && p.is_deleted == false && p.pname != pNameS)
    from c in C.Where(c => c.fcode == fCode && c.ccode == p.ccode)
    from pf in PF.Where(pf => pf.fcode == fCode && pf.pcode == p.pcode).DefaultIfEmpty()
    from defHU in DEF.Where(def => def.fixed_key == Constants.HU && pf.hu == def.def_key).DefaultIfEmpty()
    from defAU in DEF.Where(def => def.fixed_key == Constants.AU && pf.pau == def.def_key).DefaultIfEmpty()
    select new PPShow{
        Year = p.year,
        PCode = p.pcode,
        PName = p.production_name,
        FCode = pf.fcode,
        Br = p.br,
        CName = c.cname,
        PArea = pf.parea,
        PAU = pf.pau,
        PAUName = defAU.def_data,
        HA = pf.ha,
        HUName = defHU.def_data,
        SDate = p.sdate,
        PDate = p.pdate,
        StartDate = p.start_date,
        EndDate = p. end_date,
        Flag = 1
    }).ToList();

I have tried to query for 10,000 records and it took approximately 8.5s to finish, while it took only about 1s if I remove all the DefaultIfEmpty().

I wonder what DefaultIfEmpty() actually does. Also, is there any solution to my case?

ItamarG3
  • 4,092
  • 6
  • 31
  • 44
Chanh Tran
  • 231
  • 2
  • 8
  • 3
    _"I wonder what DefaultIfEmpty() actually does"_ and i wonder if you know what you actually want – Tim Schmelter Jul 03 '17 at 10:46
  • It returns 0 (or the value we want) if the query returns nothing, right? What I'm not sure is how it works. It loops through the table or something? – Chanh Tran Jul 03 '17 at 14:15
  • `DefaultIfEmpty`: https://msdn.microsoft.com/en-us/library/bb360179%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396 so no, it doesn't loop through anything. However, it may make an index unusable in SQL. – NetMage Jul 03 '17 at 17:39

0 Answers0