我知道这个问题已经被问过好几次了，但我一直在尝试从mysql表中多对多相关的多个表中获取数据。而且，每个表至少包含500000条记录。在这种情况下，如何加快数据检索过程？
虽然，我试图获得数据使用分组按屏幕准备数据，但它仍然工作得很慢。
在屏幕的控制器中，我使用了linq组。
var lstclientId = (from c in DBContext.groupmasters.Where(w => w.isactive == true && w.UserID == userid)
group c by c.ClientID into d
select new
{
clientID = d.Key,
});
var lstJobTypeID = (from c in DBContext.jobrequisitions.Where(w => w.ISActive == true)
group c by new { c.CreativeID, c.ProjectName, c.client.ClientName, c.brandmaster.branddesc } into d
select new
{
CreativeID = d.Key.CreativeID,
ProjectName = d.Key.ProjectName,
ClientName = d.Key.ClientName,
branddesc = d.Key.branddesc,
}).OrderBy(o => o.CreativeID);
List<jobrequisition> lstrequisition = new List<jobrequisition>();
List<jobrequisition> lstrequisitionusers = new List<jobrequisition>();
if ((Session["deptCode"]).ToString() == "QC" || (Session["deptCode"]).ToString() == "OP" || Convert.ToBoolean(Session["IsAdmin"]) == true)
{
database2Entities JobEntities = new database2Entities();
lstrequisition = JobEntities.jobrequisitions.Where(w => w.ISActive == true).ToList();
lstrequisitionusers.AddRange(lstrequisition);
}
else
{
foreach (var i in lstclientId)
{
database2Entities JobEntities = new database2Entities();
lstrequisition = JobEntities.jobrequisitions.Where(w => w.ISActive == true && w.ClientID == i.clientID).ToList();
lstrequisitionusers.AddRange(lstrequisition);
}
}
Hashtable ht = new Hashtable();
int k = 0;
foreach (var i in lstJobTypeID)
{
foreach (jobrequisition objRequestion in lstrequisitionusers.OrderBy(o => o.CreativeID))
{
if (objRequestion.CreativeID == i.CreativeID)
{
database2Entities ArtContext = new database2Entities();
database2Entities SubArtContext = new database2Entities();
if (lstArtWorkReqID.Count() == 0)
{
///old code...
var lstArt = ArtContext.artworkrequisitions.SqlQuery("SELECT *, count(ArtWorkName) as awtname FROM artworkrequisition as awt group by ArtWorkID having CreativeID = '" + i.CreativeID + "' and IsActive = 1").ToList();
foreach (artworkrequisition art in lstArt)
{
int isCompletedcount = 0, assigncount = 0, allitemcount = 0, jobassignedcount = 0, iscompletedjobcount = 0;
List<artworkrequisition> allartworkitems = SubArtContext.artworkrequisitions.Where(w => w.ArtWorkID == art.ArtWorkID && w.IsActive == true).ToList();
jobassignedcount = allartworkitems.Where(w => w.isAssigned == true).Count();
allitemcount = allartworkitems.Where(w => w.IsActive == true).Count();
if (jobassignedcount > 0)
{
if (jobassignedcount <= allitemcount)
{
foreach (artworkrequisition singleartitem in allartworkitems)
{
if (singleartitem.isAssigned == null && singleartitem.isAssigned == true)
{
job_assigned_employee allocatedjobdetails = SubArtContext.job_assigned_employee.Where(w => w.ItemJobReqID == singleartitem.Id && w.JobTypeID == 1 && w.JobReqNo == singleartitem.ArtWorkID && w.IsActive == true).FirstOrDefault();
if (allocatedjobdetails.IsCompelete == true)
{
iscompletedjobcount += 1;
}
}
}
if (iscompletedjobcount < allitemcount)
{
lstArtWorkReqID.Add(art);
}
}
}
else
{
lstArtWorkReqID.Add(art);
}
}
}
else
{
var lstArt = ArtContext.artworkrequisitions.SqlQuery("SELECT *, count(ArtWorkName) awtname FROM artworkrequisition awt group by ArtWorkID having CreativeID = '" + i.CreativeID + "' and IsActive = 1").ToList();
foreach (artworkrequisition art in lstArt)
{
int isCompletedcount = 0, assigncount = 0, allitemcount = 0, jobassignedcount = 0, iscompletedjobcount = 0;
List<artworkrequisition> allartworkitems = SubArtContext.artworkrequisitions.Where(w => w.ArtWorkID == art.ArtWorkID && w.IsActive == true).ToList();
jobassignedcount = allartworkitems.Where(w => w.isAssigned == true).Count();
allitemcount = allartworkitems.Where(w => w.IsActive == true).Count();
if (jobassignedcount > 0)
{
if (jobassignedcount <= allitemcount)
{
foreach (artworkrequisition singleartitem in allartworkitems)
{
if (singleartitem.isAssigned != null && singleartitem.isAssigned == true)
{
job_assigned_employee allocatedjobdetails = SubArtContext.job_assigned_employee.Where(w => w.ItemJobReqID == singleartitem.Id && w.JobTypeID == 1 && w.JobReqNo == singleartitem.ArtWorkID && w.IsActive == true).FirstOrDefault();
if (allocatedjobdetails != null)
{
if (allocatedjobdetails.IsCompelete != null && allocatedjobdetails.IsCompelete == true)
{
iscompletedjobcount += 1;
}
}
}
}
if (iscompletedjobcount <= jobassignedcount && iscompletedjobcount != allitemcount)
{
lstArtWorkReqID.Add(art);
}
}
}
else
{
lstArtWorkReqID.Add(art);
}
// job_assigned_employee allocatedjobdetails = SubArtContext.job_assigned_employee.Where(w => w.ItemJobReqID == singleartitem.Id && w.JobTypeID == 1 && w.JobReqNo == singleartitem.ArtWorkID && w.IsActive == true).FirstOrDefault();
}
}
if (lstPhotoStockReq.Count() == 0)
{
var lstPhotoStock = ArtContext.photographyrequisitions.SqlQuery("SELECT * FROM photographyrequisition where CreativeNo = '" + i.CreativeID + "' and isActive = 1 and isApproved = 0 group by photographyReqNo").ToList();
foreach (photographyrequisition objphot in lstPhotoStock)
{
lstPhotoStockReq.Add(objphot);
}
}
else
{
var lstPhotoStock = ArtContext.photographyrequisitions.SqlQuery("SELECT * FROM photographyrequisition where CreativeNo = '" + i.CreativeID + "' and isActive = 1 and isApproved = 0 group by photographyReqNo").ToList();
foreach (photographyrequisition objphot in lstPhotoStock)
{
lstPhotoStockReq.Add(objphot);
}
}
if (lstInterActiveWorkReq.Count() == 0)
{
var lstinteractivereq = ArtContext.interactiverequisitions.SqlQuery("SELECT * FROM interactiverequisition where CreativeID = '" + i.CreativeID + "' and isActive = 1 group by InteractiveID").ToList();
foreach (interactiverequisition objInter in lstinteractivereq)
{
lstInterActiveWorkReq.Add(objInter);
}
}
else
{
var lstinteractivereq = ArtContext.interactiverequisitions.SqlQuery("SELECT * FROM interactiverequisition where CreativeID = '" + i.CreativeID + "' and isActive = 1 group by InteractiveID").ToList();
foreach (interactiverequisition objInter in lstinteractivereq)
{
lstInterActiveWorkReq.Add(objInter);
}
}
if (lstTransWorkReq.Count() == 0)
{
var lstTranslatereq = ArtContext.translationrequisitions.SqlQuery("SELECT * FROM translationrequisition where creativeID = '" + i.CreativeID + "' and isActive = 1 and isApproved = 0 group by TransReqNo").ToList();
foreach (translationrequisition objTrans in lstTranslatereq)
{
lstTransWorkReq.Add(objTrans);
}
}
else
{
var lstTranslatereq = ArtContext.translationrequisitions.SqlQuery("SELECT * FROM translationrequisition where creativeID = '" + i.CreativeID + "' and isActive = 1 and isApproved = 0 group by TransReqNo").ToList();
foreach (translationrequisition objTrans in lstTranslatereq)
{
lstTransWorkReq.Add(objTrans);
}
}
if (lstPrintProductionReq.Count() == 0)
{
var lstPrintProductReq = ArtContext.printproductionrequisitions.SqlQuery("SELECT * FROM printproductionrequisition where CampaingNo = '" + i.CreativeID + "' and isActive = 1 and isApproved = 0 group by PrintProductionReqID").ToList();
foreach (printproductionrequisition objPrint in lstPrintProductReq)
{
lstPrintProductionReq.Add(objPrint);
}
}
else
{
var lstPrintProductReq = ArtContext.printproductionrequisitions.SqlQuery("SELECT * FROM printproductionrequisition where CampaingNo = '" + i.CreativeID + "' and isActive = 1 and isApproved = 0 group by PrintProductionReqID").ToList();
foreach (printproductionrequisition objPrint in lstPrintProductReq)
{
lstPrintProductionReq.Add(objPrint);
}
}
if (lstFilmProductionReq.Count() == 0)
{
var lstFilmproductionReq = ArtContext.filmproductionrequisitions.SqlQuery("SELECT * FROM filmproductionrequisition where CampaignNo = '" + i.CreativeID + "' and isActive = 1 and isApproved = 0 group by FilmProductionReqID").ToList();
foreach (filmproductionrequisition objFilm in lstFilmproductionReq)
{
lstFilmProductionReq.Add(objFilm);
}
}
else
{
var lstFilmproductionReq = ArtContext.filmproductionrequisitions.SqlQuery("SELECT * FROM filmproductionrequisition where CampaignNo = '" + i.CreativeID + "' and isActive = 1 and isApproved = 0 group by FilmProductionReqID").ToList();
foreach (filmproductionrequisition objFilm in lstFilmproductionReq)
{
lstFilmProductionReq.Add(objFilm);
}
}
if (lstRetainerFeeReq.Count() == 0)
{
var lstRetainerfeereq = ArtContext.retainerfeerequisitions.SqlQuery("SELECT * FROM retainerfeerequisition where CampaignNo = '" + i.CreativeID + "' and isActive = 1 and isApproved = 0 group by RetainerfeeReqID").ToList();
foreach (retainerfeerequisition objRetainer in lstRetainerfeereq)
{
lstRetainerFeeReq.Add(objRetainer);
}
}
else
{
var lstRetainerfeereq = ArtContext.retainerfeerequisitions.SqlQuery("SELECT * FROM retainerfeerequisition where CampaignNo = '" + i.CreativeID + "' and isActive = 1 and isApproved = 0 group by RetainerfeeReqID").ToList();
foreach (retainerfeerequisition objRetainer in lstRetainerfeereq)
{
lstRetainerFeeReq.Add(objRetainer);
}
}
if (lstStockImageReq.Count() == 0)
{
var lstStockImagereq = ArtContext.stockimagerequisitions.SqlQuery("SELECT * FROM stockimagerequisition where CampaginNo = '" + i.CreativeID + "' and IsActive = 1 and isApproved = 0 and statusId = 4 group by StockImageReqNo").ToList();
foreach (stockimagerequisition objStockImage in lstStockImagereq)
{
lstStockImageReq.Add(objStockImage);
}
}
else
{
var lstStockImagereq = ArtContext.stockimagerequisitions.SqlQuery("SELECT * FROM stockimagerequisition where CampaginNo = '" + i.CreativeID + "' and IsActive = 1 and isApproved = 0 and statusId = 4 group by StockImageReqNo").ToList();
foreach (stockimagerequisition objStockImage in lstStockImagereq)
{
lstStockImageReq.Add(objStockImage);
}
}
if (lstSystemWorkReq.Count() == 0)
{
var lstSystemWork = ArtContext.systemworkrequisitions.SqlQuery("SELECT * FROM systemworkrequisition where CampaignNo = '" + i.CreativeID + "' and IsActive = 1 and StatusID = 4 group by SystemReqNo").ToList();
foreach (systemworkrequisition systemwork in lstSystemWork)
{
lstSystemWorkReq.Add(systemwork);
}
}
else
{
var lstSystemWork = ArtContext.systemworkrequisitions.SqlQuery("SELECT * FROM systemworkrequisition where CampaignNo = '" + i.CreativeID + "' and IsActive = 1 and StatusID = 4 group by SystemReqNo").ToList();
foreach (systemworkrequisition systemwork in lstSystemWork)
{
lstSystemWorkReq.Add(systemwork);
}
}
if (lstOthersReq.Count() == 0)
{
var lstOthersreq = ArtContext.othersrequisitions.SqlQuery("SELECT * FROM othersrequisition where CampaignNo = '" + i.CreativeID + "' and isActive = 1 and isApproved = 0 group by OtherReqNo").ToList();
foreach (othersrequisition objOthers in lstOthersreq)
{
lstOthersReq.Add(objOthers);
}
}
else
{
var lstOthersreq = ArtContext.othersrequisitions.SqlQuery("SELECT * FROM othersrequisition where CampaignNo = '" + i.CreativeID + "' and isActive = 1 and isApproved = 0 group by OtherReqNo").ToList();
foreach (othersrequisition objOthers in lstOthersreq)
{
lstOthersReq.Add(objOthers);
}
}
if (lstTenderReq.Count() == 0)
{
var lstTendersreq = ArtContext.tender_noticerequisition.SqlQuery("SELECT *, count(TenderNoticeName) as tendername FROM tender_noticerequisition as awt group by tendernoticeID having CampaignNo = '" + i.CreativeID + "' and IsActive = 1").ToList();
foreach (tender_noticerequisition objTenders in lstTendersreq)
{
lstTenderReq.Add(objTenders);
}
}
else
{
var lstTendersreq = ArtContext.tender_noticerequisition.SqlQuery("SELECT *, count(TenderNoticeName) as tendername FROM tender_noticerequisition as awt group by tendernoticeID having CampaignNo = '" + i.CreativeID + "' and IsActive = 1").ToList();
foreach (tender_noticerequisition objTenders in lstTendersreq)
{
lstTenderReq.Add(objTenders);
}
}
if (lstillustrationreq.Count() == 0)
{
var lstillustrareq = ArtContext.illustrationrequisitions.SqlQuery("SELECT *,count(descriptions) FROM illustrationrequisition where CampaignNo = '" + i.CreativeID + "' and isActive = 1 and isApproved = 0 group by IllustrationNo").ToList();
foreach (illustrationrequisition objillustra in lstillustrareq)
{
lstillustrationreq.Add(objillustra);
}
}
else
{
var lstillustrareq = ArtContext.illustrationrequisitions.SqlQuery("SELECT * FROM illustrationrequisition where CampaignNo = '" + i.CreativeID + "' and isActive = 1 and isApproved = 0 group by IllustrationNo").ToList();
foreach (illustrationrequisition objillustra in lstillustrareq)
{
lstillustrationreq.Add(objillustra);
}
}
string strprojectClient = i.ProjectName + "-" + i.ClientName + "-" + i.branddesc;
ht.Add(i.CreativeID, strprojectClient);
k++;
break;
}
}
}我能得到这样的数据
活动编号：100001->工作编号：1001入职日期：2019年3月28日
1条答案
按热度按时间b91juud31#
我想如果你改变了：
像这样的
你可以稍微提高一下性能，因为据我所知，fluent版本
Where将作为对数据库的单独查询执行，而不是作为GROUPBY查询的一部分执行，从而导致数据库生成效率较低的执行计划。顺便说一句，我建议不要在sql和fluent linq查询语法之间混用，除非绝对必要。
希望有帮助！