SQL查询优化帮助请

Question

表格(简体)

媒体表

mediaID   description       multimediaGroupID   silolID   fcsPathHigh   fcsPathLow 
---------------------------------------------------------------------------------- 
1         media1            11                  6         blah          blah
2         media2            12                  6         blah          blah

多媒体组表

multimediaGroupID   multimediaGroup  isPollGroup
------------------------------------------------ 
11                  You be the ref   1 
12                  Try of the week  1

收到此查询，我知道这不是最佳查询。我不喜欢里面的子查询。

SELECT top 30 *
FROM media
WHERE (remoteMedia = 1) AND multimediaGroupID <> 13 AND siloID <> 16
AND siloID = 1 AND (fcsPathHigh like '%.flv' AND fcsPathLow like '%.flv')                     
AND (multimediagroupid is null or multimediagroupid not in
     (select multimediagroupid
     from multimediagroups
     where ispollgroup = 1))
ORDER BY dateUploaded DESC

任何人都可以建议一种没有以下子查询部分的优化方法:

and (multimediagroupid is null
or multimediagroupid not in 
     (select multimediagroupid
     from multimediagroups
     where ispollgroup = 1))

我也在考虑在其中使用 WITH (NOLOCK) 只是为了加快速度，因为它有时运行起来很慢并且有可能导致服务器崩溃。

Answer 1

让您感到不安的部分可以转换为 LEFT JOIN，如下所示:

SELECT TOP 30 m.*
FROM media m
  LEFT JOIN multimediagroups g ON m.multimediagroupid = g.multimediagroupid
    AND g.ispollgroup = 1
WHERE m.remoteMedia = 1
  AND m.multimediaGroupID <> 13
  AND m.siloID <> 16
  AND m.siloID = 1
  AND (m.fcsPathHigh like '%.flv' AND m.fcsPathLow like '%.flv')                     
  AND g.multimediagroupid IS NULL
ORDER BY m.dateUploaded DESC