带有innerjoin和子查询的Sql Query运行缓慢 [英] Sql Query with innerjoin and sub query Runs slow

问题描述

Select  j.Route,
   '' as Sequence,
   '' as Territory,
   '' as Region,
   t.TruckNo as [Truck No],
   dr.DriverName as [Driver Name],
   j.Ordernumber as [Invoice],
   j.CustomerName as [Customer Name],
   CONVERT(DATE,Cast(j.Planneddate as varchar(8)),112)as [Dispatch Date],
   d.DeliveryDate as [Delivery Date],
   ps.[POD DateTime] as [POD Return Date],
   Cast(datediff(day,CONVERT(DATE,Cast(j.Planneddate as varchar(8)),112),d.DeliveryDate)as varchar(8)) as [On Time Delivery KPI],
   Cast(datediff(day,d.DeliveryDate,ps.[POD DateTime])as varchar(8)) as [On Time POD KPI]
		
		
   from tbl_pms_JobOrders j 
   
      Inner Join tbl_pms_JobOrders_Trucks_Info jt on jt.JobID=j.JobID
      Inner Join tbl_pms_Trucks t on t.TruckID=jt.TruckID
      Inner Join tbl_pms_DeliveryInfo d on d.JobID=j.JobID
      Inner Join tbl_pms_Trucks_DriversMapping td on td.TruckID = jt.TruckID
      Inner Join tbl_pms_DriverInfo dr on dr.DriverID = td.DriverID
      Inner Join dbo.tbl_pms_POD_Submit ps on ps.JobID = j.JobID
			
      Where j.WarehouseID=@WarehouseID and
      j.Planneddate between @FromDate and @ToDate
      and jt.TruckID != 312
      and d.recno in (select max (recno) from tbl_pms_DeliveryInfo 
      where JobID in (select JobID from tbl_pms_JobOrders 
      where WarehouseID=@WarehouseID) group by JobID)

这是Sql查询，需要花费更多时间在日期之间执行..请优化此查询..





谢谢提前..

This is Sql query which is taking more time to execute between dates.. Please Optimize this query..


Thanks Advance..

推荐答案

正如Kornfeld Eliyahu Peter提到的，这几乎是不可能的......



我建议阅读查询效果 [ ^ ]。没有什么事情可以做，以增加查询执行时间。

As Kornfeld Eliyahu Peter had mentioned, it's almost impossible...

I would suggest to read about query performance[^]. There is few things to do to increase query execution time.

我不知道我是如何错过这个问题



我'使用CTE：

I don't know how I missed this question

I'd use a CTE:

 ;WITH maxrec as
 (
 select max (recno) AS maxrec from tbl_pms_DeliveryInfo di
      INNER JOIN  tbl_pms_JobOrders jo ON di.jobNo = jo.jobNo
          where jo.WarehouseID=@WarehouseID
 )

Select  j.Route,
      '' as Sequence,
      '' as Territory,
      '' as Region,
      t.TruckNo as [Truck No],
      dr.DriverName as [Driver Name],
      j.Ordernumber as [Invoice],
      j.CustomerName as [Customer Name],
      CONVERT(DATE,Cast(j.Planneddate as varchar(8)),112)as [Dispatch Date],
      d.DeliveryDate as [Delivery Date],
      ps.[POD DateTime] as [POD Return Date],
      Cast(datediff(day,CONVERT(DATE,Cast(j.Planneddate as varchar(8)),112),d.DeliveryDate)as varchar(8)) as [On Time Delivery KPI],
      Cast(datediff(day,d.DeliveryDate,ps.[POD DateTime])as varchar(8)) as [On Time POD KPI]


      from tbl_pms_JobOrders j

          Inner Join tbl_pms_JobOrders_Trucks_Info jt on jt.JobID=j.JobID
          Inner Join tbl_pms_Trucks t on t.TruckID=jt.TruckID
          Inner Join tbl_pms_DeliveryInfo d on d.JobID=j.JobID
          Inner Join tbl_pms_Trucks_DriversMapping td on td.TruckID = jt.TruckID
          Inner Join tbl_pms_DriverInfo dr on dr.DriverID = td.DriverID
          Inner Join dbo.tbl_pms_POD_Submit ps on ps.JobID = j.JobID
          INNER JOIN maxrec mr ON mr.maxrec = d.recno

          Where j.WarehouseID=@WarehouseID and
          j.Planneddate between @FromDate and @ToDate
          and jt.TruckID != 312;

这样可以摆脱两个级别的子查询，这很慢。



我还要确保PlannedDate有一个索引，而且WarehouseID也有一个索引。

This gets rid of two levels of subquery, which is what was slow.

I'd also make sure PlannedDate has an index, and WarehouseID has one, too.

这篇关于带有innerjoin和子查询的Sql Query运行缓慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！