问题描述

linq多表连接查询速度很慢，但我在数据库查询分析器中却是秒查，请各位大侠帮我分析下

1. with temp as
   (
   select (case when SHIFTSCHEDULEID is null then (case when TICKET_SELL_TYPE=2 then
   (select COMPANY_ID from TBL_YD_SHIFT where SHIFTNUMBER= (select SHIFTNUMBER from TBL_SP_TICKET where ticketid=a.REFUND_TICKET_ID)) else
   (select deptid from tbl_xt_user where userid=a.userid) end) else b.company_id end ) as deptid,
   a.userid,a.DAY, SELL_DATE,SELL_WAY,a.SHIFTNUMBER,ticketid,ticket_type,TICKET_STATE,TICKET_NO,IN_MONEY,out_money,REFUND_TICKET_ID
   from tbl_sp_ticket a left join TBL_YD_SHIFT b on a.SHIFTNUMBER=b.SHIFTNUMBER
   where TICKET_STATE not in(5,6)
   )
   select a.ticket_no,a.shiftnumber,a.DAY,a.STARTTIME,(select username from TBL_XT_USER where userid=a.USERID)as username,
   (select sitename from TBL_YD_SITE where siteid=a.ENDSITE)as sitename,
   (case when (a.TICKET_STATE =2 and b.TICKET_NO is null)then '未检'
   when (a.TICKET_STATE =2 and b.jp_state=0 and c.LD_NO is null)then '已检'
   when (a.TICKET_STATE =2 and b.jp_state=1 )then '退检'
   when (a.TICKET_STATE =2 and b.jp_state=0 and c.LD_NO is not null)then '已打路单'
   when a.TICKET_STATE =3 then '已退' when a.TICKET_STATE =5 then '破损'
   when a.TICKET_STATE =6 then '已废'end)as TicketState,
   (case a.TICKET_TYPE when 0 then '全票'when 1 then '半票'when 3 then'后付证'when 4 then '会员票' when 5 then '团体票'when 6 then '包车票'else '其它'end) as TicketType,
   a.IN_MONEY as priceJe,a.OUT_MONEY,
   isnull(c.ld_no,'')as Ld_No, a.SELL_DATE ,a.REFUND_TICKET_ID
   from tbl_sp_ticket a left join TBL_BB_JP b on a.TICKET_NO=b.TICKET_NO left join TBL_BB_WAYBILL c on b.LD_ID=c.ID
   这是数据库中的查询代码；
