2 one clause per line to avoid right-scrolling; typos.
source | link

Statment running significantly slower than compoantcomponent parts and causecauses 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 serverServer goes up to 100% and so far I let it run for 20mins20 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 llonger.

Statment running significantly slower than compoant parts and cause 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 20mins 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 l

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.

1
source | link

Statment running significantly slower than compoant parts and cause 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 20mins 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 l