c# - 尝试从非常大的数据中从mysql中的多个相关表检索数据

Question

我知道这个问题已经被问过好几次了，但是我一直在尝试从 mysql 表中的许多相关的多个表中获取数据。此外，每个表至少包含 500000 条记录。在这种情况下如何加快数据检索过程？

虽然，我尝试使用 group by 来获取数据以根据屏幕准备数据，但它仍然运行得很慢。

在屏幕 Controller 中，我通过使用 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;
        }
    }
}

我能够获取这样的数据

campaign no :100001 -> job no: 1001 entry date: 28-03-2019

Answer 1

我认为如果你改变:

from c in DBContext.jobrequisitions.Where(w => w.ISActive == true)

类似于

from c in DBContext.jobrequisitions
where c.ISActive == true

您可能会稍微提高性能，因为据我所知，流畅版本 Where 将作为对数据库的单独查询执行，而不是作为按查询分组的一部分，从而导致数据库生成效率较低的执行计划。

顺便说一句，除非绝对必要，否则我建议不要混合使用 SQL 和流畅的 LINQ 查询语法。

希望对你有帮助!