SQL query optimization help please

Question

TABLES (Simplified)

Media table

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

MultimediaGroup table

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

Got this query, which I know isn't optimal. I don't like the subquery in it.

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

Can anyone suggest a way to optimize it without the following subquery section:

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

I'm also thinking of using WITH (NOLOCK) in it just to speed it up as it runs pretty slow sometimes and has the potential to cause a server crash.

Answer 1

The section that's making you uneasy can be transformed into LEFT JOIN, like this:

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