- 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 这是数据库中的查询代码;
-
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多秒,请问各位好心的大婶这段代码怎么优化,小弟感激不敬!新手求各位大婶指点!
linq多表连接查询速度很慢,但我在数据库查询分析器中却是秒查,请各位大侠帮我分析下
- 写回答
- 好问题 0 提建议
- 追加酬金
- 关注问题
- 邀请回答
-
5条回答 默认 最新
- threenewbee 2015-09-06 07:27关注
问题可能是你用了tolist
tolist会返回所有的记录,这很恐怖。尽量把where groupby join这些放在tolist之前执行。解决 无用评论 打赏 举报
悬赏问题
- ¥15 关于#matlab#的问题:在模糊控制器中选出线路信息,在simulink中根据线路信息生成速度时间目标曲线(初速度为20m/s,15秒后减为0的速度时间图像)我想问线路信息是什么
- ¥15 banner广告展示设置多少时间不怎么会消耗用户价值
- ¥16 mybatis的代理对象无法通过@Autowired装填
- ¥15 可见光定位matlab仿真
- ¥15 arduino 四自由度机械臂
- ¥15 wordpress 产品图片 GIF 没法显示
- ¥15 求三国群英传pl国战时间的修改方法
- ¥15 matlab代码代写,需写出详细代码,代价私
- ¥15 ROS系统搭建请教(跨境电商用途)
- ¥15 AIC3204的示例代码有吗,想用AIC3204测量血氧,找不到相关的代码。