# Correlated subquery slow

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


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.