--Qtime-帆软报表-230530 优化查询速度 ---查询T1 IF OBJECT_ID('tempdb..#TEMPDATAT1') IS NOT NULL --SELECT * FROM #TEMPDATAT1 DROP TABLE #TEMPDATAT1 SELECT WorkOrderNo,PanelCode,SideIndex,Process,MIN(CREATETIME) AS T1 INTO #TEMPDATAT1 FROM TRS_Busi_StationTrace WITH (nolock) WHERE 1=1 AND CreateTime > '${StartTime}' AND CreateTime < '${StopTime}' AND MachineType ='${Machinetype}' AND SideIndex ='${BeginSide}' AND Process ='${BeginStation}' ${if(len(WorkOrderNo) == 0,"","and WorkOrderNo = '" + WorkOrderNo + "'")} ${if(len(PanelCode) == 0,"","and PanelCode = '" + PanelCode + "'")} ${if(len(Config) == 0,"","and Config = '" + Config + "'")} GROUP BY PanelCode,SideIndex,Process,WorkOrderNo ---查询T2 IF OBJECT_ID('tempdb..#TEMPDATAT2') IS NOT NULL --SELECT * FROM #TEMPDATAT2 DROP TABLE #TEMPDATAT2 SELECT WorkOrderNo,PanelCode,SideIndex,Process,MIN(CREATETIME) AS T2 INTO #TEMPDATAT2 FROM TRS_Busi_StationTrace WITH (nolock) WHERE 1=1 AND CreateTime > '${StartTime}' AND CreateTime < '${StopTime}' AND MachineType ='${Machinetype}' AND SideIndex ='${StopSide}' AND Process ='${StopStation}' ${if(len(WorkOrderNo) == 0,"","and WorkOrderNo = '" + WorkOrderNo + "'")} ${if(len(PanelCode) == 0,"","and PanelCode = '" + PanelCode + "'")} ${if(len(Config) == 0,"","and Config = '" + Config + "'")} GROUP BY PanelCode,SideIndex,Process,WorkOrderNo SELECT a.WorkOrderNo,a.PanelCode,a.SideIndex as StartSide,a.Process as StartProcess,a.T1,B.SideIndex AS StopSide,b.Process as StopProcess,b.T2,DATEDIFF(mi,A.T1, B.T2) AS DIFFMin FROM #TEMPDATAT1 a ,#TEMPDATAT2 b WHERE a.PanelCode =b.PanelCode AND DATEDIFF(mi,A.T1, B.T2) >0 ${if(len(UpperLimit) == 0,"","and DATEDIFF(mi,A.T1, B.T2) >= '" + UpperLimit + "'")} ORDER BY T1

为了优化这个 SQL，可以考虑以下几个方面：

1. 索引优化：在 TRS_Busi_StationTrace 表中创建适当的索引，以便根据查询中的过滤条件快速定位到所需的数据。例如，可以创建以下索引：CreateTime、MachineType、SideIndex、Process、WorkOrderNo、PanelCode 和 Config。

2. 避免使用临时表：在查询中使用了两个临时表 #TEMPDATAT1 和 #TEMPDATAT2，这会增加查询的开销。可以考虑使用子查询或者公用表表达式（CTE）来代替临时表，以减少查询的开销。

3. 参数化查询：在查询中使用了字符串拼接的方式来构造查询条件，这会存在 SQL 注入的风险，并且也会降低查询的性能。可以使用参数化查询来避免这个问题，并且也可以让查询计划更容易复用。

下面是一个优化后的 SQL 示例：

SELECT a.WorkOrderNo, a.PanelCode, a.SideIndex AS StartSide, a.Process AS StartProcess, a.T1, b.SideIndex AS StopSide, b.Process AS StopProcess, b.T2, DATEDIFF(mi, a.T1, b.T2) AS DIFFMin
FROM (
    SELECT WorkOrderNo, PanelCode, SideIndex, Process, MIN(CREATETIME) AS T1
    FROM TRS_Busi_StationTrace WITH (NOLOCK)
    WHERE CreateTime > @StartTime AND CreateTime < @StopTime AND MachineType = @MachineType AND SideIndex = @BeginSide AND Process = @BeginStation AND (@WorkOrderNo IS NULL OR WorkOrderNo = @WorkOrderNo) AND (@PanelCode IS NULL OR PanelCode = @PanelCode) AND (@Config IS NULL OR Config = @Config)
    GROUP BY PanelCode, SideIndex, Process, WorkOrderNo
) a
INNER JOIN (
    SELECT WorkOrderNo, PanelCode, SideIndex, Process, MIN(CREATETIME) AS T2
    FROM TRS_Busi_StationTrace WITH (NOLOCK)
    WHERE CreateTime > @StartTime AND CreateTime < @StopTime AND MachineType = @MachineType AND SideIndex = @StopSide AND Process = @StopStation AND (@WorkOrderNo IS NULL OR WorkOrderNo = @WorkOrderNo) AND (@PanelCode IS NULL OR PanelCode = @PanelCode) AND (@Config IS NULL OR Config = @Config)
    GROUP BY PanelCode, SideIndex, Process, WorkOrderNo
) b ON a.PanelCode = b.PanelCode
WHERE DATEDIFF(mi, a.T1, b.T2) > 0 AND (@UpperLimit IS NULL OR DATEDIFF(mi, a.T1, b.T2) >= @UpperLimit)
ORDER BY a.T1

注意，在这个示例中，我使用了参数化查询来代替字符串拼接，使用了内联视图来代替临时表，同时也添加了适当的索引来优化查询性能。