Correlated subquery slow

Question

I have a table that looks like this

Directory nvarchar(max)
Extension nvarchar(10)
Length bigint

I also have another table that provides the type of file (lookup table)
extension nvarchar(10)
FileType nvarchar(50)

That being said I have a correlated subquery I wanted to migrate to a sproc that takes in the levels from the root and gives you back the directories and their file types at that specific level. There are about 400k rows in the main table and about 800 extensions in the lookup table.

The query is this:

declare @levelsFromRoot as int = 7
--declare @auditName as varchar(max) = 'Aetna'

select
    ef.Type,
    sf.Directory,
    (select count(distinct part)
     FROM dbo.vwAuditView
     cross APPLY dbo.SplitPath( substring([Directory],1,LEN([Directory])

                        -CHARINDEX('\',REVERSE([Directory]))) ,'\') AS Results
                                where Directory = sf.Directory) as [LevelsFromRoot]
from dbo.FS02V_SourceFiles sf
inner join dbo.ExtensionFix ef
    on sf.Extension = ef.Extension
Where (select count(distinct part)
     FROM dbo.vwAuditView
     cross APPLY dbo.SplitPath( substring([Directory],1,LEN([Directory])

                        -CHARINDEX('\',REVERSE([Directory]))) ,'\') AS Results
                                where Directory = sf.Directory) = @levelsFromRoot
order by Directory asc

The cross apply section in the subquery counts the levels in each path starting from the root. you should be able to pass in how many levels from the root and pass back a list of directories at that level and their corresponding file types. This query has already been running 25 minutes. It is returning data to me. Is there anything I can do to improve this one? I'm fairly new at correlated subqueries and figure I am doing something wrong here.

For clarity the view looks like this:

select    Directory
        --, ef.Extension
        , ef.Type
        , sum(Length) as [Size - Bytes]
from dbo.FS02V_SourceFiles sf
INNER JOIN dbo.ExtensionFix ef
    on sf.Extension = ef.Extension 
group by ef.type, Directory

Answer 1

The solution was to refactor the function that splits the directories and counts:

ALTER FUNCTION [dbo].[Split]
(
    @RowData nvarchar(max),
    @SplitOn nvarchar(5)
) 
RETURNS @RtnValue table 
(
    Id int identity(1,1),
    Data nvarchar(max)
) 
AS 
BEGIN 
    Declare @Cnt int
    Set @Cnt = 1

    While (Charindex(@SplitOn,@RowData)>0)
    Begin
        Insert Into @RtnValue (data)
        Select 
            Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

        Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
        Set @Cnt = @Cnt + 1
    End

    Insert Into @RtnValue (data)
    Select Data = ltrim(rtrim(@RowData))

    Return
END

This new version is a ton more efficient and returns data in about 18 seconds vs. 30+ minutes.