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

2guxujil  于 2021-06-15  发布在  Mysql
关注(0)|答案(1)|浏览(379)

我知道这个问题已经被问过好几次了，但我一直在尝试从mysql表中多对多相关的多个表中获取数据。而且，每个表至少包含500000条记录。在这种情况下，如何加快数据检索过程？
虽然，我试图获得数据使用分组按屏幕准备数据，但它仍然工作得很慢。
在屏幕的控制器中，我使用了linq组。

  1. var lstclientId = (from c in DBContext.groupmasters.Where(w => w.isactive == true && w.UserID == userid)
  2.                    group c by c.ClientID into d
  3.                    select new
  4.                    {
  5.                        clientID = d.Key,
  6.                    });
  8. var lstJobTypeID = (from c in DBContext.jobrequisitions.Where(w => w.ISActive == true)
  9.                     group c by new { c.CreativeID, c.ProjectName, c.client.ClientName, c.brandmaster.branddesc } into d
  10.                     select new
  11.                     {
  12.                         CreativeID = d.Key.CreativeID,
  13.                         ProjectName = d.Key.ProjectName,
  14.                         ClientName = d.Key.ClientName,
  15.                         branddesc = d.Key.branddesc,
  16.                     }).OrderBy(o => o.CreativeID);
  17. List<jobrequisition> lstrequisition = new List<jobrequisition>();
  18. List<jobrequisition> lstrequisitionusers = new List<jobrequisition>();
  19. if ((Session["deptCode"]).ToString() == "QC" || (Session["deptCode"]).ToString() == "OP" || Convert.ToBoolean(Session["IsAdmin"]) == true)
  20. {
  21.     database2Entities JobEntities = new database2Entities();
  22.     lstrequisition = JobEntities.jobrequisitions.Where(w => w.ISActive == true).ToList();
  23.     lstrequisitionusers.AddRange(lstrequisition);
  24. }
  25. else
  26. {
  27.     foreach (var i in lstclientId)
  28.     {
  29.         database2Entities JobEntities = new database2Entities();
  30.         lstrequisition = JobEntities.jobrequisitions.Where(w => w.ISActive == true && w.ClientID == i.clientID).ToList();
  31.         lstrequisitionusers.AddRange(lstrequisition);
  32.     }
  33. }
  34. Hashtable ht = new Hashtable();
  35. int k = 0;
  36. foreach (var i in lstJobTypeID)
  37. {
  38.     foreach (jobrequisition objRequestion in lstrequisitionusers.OrderBy(o => o.CreativeID))
  39.     {
  40.         if (objRequestion.CreativeID == i.CreativeID)
  41.         {
  42.             database2Entities ArtContext = new database2Entities();
  43.             database2Entities SubArtContext = new database2Entities();
  44.             if (lstArtWorkReqID.Count() == 0)
  45.             {
  47.                 ///old code... 
  48.                 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();
  49.                 foreach (artworkrequisition art in lstArt)
  50.                 {
  51.                     int isCompletedcount = 0, assigncount = 0, allitemcount = 0, jobassignedcount = 0, iscompletedjobcount = 0;
  52.                     List<artworkrequisition> allartworkitems = SubArtContext.artworkrequisitions.Where(w => w.ArtWorkID == art.ArtWorkID && w.IsActive == true).ToList();
  53.                     jobassignedcount = allartworkitems.Where(w => w.isAssigned == true).Count();
  54.                     allitemcount = allartworkitems.Where(w => w.IsActive == true).Count();
  55.                     if (jobassignedcount > 0)
  56.                     {
  57.                         if (jobassignedcount <= allitemcount)
  58.                         {
  59.                             foreach (artworkrequisition singleartitem in allartworkitems)
  60.                             {
  61.                                 if (singleartitem.isAssigned == null && singleartitem.isAssigned == true)
  62.                                 {
  63.                                     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();
  64.                                     if (allocatedjobdetails.IsCompelete == true)
  65.                                     {
  66.                                         iscompletedjobcount += 1;
  67.                                     }
  68.                                 }
  69.                             }
  70.                             if (iscompletedjobcount < allitemcount)
  71.                             {
  72.                                 lstArtWorkReqID.Add(art);
  73.                             }
  75.                         }
  76.                     }
  77.                     else
  78.                     {
  79.                         lstArtWorkReqID.Add(art);
  80.                     }
  81.                 }
  83.             }
  84.             else
  85.             {
  86.                 var lstArt = ArtContext.artworkrequisitions.SqlQuery("SELECT *, count(ArtWorkName) awtname FROM artworkrequisition awt group by ArtWorkID having CreativeID = '" + i.CreativeID + "' and IsActive = 1").ToList();
  87.                 foreach (artworkrequisition art in lstArt)
  88.                 {
  89.                     int isCompletedcount = 0, assigncount = 0, allitemcount = 0, jobassignedcount = 0, iscompletedjobcount = 0;
  90.                     List<artworkrequisition> allartworkitems = SubArtContext.artworkrequisitions.Where(w => w.ArtWorkID == art.ArtWorkID && w.IsActive == true).ToList();
  91.                     jobassignedcount = allartworkitems.Where(w => w.isAssigned == true).Count();
  92.                     allitemcount = allartworkitems.Where(w => w.IsActive == true).Count();
  93.                     if (jobassignedcount > 0)
  94.                     {
  95.                         if (jobassignedcount <= allitemcount)
  96.                         {
  97.                             foreach (artworkrequisition singleartitem in allartworkitems)
  98.                             {
  99.                                 if (singleartitem.isAssigned != null && singleartitem.isAssigned == true)
  100.                                 {
  101.                                     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();
  102.                                     if (allocatedjobdetails != null)
  103.                                     {
  104.                                         if (allocatedjobdetails.IsCompelete != null && allocatedjobdetails.IsCompelete == true)
  105.                                         {
  106.                                             iscompletedjobcount += 1;
  107.                                         }
  108.                                     }
  109.                                 }
  110.                             }
  111.                             if (iscompletedjobcount <= jobassignedcount && iscompletedjobcount != allitemcount)
  112.                             {
  113.                                 lstArtWorkReqID.Add(art);
  114.                             }
  115.                         }
  116.                     }
  117.                     else
  118.                     {
  119.                         lstArtWorkReqID.Add(art);
  120.                     }
  122.                     //         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();
  124.                 }
  125.             }
  126.             if (lstPhotoStockReq.Count() == 0)
  127.             {
  128.                 var lstPhotoStock = ArtContext.photographyrequisitions.SqlQuery("SELECT * FROM photographyrequisition where CreativeNo = '" + i.CreativeID + "' and isActive = 1  and isApproved = 0 group by photographyReqNo").ToList();
  129.                 foreach (photographyrequisition objphot in lstPhotoStock)
  130.                 {
  131.                     lstPhotoStockReq.Add(objphot);
  132.                 }
  134.             }
  135.             else
  136.             {
  137.                 var lstPhotoStock = ArtContext.photographyrequisitions.SqlQuery("SELECT * FROM photographyrequisition where CreativeNo = '" + i.CreativeID + "' and isActive = 1  and isApproved = 0 group by photographyReqNo").ToList();
  138.                 foreach (photographyrequisition objphot in lstPhotoStock)
  139.                 {
  140.                     lstPhotoStockReq.Add(objphot);
  141.                 }
  142.             }
  143.             if (lstInterActiveWorkReq.Count() == 0)
  144.             {
  145.                 var lstinteractivereq = ArtContext.interactiverequisitions.SqlQuery("SELECT * FROM interactiverequisition where CreativeID = '" + i.CreativeID + "' and isActive = 1 group by InteractiveID").ToList();
  146.                 foreach (interactiverequisition objInter in lstinteractivereq)
  147.                 {
  148.                     lstInterActiveWorkReq.Add(objInter);
  149.                 }
  151.             }
  152.             else
  153.             {
  154.                 var lstinteractivereq = ArtContext.interactiverequisitions.SqlQuery("SELECT * FROM interactiverequisition where CreativeID = '" + i.CreativeID + "' and isActive = 1 group by InteractiveID").ToList();
  155.                 foreach (interactiverequisition objInter in lstinteractivereq)
  156.                 {
  157.                     lstInterActiveWorkReq.Add(objInter);
  158.                 }
  159.             }
  160.             if (lstTransWorkReq.Count() == 0)
  161.             {
  162.                 var lstTranslatereq = ArtContext.translationrequisitions.SqlQuery("SELECT * FROM translationrequisition where creativeID = '" + i.CreativeID + "' and isActive = 1 and isApproved = 0 group by TransReqNo").ToList();
  163.                 foreach (translationrequisition objTrans in lstTranslatereq)
  164.                 {
  165.                     lstTransWorkReq.Add(objTrans);
  166.                 }
  167.             }
  168.             else
  169.             {
  170.                 var lstTranslatereq = ArtContext.translationrequisitions.SqlQuery("SELECT * FROM translationrequisition where creativeID = '" + i.CreativeID + "' and isActive = 1  and isApproved = 0 group by TransReqNo").ToList();
  171.                 foreach (translationrequisition objTrans in lstTranslatereq)
  172.                 {
  173.                     lstTransWorkReq.Add(objTrans);
  174.                 }
  175.             }
  176.             if (lstPrintProductionReq.Count() == 0)
  177.             {
  178.                 var lstPrintProductReq = ArtContext.printproductionrequisitions.SqlQuery("SELECT * FROM printproductionrequisition where CampaingNo = '" + i.CreativeID + "' and isActive = 1 and isApproved = 0 group by PrintProductionReqID").ToList();
  179.                 foreach (printproductionrequisition objPrint in lstPrintProductReq)
  180.                 {
  181.                     lstPrintProductionReq.Add(objPrint);
  182.                 }
  183.             }
  184.             else
  185.             {
  186.                 var lstPrintProductReq = ArtContext.printproductionrequisitions.SqlQuery("SELECT * FROM printproductionrequisition where CampaingNo = '" + i.CreativeID + "' and isActive = 1 and isApproved = 0 group by PrintProductionReqID").ToList();
  187.                 foreach (printproductionrequisition objPrint in lstPrintProductReq)
  188.                 {
  189.                     lstPrintProductionReq.Add(objPrint);
  190.                 }
  191.             }
  193.             if (lstFilmProductionReq.Count() == 0)
  194.             {
  195.                 var lstFilmproductionReq = ArtContext.filmproductionrequisitions.SqlQuery("SELECT * FROM filmproductionrequisition where CampaignNo = '" + i.CreativeID + "' and isActive = 1 and isApproved = 0 group by FilmProductionReqID").ToList();
  196.                 foreach (filmproductionrequisition objFilm in lstFilmproductionReq)
  197.                 {
  198.                     lstFilmProductionReq.Add(objFilm);
  199.                 }
  200.             }
  201.             else
  202.             {
  203.                 var lstFilmproductionReq = ArtContext.filmproductionrequisitions.SqlQuery("SELECT * FROM filmproductionrequisition where CampaignNo = '" + i.CreativeID + "' and isActive = 1 and isApproved = 0 group by FilmProductionReqID").ToList();
  204.                 foreach (filmproductionrequisition objFilm in lstFilmproductionReq)
  205.                 {
  206.                     lstFilmProductionReq.Add(objFilm);
  207.                 }
  208.             }
  210.             if (lstRetainerFeeReq.Count() == 0)
  211.             {
  212.                 var lstRetainerfeereq = ArtContext.retainerfeerequisitions.SqlQuery("SELECT * FROM retainerfeerequisition where CampaignNo = '" + i.CreativeID + "' and isActive = 1 and isApproved = 0 group by RetainerfeeReqID").ToList();
  213.                 foreach (retainerfeerequisition objRetainer in lstRetainerfeereq)
  214.                 {
  215.                     lstRetainerFeeReq.Add(objRetainer);
  216.                 }
  217.             }
  218.             else
  219.             {
  220.                 var lstRetainerfeereq = ArtContext.retainerfeerequisitions.SqlQuery("SELECT * FROM retainerfeerequisition where CampaignNo = '" + i.CreativeID + "' and isActive = 1 and isApproved = 0 group by RetainerfeeReqID").ToList();
  221.                 foreach (retainerfeerequisition objRetainer in lstRetainerfeereq)
  222.                 {
  223.                     lstRetainerFeeReq.Add(objRetainer);
  224.                 }
  225.             }
  227.             if (lstStockImageReq.Count() == 0)
  228.             {
  229.                 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();
  230.                 foreach (stockimagerequisition objStockImage in lstStockImagereq)
  231.                 {
  232.                     lstStockImageReq.Add(objStockImage);
  233.                 }
  234.             }
  235.             else
  236.             {
  237.                 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();
  238.                 foreach (stockimagerequisition objStockImage in lstStockImagereq)
  239.                 {
  240.                     lstStockImageReq.Add(objStockImage);
  241.                 }
  242.             }
  244.             if (lstSystemWorkReq.Count() == 0)
  245.             {
  246.                 var lstSystemWork = ArtContext.systemworkrequisitions.SqlQuery("SELECT * FROM systemworkrequisition where CampaignNo = '" + i.CreativeID + "' and IsActive = 1 and StatusID = 4 group by SystemReqNo").ToList();
  247.                 foreach (systemworkrequisition systemwork in lstSystemWork)
  248.                 {
  249.                     lstSystemWorkReq.Add(systemwork);
  250.                 }
  251.             }
  252.             else
  253.             {
  254.                 var lstSystemWork = ArtContext.systemworkrequisitions.SqlQuery("SELECT * FROM systemworkrequisition where CampaignNo = '" + i.CreativeID + "' and IsActive = 1 and StatusID = 4 group by SystemReqNo").ToList();
  255.                 foreach (systemworkrequisition systemwork in lstSystemWork)
  256.                 {
  257.                     lstSystemWorkReq.Add(systemwork);
  258.                 }
  259.             }
  261.             if (lstOthersReq.Count() == 0)
  262.             {
  263.                 var lstOthersreq = ArtContext.othersrequisitions.SqlQuery("SELECT * FROM othersrequisition where CampaignNo = '" + i.CreativeID + "' and isActive = 1 and isApproved = 0 group by OtherReqNo").ToList();
  264.                 foreach (othersrequisition objOthers in lstOthersreq)
  265.                 {
  266.                     lstOthersReq.Add(objOthers);
  267.                 }
  268.             }
  269.             else
  270.             {
  271.                 var lstOthersreq = ArtContext.othersrequisitions.SqlQuery("SELECT * FROM othersrequisition where CampaignNo = '" + i.CreativeID + "' and isActive = 1 and isApproved = 0 group by OtherReqNo").ToList();
  272.                 foreach (othersrequisition objOthers in lstOthersreq)
  273.                 {
  274.                     lstOthersReq.Add(objOthers);
  275.                 }
  276.             }
  278.             if (lstTenderReq.Count() == 0)
  279.             {
  280.                 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();
  281.                 foreach (tender_noticerequisition objTenders in lstTendersreq)
  282.                 {
  283.                     lstTenderReq.Add(objTenders);
  284.                 }
  285.             }
  286.             else
  287.             {
  288.                 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();
  289.                 foreach (tender_noticerequisition objTenders in lstTendersreq)
  290.                 {
  291.                     lstTenderReq.Add(objTenders);
  292.                 }
  293.             }
  295.             if (lstillustrationreq.Count() == 0)
  296.             {
  297.                 var lstillustrareq = ArtContext.illustrationrequisitions.SqlQuery("SELECT *,count(descriptions) FROM illustrationrequisition where CampaignNo = '" + i.CreativeID + "' and isActive = 1 and isApproved = 0 group by IllustrationNo").ToList();
  298.                 foreach (illustrationrequisition objillustra in lstillustrareq)
  299.                 {
  300.                     lstillustrationreq.Add(objillustra);
  301.                 }
  302.             }
  303.             else
  304.             {
  305.                 var lstillustrareq = ArtContext.illustrationrequisitions.SqlQuery("SELECT * FROM illustrationrequisition where CampaignNo = '" + i.CreativeID + "' and isActive = 1 and isApproved = 0 group by IllustrationNo").ToList();
  306.                 foreach (illustrationrequisition objillustra in lstillustrareq)
  307.                 {
  308.                     lstillustrationreq.Add(objillustra);
  309.                 }
  310.             }
  312.             string strprojectClient = i.ProjectName + "-" + i.ClientName + "-" + i.branddesc;
  313.             ht.Add(i.CreativeID, strprojectClient);
  314.             k++;
  315.             break;
  316.         }
  317.     }
  318. }

我能得到这样的数据
活动编号：100001->工作编号：1001入职日期：2019年3月28日

mysqlLambdac#linqasp.net-mvc-5

1条答案

按热度按时间
doinxwow

doinxwow1#

我想如果你改变了：

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

像这样的

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

你可以稍微提高一下性能，因为据我所知，fluent版本 Where 将作为对数据库的单独查询执行，而不是作为GROUPBY查询的一部分执行，从而导致数据库生成效率较低的执行计划。
顺便说一句，我建议不要在sql和fluent linq查询语法之间混用，除非绝对必要。
希望有帮助！

赞(0）分享  回复(0）举报  2021-06-16
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com