2. public List GetSpRecordMxInfo(int deptid, DateTime startRq, DateTime endRq)
   {
   List list = new List();
   using (var dbContext = new SKJTDBContext())
   {

           var spUsers = from u in dbContext.Set<User>()
                         select new
                         {
                             userid = u.USERID,
                             username = u.USERNAME,
                             usercode = u.USERCODE,
                             deptid = u.DEPTID,
                             zwgsid=u.USERUNIT
                         };
           var tempusers = spUsers.Where(u => u.zwgsid == deptid);
           //查询只属于指定站务公司下卖出的车票记录，通过班次号关联班次档案信息来判断
           var shifts = dbContext.Set<Shift>().Where(s => s.COMPANY_ID == deptid);
           if (shifts == null || shifts.Count() == 0) return null;
           var spRecords = (from sp in dbContext.Set<Sp_Ticket>().Where(
                               s => s.SELL_DATE <= endRq && s.SELL_DATE >= startRq).Where(
                s => !string.IsNullOrEmpty(s.SHIFTNUMBER))
                            join shift in shifts
                            on sp.SHIFTNUMBER equals shift.SHIFTNUMBER
                            select sp).ToList();
           if (spRecords == null || spRecords.Count() == 0) return null;
           var nullshiftRecords = dbContext.Set<Sp_Ticket>().Where(
               s => s.SELL_DATE <= endRq && s.SELL_DATE >= startRq).Where(
               s => string.IsNullOrEmpty(s.SHIFTNUMBER)).ToList();
           //单独处理班次号为空的情况
           if (nullshiftRecords != null || nullshiftRecords.Count() > 0)
           {
               foreach (var sp in nullshiftRecords)
               {
                   if (sp.TICKET_SELL_TYPE == TICKET_SELL_TYPE.退票)
                   {
                       var tempshift = dbContext.Set<Sp_Ticket>().Where(
                             r => r.TICKETID == sp.REFUND_TICKET_ID).FirstOrDefault().SHIFTNUMBER;
                       if (shifts.Any(s => s.SHIFTNUMBER == tempshift))
                           spRecords.Add(sp);
                       else continue;
                   }
                   else
                   {
                       if (tempusers.Any(r => r.userid == sp.USERID))
                           spRecords.Add(sp);
                       else continue;
                   }
               }
           }
           var results = from t in spRecords
                         join jp in dbContext.Set<Bb_Jp>().Where(w => w.OPER_TIME > startRq)
                         on t.TICKETID equals jp.TICKETID
                         into results1
                         from t_jp in results1.DefaultIfEmpty()
                         join ld in dbContext.Set<Bb_Waybill>().Where(w => w.OPER_TIME > startRq)
                         on t_jp == null ? new Bb_Jp().LD_ID : t_jp.LD_ID equals ld.ID
                         into results2
                         from t_jp_ld in results2.DefaultIfEmpty()
                         select new SpTicketRecordMx
                         {
                             TicketNO = t.TICKET_NO == null ? string.Empty : t.TICKET_NO,
                             ShiftNumber = t.SHIFTNUMBER == null ? string.Empty : t.SHIFTNUMBER,
                             StartRq = t.STARTTIME,
                             time = t.STARTTIME == null ? string.Empty : ((DateTime)t.STARTTIME).ToString("t"),
                             TicketState = t_jp == null ? t.TICKET_STATE.ToString() : (t_jp.JP_STATE == JP_STATE.已检 ? (t_jp.LD_ID == null ? "已检未打路单" : "已打路单") :(t.TICKET_STATE==TICKET_STATE.已退? "已退": "退检")),
                             EndSite = t.ENDSITE == null ? string.Empty : dbContext.Set<Site>().Where(
                             s => s.SITEID == t.ENDSITE).FirstOrDefault().SITENAME,
                             PriceJe = t.IN_MONEY,
                             Out_Money = t.OUT_MONEY,
                             RefoundTicketID = t.REFUND_TICKET_ID,
                             Ld_No = t_jp_ld == null ? string.Empty : t_jp_ld.LD_NO,
                             TicketType = t.TICKET_SELL_TYPE.ToString(),
                             UserName = spUsers.Where(u => u.userid == t.USERID).FirstOrDefault() == null ? "SPYA" :
                             spUsers.Where(u => u.userid == t.USERID).FirstOrDefault().username,
                             OperateRq = (DateTime)t.SELL_DATE
                         };
           // var liettemp = results.ToList();
           return results.ToList();
   
       }
   
   }
           这是c#写的代码，这代码运行时查询得20多秒，请问各位好心的大婶这段代码怎么优化，小弟感激不敬！新手求各位大婶指点！
   

解决方案

问题可能是你用了tolist
tolist会返回所有的记录，这很恐怖。尽量把where groupby join这些放在tolist之前执行。

解决方案二：

谢谢一楼的回答，这是我第一次在csdn发帖，不太熟悉额，我貌似只在两个位置用了两个tolist，一处是在代码注释语句“//单独处理班次号为空的情况”上面，另一处是方法最后，因为这是该方法的类型，结合你说的，我还是不大明白

解决方案三：

补充一下：不以为方便的组件，性能就一定好，在进行真正的数据库查询前后，linq还要执行很多的解释性代码和转换代码，而这些事会有个触发点，
就是ToListToArray....

解决方案四：

我个人感觉linq在多表链接查询时速度不是蛮理想，可能是我对linq的理解不够，希望各位大虾大婶指导下，谢谢了