GlenCloncurry GlenCloncurry - 2 months ago 18
SQL Question

SQL Improve Performance Inner query

I am trying to improve the performance of my query by reducing the inner queries.

I will need the first one as it returns the ReasonTUM.
However, I would like to use a case with the ReasonTUM so that it returns a number as well.

How can I achieve this? I've tried referencing

mms.sMachineStateName
in the original query but it doesn't detect the column.

The only other way I can think of is to use another sub query but this is taking about 1min to return 300 rows due to the joins.

declare @ReportingStart datetime = '20160917 07:00'
declare @ReportingEnd datetime = '20160918 07:00'

SELECT
[sWorkcellDescription]
,[tStart]
,[dDurationSeconds]/60 as Duration_m
,[sStateDescription]
,datepart(hh,tstart) as myHr
,case when convert(time,tstart)< '07:00' then dateadd(dd,-1,convert(date,tstart)) else convert(date,tstart) end as myDate,
cast(dateadd(hour,datepart(hh,tstart),0) as datetime) as dispTime,
(
select top 1 mms.sMachineStateName
from OEEEvent oe
inner join RSBizWare.dbo.OEEConfigEvent ce on oe.lOEEConfigEventId = ce.lOEEConfigEventId
inner join RSBizWare.dbo.OEELOVCodeVal rs on oe.sStartVal = rs.sDescription and ce.lOEEIntRSSqlId=rs.lOEELOVCodeId
inner join RSBizWare.dbo.OEEStateConfig mms on rs.lMachineState = mms.lOEEStateConfigId
where qq.tStart between oe.tStart and oe.tEnd and oe.sPartId='Ore-Hoist'
order by qq.tStart asc
) as ReasonTUM,
(
select top 1 case
when mms.sMachineStateName = 'Production' then '1'
when mms.sMachineStateName = 'Unscheduled Production' then '2'
when mms.sMachineStateName = 'Idle time' then '3'
when mms.sMachineStateName = 'Opportune Maintenance' then '4'
when mms.sMachineStateName = 'Planned External Downtime' then '5'
when mms.sMachineStateName = 'Planned External Downtime' then '5'
when mms.sMachineStateName = 'Planned Maintenance Mechanical' then '5'
when mms.sMachineStateName = 'Planned Maintenance Electrical' then '6'
when mms.sMachineStateName = 'Unplanned Downtime Operational' then '7'
when mms.sMachineStateName = 'Unplanned Downtime Mechanical' then '8'
when mms.sMachineStateName = 'Unplanned Downtime Electrical' then '9'
else '99' end
from OEEEvent oe
inner join RSBizWare.dbo.OEEConfigEvent ce on oe.lOEEConfigEventId = ce.lOEEConfigEventId
inner join RSBizWare.dbo.OEELOVCodeVal rs on oe.sStartVal = rs.sDescription and ce.lOEEIntRSSqlId=rs.lOEELOVCodeId
inner join RSBizWare.dbo.OEEStateConfig mms on rs.lMachineState = mms.lOEEStateConfigId
where qq.tStart between oe.tStart and oe.tEnd and oe.sPartId='Ore-Hoist'
) as rank
FROM [RSBizWare].[dbo].[OEEQStateData] qq
where (tstart >= @ReportingStart and tStart < @ReportingEnd) and
sWorkcellDescription ='Hoisting' and dDurationSeconds > 5
order by tStart asc

Answer

Use OUTER APPLY to avoid calling the sub-query twice

SELECT [sworkcelldescription], 
       [tstart], 
       [ddurationseconds] / 60                                  AS Duration_m, 
       [sstatedescription], 
       Datepart(hh, tstart)                                     AS myHr, 
       CASE 
         WHEN CONVERT(TIME, tstart) < '07:00' THEN Dateadd(dd, -1, 
                                                   CONVERT(DATE, tstart 
                                                   )) 
         ELSE CONVERT(DATE, tstart) 
       END                                                      AS myDate, 
       Cast(Dateadd(hour, Datepart(hh, tstart), 0) AS DATETIME) AS dispTime, 
       OA.reasontum, 
       OA.[rank] 
FROM   [RSBizWare].[dbo].[oeeqstatedata] qq 
       OUTER apply (SELECT TOP 1 mms.smachinestatename, 
                                 CASE mms.smachinestatename 
                                   WHEN 'Production' THEN '1' 
                                   WHEN 'Unscheduled Production' THEN '2' 
                                   WHEN 'Idle time' THEN '3' 
                                   WHEN 'Opportune Maintenance' THEN '4' 
                                   WHEN 'Planned External Downtime' THEN '5' 
                                   WHEN 'Planned External Downtime' THEN '5' 
                                   WHEN 'Planned Maintenance Mechanical' THEN '5' 
                                   WHEN 'Planned Maintenance Electrical' THEN '6' 
                                   WHEN 'Unplanned Downtime Operational' THEN '7' 
                                   WHEN 'Unplanned Downtime Mechanical' THEN '8' 
                                   WHEN 'Unplanned Downtime Electrical' THEN '9' 
                                   ELSE '99' 
                                 END AS [Rank] 
                    FROM   oeeevent oe 
                           INNER JOIN rsbizware.dbo.oeeconfigevent ce 
                                   ON oe.loeeconfigeventid = 
                                      ce.loeeconfigeventid 
                           INNER JOIN rsbizware.dbo.oeelovcodeval rs 
                                   ON oe.sstartval = rs.sdescription 
                                      AND ce.loeeintrssqlid = rs.loeelovcodeid 
                           INNER JOIN rsbizware.dbo.oeestateconfig mms 
                                   ON rs.lmachinestate = mms.loeestateconfigid 
                    WHERE  qq.tstart BETWEEN oe.tstart AND oe.tend 
                           AND oe.spartid = 'Ore-Hoist' 
                    ORDER  BY qq.tstart ASC) OA 
WHERE  ( tstart >= @ReportingStart 
         AND tstart < @ReportingEnd ) 
       AND sworkcelldescription = 'Hoisting' 
       AND ddurationseconds > 5 
ORDER  BY tstart ASC 
Comments