March 16, 2015 at 7:43 pm
Hello
I'm trying to optimize a query and have done the usual of ensuring that covering indexes / missing indexes are in place ...However I wanted to try to try to rewrite it to use JOINS rather than "NOT EXISTS" ... Can someone assist?
My attempts have resulted in the wrong result being returned 🙁
The query is :
SELECT SUM(content_size / 1024) AS totalsize
FROM dmr_content_sp AS dmr_content
WHERE (storage_id = N'2801e24080000100')
AND (NOT EXISTS
(
SELECT 1
FROM dmr_content_r
WHERE (parent_id IS NOT NULL)
AND (dmr_content.r_object_id = r_object_id)
)
)
AND (is_archived = 0)
Cheers
Tim
March 16, 2015 at 10:51 pm
Sorted ....
SELECT SUM(content_size / 1024) AS totalsize
FROM dmr_content_sp AS dmr_content
LEFT OUTER JOIN (SELECT r_object_id FROM dmr_content_r WHERE parent_id IS NOT NULL) T ON T.r_object_id = dmr_content.r_object_id
WHERE (storage_id = N'2801e24080000100')
AND T.r_object_id IS NULL
AND (is_archived = 0)
Cheers
March 17, 2015 at 2:43 am
However it's worth noting that: http://sqlinthewild.co.za/index.php/2010/03/23/left-outer-join-vs-not-exists/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 17, 2015 at 4:21 am
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply