复杂查询还是直接写sql吧
今日改了一个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访问器，在里面操作了数据库。也相当于子查询。
于是又改了一波。
另外重要的
另外重要的，感觉是数据表的设计。
因为，感觉，虽然不知道是什么业务；有些字段的获取应该不用关联这么多表吧？应该是可以简化的。
