I have a table that looks like this
I also have another table that provides the type of file (lookup table)
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