0

I am running the following statement in SQL Server Studio 2008:

Use Systems

select subdirectory 
from [Systems].[dbo].[ScanningArchiveFileSystem] f 
where f.FileFlag = 1 
and SubDirectory not in
(
    Select m.strSchemeCode + N'\' + f.FileRef AS P3_Skem 
    from  AdminWIP_DS.dbo.fnTblMembers(NULL) AS m 
    RIGHT OUTER JOIN AdminWIP_DS.dbo.tblScannedItems f 
    ON m.intMemRef = f.intMemRef 
    AND m.intSystem = f.intSystem 
    where RIGHT(m.strSchemeCode + N'\' + f.FileRef,5) like '%.%'

    union

    select (m.skem COLLATE Latin1_General_CI_AS  + '\' + f.strPath COLLATE latin1_General_CI_AS ) as filepath
    from AdminWIP_DS.dbo.tbl_Client_BAY_MemberFiles f 
    join p3Test.dbo.MEMBERS m 
    on m.MEMNO = f.intMemRef 
    and RIGHT(f.strPath,3) = 'pdf'
)

Each select statement runs with the following times:

select subdirectory 
from [Systems].[dbo].[ScanningArchiveFileSystem] f 
where f.FileFlag = 1 

Rows 247769, Time 1 Secs

Select m.strSchemeCode + N'\' + f.FileRef AS P3_Skem 
from AdminWIP_DS.dbo.fnTblMembers(NULL) AS m 
RIGHT OUTER JOIN AdminWIP_DS.dbo.tblScannedItems f 
ON m.intMemRef = f.intMemRef 
AND m.intSystem = f.intSystem 
where RIGHT(m.strSchemeCode + N'\' + f.FileRef,5) like '%.%'

Rows 330442, Time 6 Secs

select (m.skem COLLATE Latin1_General_CI_AS  + '\' + f.strPath COLLATE latin1_General_CI_AS ) as filepath
from AdminWIP_DS.dbo.tbl_Client_BAY_MemberFiles f 
join p3Test.dbo.MEMBERS m 
on m.MEMNO = f.intMemRef 
and RIGHT(f.strPath,3) = 'pdf')

Rows 63133 0 Secs

and finally the Union as a whole runs as Rows 237660, Time 14 secs.

When I bring them all together for the "Not in" where clause CPU usage on the SQL Server goes up to 100% and so far I let it run for 20 mins and it didn't complete.

I'm not a SQL whiz so I was wondering if anyone could help with why the whole statement can take significantly longer.

  • 1
    Attaching the execution plan for the full query would likely help. – Michael Green Jul 25 '18 at 12:52
  • 1
    Also remember that 'UNION' outputs only distinct rows, so it more expensive than 'UNION ALL' – David Browne - Microsoft Jul 25 '18 at 12:56
  • @ David Browne Isn't SQL Server smart enough to do NOT sort the rows within NOT IN? – sepupic Jul 25 '18 at 13:02
  • 2
    @sepupic in order to make the result set unique / distinct by the UNION, it has to sort them. – scsimon Jul 25 '18 at 13:10
  • @scsimon Did you look at execution plan for "...not in(....union)..." and "...not in(...union all)" ? Did you see any difference? Even if you ask server to first sort the result, it's smart enough to not to do it – sepupic Jul 25 '18 at 13:17
0

You should check your execution plan.

It can be that cardinality estimation for the last Anti Semi Join is wrong: you join 247.769 rows with 237.660 rows and there should be Hash Join but if it underestimated cardinality (AdminWIP_DS.dbo.fnTblMembers()) it used NL so you've got your 20 minutes.

Another thing to consider here is to eliminate right join:

You concatenate 2 strings and if one of them is null you'll get null as the result.

  • If you cannot see execution plan, you can check if it's your case this way: save your select from AdminWIP_DS.dbo.fnTblMembers(NULL) into #tmp, i.e. do select ...into #tmp, then add the rows from from AdminWIP_DS.dbo.tbl_Client_BAY_MemberFiles in the same table (insert into #tmp select from AdminWIP_DS.dbo.tbl_Client_BAY_MemberFiles) and then re-run your query against this #tmp table: ...where SubDirectory not in (select SubDirectory from #tmp), this way server will know the cardinality of #tmp and will choose the correct join type – sepupic Jul 26 '18 at 6:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.