今日改了一個linq，為了查詢最佳化。
主要思路是把子查詢改為連線查詢。
改完後，本地執行是很快的；但是釋出到伺服器，加上網路時間，就有點不如意了。
所以感覺，非常複雜的查詢還是直接用sql寫好。
更改前後
更改前是這樣的。
void setPropety(List<SEO_Cust_RechargeModel> _list) { SEO_Set_MealBusiness sEO_Set_MealBusiness = new SEO_Set_MealBusiness(); SEO_CustBusiness sEO_CustBusiness = new SEO_CustBusiness(); //SEO_Cust_ProductBusiness sEO_Cust_ProductBusiness = new SEO_Cust_ProductBusiness(); var domain = new Seo_Domain.SEO_DomainBusiness().GetIQueryable(); var keywords = new SEO_KeyWordBusiness().GetIQueryable(); var newsfeed = new Seo_NewsFeed.SEO_NewsFeedBusiness().GetIQueryable(); var zengzhibus = new SEO_ZengZhiFwBusiness(); _list.ForEach(x => { x.SetMeal_Name = sEO_Set_MealBusiness.GetTheData(x.setmeal_id)?.set_meal_name; x.Cust_Nmae = sEO_CustBusiness.GetTheData(x.cust_id)?.user_name; x.domainnum = domain.Where(xx => xx.cust_recharge_id == x.Id).Count(); x.keywordnum = keywords.Where(xx => xx.recharge_id == x.Id).Count(); x.newsfeednum = newsfeed.Where(xx => xx.recharge_id == x.Id).Count(); x.zengzhinum = zengzhibus.GetIQueryable().Count(y => y.recharge_id == x.Id); x.pinpainum = new SEO_PinPai_KeyWordBusiness().GetIQueryable().Where(xx => xx.recharge_id == x.Id).Count(); x.SEOUser_Name = Base_UserBusiness.GetTheUser(x.seo_user_id)?.RealName; }); }
更改後是這樣的。
void setPropetyByJoin(List<SEO_Cust_RechargeModel> _list) { //5個count var queryCount = from r in _list join dm in new SEO_DomainBusiness().GetIQueryable() on r.Id equals dm.cust_recharge_id into dmg join kw in new SEO_KeyWordBusiness().GetIQueryable() on r.Id equals kw.recharge_id into kwg join nf in new SEO_NewsFeedBusiness().GetIQueryable() on r.Id equals nf.recharge_id into nfg join zz in new SEO_ZengZhiFwBusiness().GetIQueryable() on r.Id equals zz.recharge_id into zzg join pp in new SEO_PinPai_KeyWordBusiness().GetIQueryable() on r.Id equals pp.recharge_id into ppg select new { rId = r.Id, dmCount = dmg.Count(), kwCount = kwg.Count(), nfCount = nfg.Count(), zzCount = zzg.Count(), ppCount = ppg.Count(), }; var lct = queryCount.ToList(); //連線其他表 var queryJoin = from r in _list join c in queryCount.Distinct() on r.Id equals c.rId join sm in new SEO_Set_MealBusiness().GetIQueryable() on r.setmeal_id equals sm.Id into rSmG join cst in new SEO_CustBusiness().GetIQueryable() on r.cust_id equals cst.Id into rCstG join bu in new Base_UserBusiness().GetIQueryable() on r.seo_user_id equals bu.UserId into rBuG join pr in new SEO_ProductBusiness().GetIQueryable() on r.product_id equals pr.Id into rPrG from rSm in rSmG.DefaultIfEmpty() from rCst in rCstG.DefaultIfEmpty() from rBu in rBuG.DefaultIfEmpty() from rPr in rPrG.DefaultIfEmpty() select new { r = r, c = c, agent_id = rCst?.agent_id ?? "", xs_user_id = rCst?.xs_user_id ?? "", SetMeal_Name = rSm == null ? "" : rSm.set_meal_name, Cust_Nmae = rCst == null ? "" : rCst.user_name, SEOUser_Name = rBu == null ? "" : rBu.RealName, pro_name = rPr == null ? "-" : rPr.pro_name, aftersale_user_id = rCst.aftersale_user_id, }; //二次連線的 var queryJoin2 = from m in queryJoin join ag in new SEO_AgentBusiness().GetIQueryable() on m.agent_id equals ag.Id into mAgG join buAfter in new Base_UserBusiness().GetIQueryable() on m.aftersale_user_id equals buAfter.UserId into mBuAfterG from mAg in mAgG.DefaultIfEmpty() from mBuAfter in mBuAfterG.DefaultIfEmpty() select new { r = m.r, c = m.c, agent_id = m.agent_id, xs_user_id = m.xs_user_id, SetMeal_Name = m.SetMeal_Name, Cust_Nmae = m.Cust_Nmae, SEOUser_Name = m.SEOUser_Name, pro_name = m.pro_name, SEOAfterSale_Name = m.aftersale_user_id.IsNullOrEmpty() || m.aftersale_user_id == "0" ? "-" : mBuAfter?.RealName??"-", SEOSale_Name = (m.xs_user_id.IsNullOrEmpty() || m.xs_user_id == "0") ? mAg?.inst_value??"-" : m.SEOUser_Name, }; //迴圈賦值 queryJoin2.Select(m => { //Count的 m.r.domainnum = m.c.dmCount; m.r.keywordnum = m.c.kwCount; m.r.newsfeednum = m.c.nfCount; m.r.zengzhinum = m.c.zzCount; m.r.pinpainum = m.c.ppCount; //單個屬性 m.r.SetMeal_Name = m.SetMeal_Name; m.r.Cust_Nmae = m.Cust_Nmae; m.r.SEOUser_Name = m.SEOUser_Name; m.r.pro_name = m.pro_name; m.r.SEOAfterSale_Name = m.SEOAfterSale_Name; m.r.SEOSale_Name = m.SEOSale_Name; return 1; }).ToList(); }
可以明顯的看到：
子查詢看起來簡單；但是執行效率低。
連線查詢編寫起來複雜，特別是用linq寫，就更復雜了。
還有第二波問題：
改完一波，我才發現，原來的作者在ViewModel里弄了一些有get訪問器的屬性。
而有幾個get訪問器，在裡面操作了資料庫。也相當於子查詢。
於是又改了一波。
另外重要的
另外重要的，感覺是資料表的設計。
因為，感覺，雖然不知道是什麼業務；有些欄位的獲取應該不用關聯這麼多表吧？應該是可以簡化的。