今日改了一个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访问器，在里面操作了数据库。也相当于子查询。
于是又改了一波。
另外重要的
另外重要的，感觉是数据表的设计。
因为，感觉，虽然不知道是什么业务；有些字段的获取应该不用关联这么多表吧？应该是可以简化的。
翻译
搜索
复制
标签：Name,复杂,GetIQueryable,查询,sql,SEO,new,Id,id From： https://www.cnblogs.com/fabao/p/18349401