Statment running significantly slower than component parts and causes high CPU usage 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.
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.