From 2051c758477b68fea0f2d10b75d0d54430ab096a Mon Sep 17 00:00:00 2001
From: Jake Mannens <jake@asger.xyz>
Date: Mon, 28 Aug 2023 23:37:48 +1000
Subject: Massive DB performance improvement when filtering NSFW content

---
 Pages/Component/TagSelectDialog.razor | 14 +++++---
 Pages/TagDefinitions.razor            | 60 ++++++++++++++++++++++++-----------
 2 files changed, 51 insertions(+), 23 deletions(-)

(limited to 'Pages')

diff --git a/Pages/Component/TagSelectDialog.razor b/Pages/Component/TagSelectDialog.razor
index 3c30305..e6e78a4 100644
--- a/Pages/Component/TagSelectDialog.razor
+++ b/Pages/Component/TagSelectDialog.razor
@@ -84,14 +84,20 @@
 
         var selected = SelectedTags.Select(td => td.Guid);
 
+        int[] nsfwTags = Array.Empty<int>();
+        if(!userService.ShowNsfw)
+            nsfwTags = tagService.TagsThatImply(HBContext.NsfwTag)
+                .Select(td => td.ObjectId)
+                .ToArray();
+
         tagDefinitions = db.TagDefinitions
+            .Include(td => td.ImplicitTags)
             .Where(td => td.Source == TagSource.UserTag)
             .OrderBy(td => td.Name)
             .AsEnumerable()
-            .Where(td => userService.ShowNsfw || !tagService
-                .GetAllTags(td)
-                .Select(e => e.tagDefinition.Guid)
-                .Contains(HBContext.NsfwTag))
+            .Where(td => userService.ShowNsfw || !td.ImplicitTags
+                .IntersectBy(nsfwTags, td => td.ObjectId)
+                .Any())
             .Select(td => new Tuple<TagDefinition, bool>(
                 td,
                 selected.Contains(td.Guid)).ToValueTuple())
diff --git a/Pages/TagDefinitions.razor b/Pages/TagDefinitions.razor
index b59561b..7ff870a 100644
--- a/Pages/TagDefinitions.razor
+++ b/Pages/TagDefinitions.razor
@@ -96,7 +96,7 @@
     OnSubmit=SetImplicitTags
     @ref=implicitTagDialog />
  
-@code {
+    @code {
     private Dialog          createTagDialog;
     private Dialog          deleteTagDialog;
     private Dialog          editTagDialog;
@@ -110,28 +110,41 @@
     private TagDefinition? toEdit;
     private TagDefinition? toEditImplicit;
 
-    private TagDefinition[] tagDefinitions =>
-        dbFactory.CreateDbContext().TagDefinitions
-            .Include(td => td.ImplicitTags)
-            .Where(td => td.Source == TagSource.UserTag)
-            .OrderBy(td => td.Namespace)
-            .ThenBy(td => td.Name)
-            .AsEnumerable()
-            .Where(td => userService.ShowNsfw || !tagService
-                .GetAllTags(td)
-                .Select(td => td.tagDefinition.Guid)
-                .Contains(HBContext.NsfwTag))
-            .ToArray();
+    private TagDefinition[] tagDefinitions;
 
-    private string?[] tagNamespaces => tagDefinitions
-        .Select(td => td.Namespace)
-        .Order()
-        .Distinct()
-        .ToArray();
+    private string?[] tagNamespaces;
 
     protected override void OnInitialized() =>
         userService.ShowNsfwChanged += ShowNsfwChanged;
 
+    protected override void OnParametersSet() =>
+        LoadTags();
+
+    private void LoadTags() {
+        int[] nsfwTags = Array.Empty<int>();
+        if(!userService.ShowNsfw)
+            nsfwTags = tagService.TagsThatImply(HBContext.NsfwTag)
+                .Select(td => td.ObjectId)
+                .ToArray();
+
+        tagDefinitions = dbFactory.CreateDbContext().TagDefinitions
+                .Include(td => td.ImplicitTags)
+                .Where(td => td.Source == TagSource.UserTag)
+                .OrderBy(td => td.Namespace)
+                .ThenBy(td => td.Name)
+                .AsEnumerable()
+                .Where(td => userService.ShowNsfw || !td.ImplicitTags
+                    .IntersectBy(nsfwTags, td => td.ObjectId)
+                    .Any())
+                .ToArray();
+
+        tagNamespaces = tagDefinitions
+            .Select(td => td.Namespace)
+            .Order()
+            .Distinct()
+            .ToArray();
+    }
+
 
     private void CreateTagDefinition() {
         if(string.IsNullOrEmpty(tagNamespace))
@@ -139,6 +152,7 @@
 
         tagService.CreateTagDefinition(tagName, tagNamespace, tagAlias);
         createTagDialog.Hide();
+        LoadTags();
         StateHasChanged();
     }
 
@@ -155,6 +169,7 @@
             return;
 
         tagService.UpdateTagDefinition(toEdit, tagName, tagNamespace, tagAlias);
+        LoadTags();
         StateHasChanged();
     }
 
@@ -169,6 +184,8 @@
 
         tagService.DeleteTagDefinition(toDelete);
         deleteTagDialog.Hide();
+        LoadTags();
+        StateHasChanged();
     }
 
     private void PromptImplicitTags(TagDefinition toEditImplicit) {
@@ -183,6 +200,7 @@
             return;
 
         tagService.SetImplicitTags(toEditImplicit, tagDefs);
+        LoadTags();
         StateHasChanged();
     }
 
@@ -191,9 +209,13 @@
             tagService.AddImplicitTag(tagDef.Guid, HBContext.NsfwTag);
         else
             tagService.RemoveImplicitTag(tagDef.Guid, HBContext.NsfwTag);
+        LoadTags();
         StateHasChanged();
     }
 
     private async void ShowNsfwChanged(object? sender, bool showNsfw) =>
-        await InvokeAsync(() => StateHasChanged());
+        await InvokeAsync(() => {
+            LoadTags();
+            StateHasChanged();
+        });
 }
-- 
cgit v1.3