如何提高SQL查询性能（相关子查询）？

I Would like to write the below query in a better & efficient way any help?

我想以更好,更有效的方式编写以下查询任何帮助?

SELECT   a.assetnum as Asset,
         a.assettag as Asset_Tag,
         a.manufacturer as Manufacturer,
         a.serialnum as Serial,
         a.description as Description,
         (
             SELECT CASE  a.isrunning
                    WHEN  1 
                    THEN  'Operational' 
                    WHEN  0 
                    THEN  'Down' 
                    END
         ) AS  Condition ,
         l.kbs_loctag as Location,
         (
             SELECT TOP 1 wo.wonum 
             FROM   workorder wo 
             WHERE  wo.assetnum = a.assetnum
                    and wo.worktype = 'UN'
             ORDER BY wo.reportdate DESC
         ) AS Last_Workorder,
         (
             SELECT wo.statusdate 
             FROM   workorder wo 
             WHERE  wo.wonum IN
                    (
                        SELECT   top 1 wo.wonum 
                        FROM     workorder wo
                        WHERE    wo.assetnum = a.assetnum
                                 AND wo.worktype = 'UN'
                        ORDER BY wo.reportdate DESC
                    )
         ) AS Last_Status_Date,
         (
             SELECT top 1 lt.memo
             FROM   labtrans lt
             WHERE  lt.assetnum = a.assetnum 
                    AND lt.transtype = 'REPAIR'
             ORDER BY lt.transdate DESC
       ) AS Action
FROM   asset a 
       LEFT OUTER JOIN locations l
           ON a.location = l.location
WHERE  (
           a.description like '%WASH%' 
           or a.description LIKE '%DRYER%'
       )
ORDER BY  l.location, 
       a.description

1 个解决方案

#1

In most cases I prefer to use APPLY operator instead of correlated subquery.

在大多数情况下,我更喜欢使用APPLY运算符而不是相关子查询。

In your case I would suggest the next solution:

在你的情况下,我会建议下一个解决方案:

SELECT   a.assetnum as Asset,
         a.assettag as Asset_Tag,
         a.manufacturer as Manufacturer,
         a.serialnum as Serial,
         a.description as Description,
         CASE  a.isrunning
             WHEN  1 THEN  'Operational' 
             WHEN  0 THEN  'Down' 
         END AS  Condition,
         l.kbs_loctag as Location,
         wo.wonum AS Last_Workorder,
         wo.statusdate AS Last_Status_Date,
         lt.memo AS Action
FROM   asset a 
       LEFT OUTER JOIN locations l ON a.location = l.location
       OUTER APPLY (
             SELECT TOP 1 wonum, statusdate
             FROM   workorder 
             WHERE  assetnum = a.assetnum
                    and worktype = 'UN'
             ORDER BY reportdate DESC) AS wo
       OUTER APPLY (
             SELECT top 1 memo
             FROM   labtrans
             WHERE  assetnum = a.assetnum 
                    AND transtype = 'REPAIR'
             ORDER BY transdate DESC) AS lt

WHERE  (
           a.description like '%WASH%' 
           or a.description LIKE '%DRYER%'
       )
ORDER BY  l.location, a.[description]

BTW - you can find amazing video lesson (from Itzik Ben-Gan)about using APPLY Operator here.

顺便说一句 - 你可以在这里找到关于使用APPLY Operator的精彩视频课程(来自Itzik Ben-Gan)。

#1