refactor: Allow SQL to do recursive search for files in subfolders#6582
refactor: Allow SQL to do recursive search for files in subfolders#6582valadas merged 3 commits intodnnsoftware:developfrom
Conversation
|
@timi-ty Thank you so much for such a detailed submission, we will be sure to get this reviewed as quickly as possible |
bdukes
left a comment
There was a problem hiding this comment.
This is a great contribution, thanks for the robust write up and investigation.
| files.AddRange(this.SearchFiles(subFolder, regex, true)); | ||
| } | ||
| } | ||
| return fileCollection.Where(f => regex.IsMatch(f.FileName)).Cast<IFileInfo>(); |
There was a problem hiding this comment.
This method used to return a List and now it returns an IEnumerable. In the usages within DNN itself, this appears to be a performance boost, since the two callers do sorting and then materialize the results into a list (thus, this change avoids materializing the list an extra time).
It's possible that 3rd party usages of this method will be less performant if there were reusing the returned collection, but I think the benefit it higher than the drawback.
Co-authored-by: Brian Dukes <bdukes@engagesoftware.com>
valadas
left a comment
There was a problem hiding this comment.
It looks good to me, I would just like to give this a test with folder providers before merging
|
Ok, I just tested with an Azure folder provider with a lot of subfoldes and file and performance was great. Merci. Thanks @timi-ty |
Summary
Eliminates excessive database load during file searches by making a single recursive call instead of one per sub-folder.
FolderManager.SearchFilesProblem statement
On portals with deep folder hierarchies every search triggers N ×
dbo.GetFilescalls (N = folder count). Though each query costs < 35 ms CPU, thousands executed serially drive SQL Server to 100 % CPU and users wait ~1 minute.Implementation details
recursiveflag toGetFiles.Performance results
GetFilesTest plan
Evidence
Before Fix
DNN_Search_For_d_57_secs_original.mp4
After Fix
DNN_Search_For_d_2_secs.mp4