Wie gelesene Forenbereiche DB-technisch umsetzen?

Verwendetes Datenbanksystem: MSSQL (Azure SQL)

Wie in Sammelthema Wünsche und Bugreports myCSharp nachzulesen gibt es den Wunsch, dass wir die aktuelle Implementierung der gelesenen Forenbereiche überarbeiten, was wir seit einiger Zeit versuchen umzusetzen.

Die aktuelle Datenbankstruktur inkl. den relevanten Spalten für dieses Feature sieht wie folgt aus:

• UsersAccounts (Id)
• ForumBoards (Id)
• ForumThreads (Id, BoardId)
• ForumPosts (Id, ThreadId, PostedOn)
• ForumBoardUserVisits (UserId, BoardId, VisitedOn) - Wann hat der Benutzer das letzte Mal das Board besucht
• ForumThreadUserVisits (UserId, ThreadId, VisitedOn) - Wann hat der Benutzer das letzte Mal das Thema besucht

Wir suchen nun eine performante Möglichkeit, die es ermöglich das gewünschte Feature umzusetzen.
Dabei gibt es zwei Anforderungen:

"Wurde das Thema gelesen" was relativ einfach umzusetzen ist, weil wir einfach den letzten Besuch aus ForumThreadUserVisits mit dem letzten Beitrag eines Themas vergleichen können; im Endeffekt also eine 1:1 Relation basierend auf dem User-Context.
Die Fragestellung "Ist etwas im Forenbereich A von Benutzer B ungelesen" ist hier eine etwas andere Dimension, da wir für jeden Benutzer alle Themen eines Boards ermitteln müssen und pro Thema dann den letzten Beitrag und den zugehörigen Visit auswerten müssen. Im Endeffekt läuft das also auf einen Gesamt-Scan der Datenbank aus, weil wir ja ständig alles miteinander vergleichen müssen.

Der aktuelle Workaround ist daher, dass wir in ForumBoardUserVisits den letzten Besuch eines Lesers speichern und so nicht alle Inhalte miteinander vergleichen müssen, was aber zur Folge hat, dass der Bereich als ungelesen gewertet wird, wenn ein Thema angesehen wird, aber nicht das Board selbst- der aktuelle Kritikpunkt.

Daher suchen wir nun gemeinsam mit euch eine Möglichkeit eine zufriedenstellende, stabile Lösung zu finden.

Wie hat das alte Forum das gemacht?
Leider gar nicht. Das alte Forum hatte keinerlei Backend-Funktionalitäten für dieses Feature.
Es wurde ein Visit-Cookie mit der jeweiligen Uhrzeit gespeichert und damit verglichen. Auch hier gab es daher die Race-Condition (sogar pro Browser, da eben Cookie), dass Inhalte als ungelesen/gelesen markiert wurden, obwohl das Gegenteil der Fall war.

Randinformationen:

• In ForumThreadUserVisits wird wirklich jeder Visit gezählt. Ist der Benutzer völlig neu, dann hat er hier keine Einträge. Hat ein Benutzer das gesamte Forum gelesen, dann befinden sich hier für den Benutzer aktuell 117.000 Einträge. Entsprechend erhöht sich die Anzahl der potentielle Einträge anhand von Anzahl Threads x Anzahl Benutzer. Wurde soweit Lastgetestet und ist eigentlich kein Problem für die Zukunft.
• Der Query darf sich auf keinen Fall expotentiell Anhand der Inhalte verlangsamen

Was haben wir bisher versucht?
Die Datenbank ist sehr auf Performance und weniger auf Größe optimiert und der Leseanteil der Zugriffe beträgt etwas über 99%.
Daher haben wir sehr viele Abfragen in Form von indizierten Views umgesetzt.

Beispielsweise müssen wir nicht alle Forenposts pro Thema abfragen, um den neuesten Beitrag und dessen Datum zu lesen, sondern haben dies in einer indizierten View (ThreadId, LatestPostId, LatestPostedOn).
Ebenso haben auch die Forenstats in indizierten Views, die aktuell die Basis der Implementierung darstellt (ForumBoardId, TotalThreadsCount, TotalPostsCount).

Die Idee aktuell ist: wir zählen die Themen, die der Benutzer besucht hat und vergleichen diese mit der aktuellen Anzahl des Boards.
Dies sind indizierte Informationen, sodass wir nur zwei Zahlen miteieinander vergleichen müssen. Leider kommt jedoch auch der Faktor des Datums hinzu, da uns ja nicht reicht ob ein Benutzer alle Themen besucht hat sondern auch, ob es neue Beiträge seit seinem letzten Besuch gibt.

Tjo, und da sind wir nun.
Wir haben einige Versuche gestartet, und auch Lösungen gefunden, aber 3 Sekunden Zeitdauer für einen Query sind einfach viel zu lang.
Wir suchen daher Ideen und Hilfe diese aktuelle Anforderung umzusetzen.

PS: ich zeige nun an dieser Stelle bewusst nicht meine aktuelle Lösung, die zu langsam ist, ich will niemand hier auf meinen evtl. falschen Lösungsweg bringen.

Herzlichen Dank!

Ich bin kein Profi, aber "brainstorme" mal ein bisschen:
Ob das so eine geile Idee ist, weiß ich nicht, aber vielleicht bringt's jemand Anderes auf die zündende Idee ^^

Was ist, wenn Du das Problem umdrehst, also nicht die gelesenen Themen anschaust, sondern die ungelesenen Themen?
Mein Gedanke wäre eine Tabelle, in der die Anzahl ungelesener Threads je Board und User steht, es sind also nur die Spalten BoardId, UserId und Count.
Um herauszufinden, ob ein Board ungelesene Themen enthält, muss nur diese Zahl mit 0 verglichen werden. Gibt's keinen Eintrag, gilt das Board als ungelesen.
Wenn ein User ein Thread besucht, das er noch nicht gelesen hat, wird Count um 1 verringert.

Wenn irgendwo in einem Board etwas geändert wird, muss jeder Eintrag in dieser Tabelle (gefiltert auf BoardId, also aktuell max. 117k) abgelaufen und Count um 1 erhöht werden.
Wenn ein User ein Board besucht und noch keinen Eintrag hat, wird dieser Eintrag mit der Anzahl der tatsächlich ungelesenen Threads erstellt.
Ich gehe mal davon aus, dass viele User inaktiv sind und vom Rest nicht alle alles lesen, deshalb wäre diese Tabelle deutlich kleiner als derzeit Anzahl User * Anzahl Boards.

Zusätzlich könnte man auch regelmäßig (z.B. nachts) einen Job laufen lassen und aus dieser Tabelle alles raus werfen, wo die Anzahl größer als 0 ist und der User lange nicht aktiv war.

Damit verlagerst Du das Problem zwar ein Stück weit auf Änderungen im Forum, aber Du sagst ja selber auch, dass weit häufiger gelesen als geschrieben wird und ich persönlich kann ein kleines bisschen mehr Zeit nach einem neuen Post durchaus verschmerzen.

Was ist, wenn Du das Problem umdrehst, also nicht die gelesenen Themen anschaust, sondern die ungelesenen Themen?

Das haben wir auch evaluiert, aber damit hast Du am Ende mehr Last und mehr Storage, da Du ja Dinge von inaktiven Benutzern tracken musst.
Und das ist potentiell ein höher wachsendes Potential, als umgekehrt.

Darüberhinaus müssen wir ja einen Besuch eines Benutzers speicher, um die Uhrzeit zu haben.
Ansonsten können wir nicht den "Besuch" von einem "Besuch, aber neue Antwort" unterscheiden.

Der Faktor ForumThreadUserVisits werden wir daher auf keinen Fall abschaffen (können). Gleiches Konstrukt haben wir übrigens auch für die Notifications (also ob Du eine E-Mail über eine neue Themenbenachrichtigung gelesen hast, wenn Du es aktiviert hast), was hier aber in dem Fall nicht Teil des Themas ist.

Der Hauptfokus ist daher wirklich der Query, wie wir diesen erstellen / optimieren.

Ansonsten können wir nicht den "Besuch" von einem "Besuch, aber neue Antwort" unterscheiden.

Und wenn Du eine neue Spalte "HasVisited" hinzufügst, die dann auch auf 0 gesetzt werden muss?
Aber ja, es bedeutet mehr Platz und man muss bei jeder Änderung alle Einträge ablaufen.

[...], da Du ja Dinge von inaktiven Benutzern tracken musst.

Daher der Gedanke, dass regelmäßig (nachts, einmal pro Woche/Monat) aufgeräumt wird, um die Leute, die nicht mehr aktiv sind, aus der Gleichung zu entfernen.
Es muss ja nicht für jeden User ein Eintrag da sein, fehlt der, gilt das Board als ungelesen und der Eintrag wird erst beim ersten Besuch erstellt.
So reduziert sich zwar kaum die Lese-Performance, aber man muss weniger Einträge ändern, was weniger Traffic bedeutet.

Oder noch eine ergänzende Idee:
Es gibt noch eine zweite Tabelle, in der je Boad generell gezählt wird, wie viele Threads geändert wurden.
Regelmäßig (z.B. stündlich) läuft ein Job los, addiert diese Zahlen auf die erste Tabelle drauf und leert anschließend die zweite Tabelle.
Damit der Zähler der ersten Tabelle noch stimmt, muss der auch unter 0 zählen dürfen, außerdem muss diese zweite Tabelle bei der Abfrage immer mit eingerechnet werden.

So könntest Du das ständige Schreiben in der ersten Zähler-Tabelle reduzieren, die zweite Zähler-Tabelle ist mit der Anzahl Boards ja ziemlich klein.
Nachteil ist, dass die Anzeige auf der Startseite nicht immer top aktuell ist, steuern kann man das aber mit dem Intervall, wie oft der Job läuft.
Aber auch hier hast Du mehr Storage, dafür aber weniger Traffic.

Und wenn Du eine neue Spalte "HasVisited" hinzufügst, die dann auch auf 0 gesetzt werden muss?

Das hilft uns ja nicht, weil wir die Uhrzeit brauchen.
Das "HasVisited" ist prinzipiell schon die Existenz des Eintrags, damit brauchen wir keine zusätzliche Spalte.

Und so wie es aktuell ist (Key über Thread Id und User Id) ist das die schnellste Art und Weise, wie eine Abfrage möglich ist (der clustered key).

Daher der Gedanke, dass regelmäßig (nachts, einmal pro Woche/Monat) aufgeräumt wird, um die Leute, die nicht mehr aktiv sind, aus der Gleichung zu entfernen.
..
So reduziert sich zwar kaum die Lese-Performance, aber man muss weniger Einträge ändern, was weniger Traffic bedeutet.

Ich denke, dass das am Ende mehr Read-Compute benötigt PLUS den zusätzlichen Aufwand der Verwaltung + Storage.

Regelmäßig (z.B. stündlich) läuft ein Job los, addiert diese Zahlen auf die erste Tabelle drauf und leert anschließend die zweite Tabelle.

Damit haben wir aber keinen Echtzeit-Status, der aber gewünscht ist.

Ich würde das wirklich gerne einfach über indizierte Views lösen.
Die haben das absolute Minimum an Schreib-Operation pro Read (weil nur die View-Page aktualisiert werden muss, die sich ändert) und wir brauchen keine zusätzlichen Jobs oder sowas.

Ich bin auch überzeugt, dass wir nicht mehr Infos speichern müssen, als wir es heute schon haben, das funktioniert alles super.
Nur der Query über ein Board hinweg fehlt.

Die Sache ist auch die, dass wir am Ende nicht wissen müssen wie viele Threads nicht gelesen / neue Posts ungelesen sind, sondern ob.
Wir brauchen / wollen also ein .Any() und kein .Count() und ich weiß auch, dass das der SQL Server unterstützt und so die Queries optimiert (und damit die Anzahl an Einträgen egal sind) - aber ich weß nicht, wie man das in unserem Fall korrekt anwendet.

Hallo Abt,

könntet ihr die Abfrage zweiteilen?
Wenn pro User jeweils das Datum/Uhrzeit des letzten Aufrufs eines Themas (Threads), nicht eines Boards oder einer anderen organisatorischen Seite, gespeichert wird, dann würde man zuerst abfragen, ob seitdem im betreffenden Board ein neuerer Beitrag erstellt wurde (also nur ein Datumsvergleich).
Und nur wenn nicht, dann die Abfrage (per TOP 1, IIF(EXISTS(...), 1, 0) o.ä.) auf die ungelesenen Beiträge eines Users je Board.

Die Tabelle ForumBoardUserVisits sehe ich dann hierbei als nicht zu betrachten an, da es ja auch andere Möglichkeiten (wie z.B. mittels Aktive Themen oder eines Direkt-Links) gibt, einzelne Themen aufzurufen und sich dadurch dann der Gelesen/Ungelesen-Status eines Boards ändern kann.

Edit:
Oder man benutzt die Tabelle ForumBoardUserVisits nicht als "Wann wurde das Board zuletzt aufgerufen?", sondern speichert dort, wann hat ein User zuletzt einen Thread aus diesem Board gelesen (und nutzt dann diese Info als erste Abfrage).

PS: Ich gehe davon aus, daß man jeweils von einem Thread an dessen Board kommt, also so gespeichert ist:

• ForumThreads (Id, BoardId)

(diese Info fehlte m.E. in deinem ersten Beitrag)

Hi,

danke für das Feedback.

Im Endeffekt suche ich ein Query wie

SELECT
   Id,
   BoardName,
   ...
   (hier ein Subselect aka "hat der User ein Thema im Board nicht gelesen oder gibt es einen neuen Beitrag) -- wobei das auch ein Join sein könnte
FROM ForumBoards
JOIN (hier Permission Check)

An für sich existiert dieser Query, aber er hat folgende Probleme:
-- Es wird im Endeffekt jedes Board abgefragt und damit auch jeder Thread - das heisst es wird alles durchsucht -> unperformant
-- Ich habe einen Fehler im JOIN, dass wenn ein Thema nie gelesen wurde (und damit kein Visit-Eintrag existiert, das gesamte Board als gelesen deklariert wird);
-- Der User im Subquery kann NULL sein, wenn nicht angeloggt; das optimiert aber der SQL Server raus, weil die Visit-Table niemals einen User mit NULL haben kann

(diese Info fehlte m.E. in deinem ersten Beitrag)

Danke, habe ich begefügt.

Wenn pro User jeweils das Datum/Uhrzeit des letzten Aufrufs eines Themas (Threads), nicht eines Boards oder einer anderen organisatorischen Seite, gespeichert wird, dann würde man zuerst abfragen, ob seitdem im betreffenden Board ein neuerer Beitrag erstellt wurde (also nur ein Datumsvergleich).

Wenn ich dich richtig verstehe, dann haben wir das als exakte "Lösung" derzeit.
Wir speichern, wann ein User das letzte Mal das Board besucht hat und vergleichen das mit der Uhrzeit des letzten Beitrags in diesem Board.
Die Uhrzeit wird aber nur gesetzt, wenn man das Board besucht, nicht das Thema. Besucht man das Thema direkt, dann ist das Board weiter ungelesen.

und sich dadurch dann der Gelesen/Ungelesen-Status eines Boards ändern kann.

Ich kann aber die Uhrzeit nicht im Thema setzen: hätte man 3 Themen in einem Board ungelesen, dann würde man das Board bei einem Themenbesuch als gelesen markieren, obwohl man noch 2 Themen nicht gesehen hat.

Zu der Query müßte ich mir mal intensiver Gedanken machen (ich ahne schon, daß es mit der bisherigen Vorgehensweise, alleine anhand der Einträge in ForumThreadUserVisits die ungelesenen Beiträge zu ermitteln, schwer bzw. inperformant ist).

Bzgl.

Wenn ich dich richtig verstehe, dann haben wir das als exakte "Lösung" derzeit.

Ich meine gerade nicht den Besuch des Boards, sondern nur, wenn ein konkretes Thema gelesen wird, daß man dann Datum und Uhrzeit speichert (bzw. wie in meinen Edit angesprochen, für das jeweilige Board - basierend auf der BoardId des gelesenen Threads).
Alle Beiträge welche später hinzugefügt werden, würden dann automatisch dazu führen, daß die entsprechenden Boards als ungelesen angesehen werden können.

Ob die Boardübersicht angeschaut wird oder nicht, sollte also gar keinen Einfluß auf den Gelesen/Ungelesen-Status des Boards haben!

Ob die Boardübersicht angeschaut wird oder nicht, sollte also gar keinen Einfluß auf den Gelesen/Ungelesen-Status des Boards haben!

Ja, vermutlich kann man die Tabelle ForumBoardUserVisits droppen, weil sie nicht hilft. Meine Idee kam hier ja nich gut an 😉
Also weg damit.

Wir brauchen zwei Informationen, die beide mit ForumThreadUserVisits zu erreichen sind:

• Hat der User das Thema jemals besucht? -> Ergibt sich aus dem Key ThreadId und UserId in ForumThreadUserVisits
• Gibt es ein neuen Beitrag im Thema seit dem letzten Besuch? -> Ergibt sich aus VisitedOn in ForumThreadUserVisits im Vergleich zum letzten Themenbeitrag.

Eigentlich super easy 🙂

Ich habe gestern nachmittag darüber weitergedacht, wollte aber erst eine Nacht darüberschlafen, bevor ich es hier poste.

Die Abfrage auf alle ungelesenen Themen eines Boards sieht wahrscheinlich bisher so ähnlich aus:

SELECT T.Id FROM ForumThreads T, ForumThreadUserVisits U
WHERE T.Id = U.ThreadId AND T.BoardId = :BoardId AND U.UserId = :UserId AND NOT ThreadCompleteRead(:UserId, T.Id)

(wobei ThreadCompleteRead eine Funktion, View oder auch Join ist).

Um auch die nicht besuchten Themen zu erfassen, muß hier wohl noch zusätzlich ein LEFT-/ oder RIGHT JOIN hin.
Und dann diese Abfrage optimiert, wie in meinem vorletzten Beitrag geschrieben, mit TOP 1, IIF(EXISTS(...), 1, 0), o.ä. um nur zu schauen, ob es mindestens einen ungelesenen Beitrag gibt.

Aber gerade für jeden Thread dann ThreadCompleteRead ausführen zu müssen, verlangsamt die Abfrage doch sehr (insbesondere, wenn viele schon gelesen sind).

Daher habe ich noch mal über die Hauptszenarien (Use-Cases) nachgedacht und komme eigentlich nur auf 2:

1. Ein Forumsanwender ist recht häufig im Forum und liest viele Themen (bzw. benutzt "alle Threads (eines Boards) als gelesen markieren"), d.h. es gibt nur wenige noch ungelesene Beiträge.
2. Ein Forumsanwender ist nur ab und zu im Forum, kümmert sich aber nicht um die ungelesenen Beiträge, d.h. es gibt eher sehr viele ungelesene Boards.

Gefragt ist die Funktionalität ja nur auf der Hauptforumseite Forum, d.h. nach dem Login bzw. nach einer Aktualisierung dieser Seite.

Wie wäre es, wenn nur eine bestimmte Anzahl von Themen zur Ermittlung herangezogen werden? Die bisherige Boardabfrage ("https://mycsharp.de/forum/boards/XXX"), d.h. die nach Datum sortierte Liste, scheint ja recht schnell zu gehen.

Wie wäre es also, wenn z.B. nur die Themen der ersten Seite jedes Boards dafür überprüft werden, d.h. page=1, also in der oberen Abfrage statt ForumThreads dann die entsprechende Boardthemen (die nehme ich mal an, schon als View existieren) überprüft werden?

Dies wäre dann ein immer gleicher Themenvergleich, unabhängig von der Gesamtthemenanzahl (je Board).
Der Tooltip lautet ja bisher auch einfach "Neue Beiträge!", d.h. für jeden Forumsanwender wäre dann auch klar, daß ältere Themen nicht berücksichtigt werden (falls einige davon noch ungelesen sind, aber die neueren alle gelesen wurden).

Hi, danke Dir für die Mühe!

Ich muss zugeben, dass es aktuell sogar zwei Queries sind - weil ich es nicht besser hin bekommen habe.
Der erste Query (als View) lädt alle Boards, worauf der User rechte hat.

SELECT
   Id,
   BoardName,
   ...
FROM ForumBoards
JOIN (hier Permission Check)

Der zweite Query ist nun für das Laden des Gelesen-Status (aktuell als Linq).
Diese Query soll eigentlich Teil des ersten Queries sein; aber er funktioniert aktuell nicht so wie gewünscht.

Um auch die nicht besuchten Themen zu erfassen, muß hier wohl noch zusätzlich ein LEFT-/ oder RIGHT JOIN hin.

Ich denke, dass dies über ein Subselect zu lösen ist, gerne aber auch als Join
Ich vermute, dass ich einen LEFT JOIN brauchen werde.

Aber gerade für jeden Thread dann ThreadCompleteRead ausführen zu müssen, verlangsamt die Abfrage doch sehr (insbesondere, wenn viele schon gelesen sind).

Meine aktuelle Lösung ist alleine gesehen relativ performant, aber eingebaut in den ersten Query nicht.
Darüber hinaus gibts auch einen LEFT JOIN Fehler, sodass ein funktionierendes Endresultat evtl langsamer sein kann.

SELECT
    [f].[ForumBoardId], COUNT(*) AS [BoardThreadCount],
    COUNT(CASE WHEN [f3].[ForumThreadId] IS NULL OR [f3].[UserAccountId] IS NULL THEN 1 END) AS [BoardThreadsUnvisitedCount],
    COUNT(CASE WHEN ([f3].[ForumThreadId] IS NOT NULL AND [f3].[UserAccountId] IS NOT NULL) AND ([f3].[LastVisitedOn] < [f2].[PostedOn]) THEN 1 END) AS [BoardThreadsWithNewPostCount]
FROM [ForumRelationsDbView] AS [f]
INNER JOIN (
    SELECT Berechtigungen des Users
) AS [t] ON [f].[ForumBoardId] = [t].[Id]
INNER JOIN [ForumPosts] AS [f2] ON [f].[ForumThreadLatestPostId] = [f2].[Id]
LEFT JOIN [ForumThreadUserVisits] AS [f3] ON [f].[ForumThreadId] = [f3].[ForumThreadId]
WHERE ([f3].[ForumThreadId] IS NULL OR [f3].[UserAccountId] IS NULL) OR [f3].[UserAccountId] IS NULL
GROUP BY [f].[ForumBoardId]

Ein Fehler dieses Queries ist auch, dass ein Board einfach nicht auftaucht, das keine ungelesene Dinge für einen User für ein Board hat.
Eingebaut im ersten Query führt das dazu, dass entsprechend das Board nicht auftaucht (vermutlich weil der LEFT JOIN irgendwo falsch ist).

Daher ist der aktuelle Work around:
Zwei Queries und ich map das ganz eklig im Code basierend auf der Board-Id...

Wie wäre es, wenn nur eine bestimmte Anzahl von Themen zur Ermittlung herangezogen werden?

Im Idealfall braucht man das ja nicht, sofern meine Vorstellung überhaupt funktioniert.
Die wäre ja, dass man nur Anzahlen sowie Zeitwerte miteinander vergleicht, sodass diese indizierbar werden.
Dann kommt es auf mehr/weniger Einträge nicht mehr an.
Sort Speed ist auch kein Problem, das wir auf alle relevanten Spalten bereits entsprechende Indizes setzen; macht das Schreiben minimal (kaum messbar) langsamer, aber der Main-Case ist ohnehin der lesende Zugriff.

Wie wäre es also, wenn z.B. nur die Themen der ersten Seite jedes Boards dafür überprüft werden, d.h. page=1

Die Anzahl von Einträgen pro User kann unterschiedlich sein.
Im Code bereits vorgesehen, in der UI noch nicht drin.

Hallo Abt,

danke für deinen Beitrag.
Könntest du an der Query zwei Details erklären, die ich nicht so ganz verstehe:

• Das erste ist eine Verständnisfrage bzgl. der Rückgabe, d.h. was bedeutet COUNT(CASE, ...)? Das dient der Aufsummierung der einzelnen Werte je zurückgegebener Zeile (für die Gruppierung), oder?
• Und dann frage ich mich, was die doppelte Abfrage von [f3].[UserAccountId] IS NULL in der WHERE-Bedingung soll? Bzw. was möchtest du mit der Bedingung ausdrücken?

Der LEFT JOIN sollte hier so richtig sein (kommt ja immer auf die Reihenfolge an, ob LEFT oder RIGHT).
Was für einen Fehler erhältst du denn?

was bedeutet COUNT(CASE, ...)? Das dient der Aufsummierung der einzelnen Werte je zurückgegebener Zeile (für die Gruppierung), oder?

Was eine Datenbank ganz schlecht kann, ist iterieren, weil das Paging sehr aufwändig ist.
Die Ansatz ist daher, und das hab ich auch schon als positives Feedback erhalten, nur einmal zu iterieren und Abfragen durch Bedingungen zu zählen.
Das heisst, Count() zählt hier nur hoch, wenn where erfüllt ist. Laut verschiedenen Personen und Lektüren ist das die schnellste Art und Weise des Zählens (weil Du ja sonst mehrere Male iterieren müsstest).

Dieser Query ist von Hand; mittlerweile kann seit EF Core 5 der SQL Generator ein solchen Fall und würde das genauso aufbauen.

Und dann frage ich mich, was die doppelte Abfrage von [f3].[UserAccountId] IS NULL in der WHERE-Bedingung soll?

WHERE ([f3].[ForumThreadId] IS NULL OR [f3].[UserAccountId] IS NULL) OR [f3].[UserAccountId] IS NULL

Prinzipiell gehe ich davon aus, dass diese Abfrage nicht korrekt ist, aber die Werte sind in diesem Fall geschuldet, dass ich nicht angemeldet bin.
Im angemeldeten Zustand ist der zweite Wert die UserId, also in meinem Fall

WHERE ([f3].[ForumThreadId] IS NULL OR [f3].[UserAccountId] IS NULL) OR [f3].[UserAccountId] = 12639

Der Grund ist der: f3 ist in diesem Fall [ForumThreadUserVisits] und ich will wissen, ob der Benutzer überhaupt noch nicht das Thema besucht hat, oder ob er es besucht hat.
Der Query wird hier vom EF generiert.

Der Test-EF Aufbau ist wie folgt:

var statsQuery =
    // join views
    from frv
        in DbContext.ForumRelationsDbViews
    join fb // limit access
        in QueryBoardsByUserAccess(userId, DbTrackingOptions.Disabled)
        on frv.ForumBoardId equals fb.Id
    join fp
        in QueryPosts(DbTrackingOptions.Disabled) on frv.ForumThreadLatestPostId equals fp.Id
    join ftuv
        in QueryThreadVisits(DbTrackingOptions.Disabled)
        on frv.ForumThreadId equals ftuv.ForumThreadId
        into ftuvGroup
    from
        ftuvMatch in ftuvGroup.DefaultIfEmpty()
        // filter on user
    where
        ftuvMatch == null || ftuvMatch.UserAccountId == userId
    // group  by board
    group new
    {
        Board = fb,
        LatestPost = fp,
        LatestUserVisit = ftuvMatch
    }
        by frv.ForumBoardId into viewGroup
    // select and count
    select new
    {
        // Board
        ForumBoardId = viewGroup.Key,
        // Numbers
        BoardThreadCount = viewGroup.Count(),
        BoardThreadsUnvisitedCount = viewGroup.Count(
                                        x => x.LatestUserVisit == null),
        BoardThreadsWithNewPostCount = viewGroup.Count(x =>
                                        x.LatestUserVisit != null && x.LatestPost != null
                                        && x.LatestUserVisit.LastVisitedOn < x.LatestPost.PostedOn),
    };

Was für einen Fehler erhältst du denn?

Das ist der weiter oben genannte Fehler, dass mir Boards nicht geliefert werden, deren Count = 0 ist.

Ein Fehler dieses Queries ist auch, dass ein Board einfach nicht auftaucht, das keine ungelesene Dinge für einen User für ein Board hat.
Eingebaut im ersten Query führt das dazu, dass entsprechend das Board nicht auftaucht (vermutlich weil der LEFT JOIN irgendwo falsch ist).

OK, mit dem Vergleich der UserId macht das so Sinn.

Kannst du denn die SQL-Abfragen mal direkt auf der DB ausführen?
Und ersteinmal nur

LEFT JOIN [ForumThreadUserVisits] ...

ohne die vorherigen INNER JOINS (evtl. auch mal ohne Gruppierung)?

Daß du nicht alle BoardIds in der Gesamt-Query erhältst, deutet ja eher auf eine zu restriktive Bedingung hin (ein LEFT JOIN liefert aber gleich viele oder mehr Werte als ein INNER JOIN).

Hab jetzt bisschen rumprobiert, was Du meinen könntest, aber komme nicht auf den grünen Zweig.
Wo soll ich das left join packen, an den ersten Query?

Gedanklich gesehen kann ich die beiden Queries nich joinen, sondern brauche (nach meinem Verständnis) ein Subselect bzw. weil ich ja 2 Werte aus dem Sub-Selekt haben will eine CTE, oder nicht?

Also falls sich jemand wundert, wie weit wir mit meinem SQL Wissen gekommen sind: ich mich auch.

Du bist doch der Meinung, daß der LEFT JOIN Datensätze ausläßt (Boards mit Ungelesen-Count = 0)?!
Daher würde ich den SELECT nach und nach überprüfen, d.h. ersteinmal nur

SELECT count(*) FROM [ForumRelationsDbView] AS [f]

Dann

SELECT count(*) FROM [ForumRelationsDbView] AS [f]
LEFT JOIN [ForumThreadUserVisits] AS [f3] ON [f].[ForumThreadId] = [f3].[ForumThreadId]
WHERE [f3].[UserAccountId] = 12639

So sollte die Anzahl ja gleich bleiben, denn in der ForumThreadUserVisits sollte ja höchstens 1 Eintrag je ForumThreadId+UserAccountId stehen.
Anschließend baust du jeweils die Gruppierung nach [ForumBoardId] wieder ein und vergleichst dann die Anzahl (der Boards), die auch wieder gleich sein sollte.

Wenn das paßt, dann fügst du nach und nach wieder die anderen INNER JOINs hinzu (jeweils zu beide Queries) und vergleichst die Resultate.

Und erst wenn die direkten SQL-Queries stimmen, dann würde ich es mit EF umsetzen (und auch wieder vergleichen).

PS: Ich gehe mal davon aus, daß du auf einer Kopie (Snapshot) der DB arbeiten kannst (so daß nicht zeitgleich neue Threads hinzukommen).

Das Query Problem ist eigentlich nur der zweite Teil.
Konkretes Beispiel, das (glaube ich) den Fehler verursacht:

Das Forum besitzt derzeit 29 Boards, wobei man 29 Boards sieht, wenn man die Rechte hat.
Entsprechend weniger sehen andere Forenteilnehmer, je nach Rechten. Soweit normal.

Mein Testaccount hat Zugriff auf alle 29 Boards.
Eines dieser Boards hat aber weder Themen und dadurch auch keine Beiträge.

Daher liefert mir der Query 2 nur 28 Boards und keine 29.
Grund hierfür ist (vermutlich) der Join auf die Visits, weil hier natürlich auch nichts existiert. Dadurch wird das ganze Board ausgefiltert.
Joine ich das auf den ersten Query, dann fehlt auch da entsprechend das Board.

Ich weiß nicht genau wie, aber man muss Query 2 so anpassen, dass theoretisch keinerlei Visits existieren können, die gezählt werden können - und das Board darf nicht ausgefiltert werden.

Ich dachte es geht um die Boards, wo ein User keine ungelesenen Themen mehr hat.

Es ist klar, daß die bisherige Query nur Boards mit enthaltenen Themen liefert (da ja über alle Themen [Threads] selektiert wird).

Aber ist das wirklich ein Problem?
Du könntest natürlich ebenfalls hier einen LEFT JOIN machen:

SELECT * FROM [ForumBoards] AS [fb]
LEFT JOIN (<bisherige Query>) AS [fb2] ON [fb].[Id] = [fb2].[ForumBoardId]

So erhältst du dann allerdings NULL in den zurückgegebenen Spalten für die Boards ohne Themen (anstatt z.B. 0).

Edit: Ah, jetzt habe ich wohl deine Gesamt-Query verstanden (auch bzgl. der "Berechtigungen des Users").
Ich war bisher davon ausgegangen, daß ForumRelationsDbView alle Themen zurückliefert (aber diese sind nur in ForumThreads enthalten).

Dann wirst du die bisherige Query etwas anders aufbauen müssen (und zwar kombiniert mit meinem obigen LEFT JOIN).
Du solltest den SELECT mit der Gruppierung separieren (und als Sub-SELECT des LEFT JOIN einbinden).
Und der erste SELECT links davon sollte nur die Boards (mit Berechtigungen) betrachten.
Hier mal als Grob-Entwurf:

SELECT *
FROM [ForumRelationsDbView] AS [f]

INNER JOIN (
    SELECT Berechtigungen des Users
) AS [t] ON [f].[ForumBoardId] = [t].[Id]

LEFT JOIN

(SELECT [f2].[ForumBoardId], COUNT(*) AS [BoardThreadCount],
    COUNT(CASE WHEN [f3].[ForumThreadId] IS NULL OR [f3].[UserAccountId] IS NULL THEN 1 END) AS [BoardThreadsUnvisitedCount],
    COUNT(CASE WHEN ([f3].[ForumThreadId] IS NOT NULL AND [f3].[UserAccountId] IS NOT NULL) AND ([f3].[LastVisitedOn] < [f2].[PostedOn]) THEN 1 END) AS [BoardThreadsWithNewPostCount]
 FROM [ForumPosts] AS [f2] WHERE [f].[ForumThreadLatestPostId] = [f2].[Id]
 LEFT JOIN ForumThreadUserVisits] AS [f3] ON [f2].[ForumThreadId] = [f3].[ForumThreadId]
 WHERE ([f3].[ForumThreadId] IS NULL OR [f3].[UserAccountId] IS NULL) OR [f3].[UserAccountId] = :UserId
 GROUP BY [f2].[ForumBoardId]
) AS [fb2]

ON [f].[ForumBoardId] = [fb2].[ForumBoardId]

(ich hoffe, ich bin bei den vielen Alias- und Spaltennamen nicht durcheinandergekommen)

Und hoffentlich kriegst du das dann so auch in EF als Linq-Ausdruck hin. 😉

Wenn ich das richtig verstehe wird VisitedOn in ForumThreadUserVisits aktualisiert, wenn ein Thread aufgerufen.
Und das gleiche bei ForumBoardUserVisits, wenn ein Board aufgerufen wird.

Ich weiß nicht wie fix das Datenbankschema ist.
Ich hätte da einen Vorschlag.

Ist es wichtig zu wissen wann eine Thread zuletzt aufgerufen wurde oder reicht es nur zu wissen das für einen User ungelesene vorliegen.

Die Tabellen ForumThreadUserVisits und ForumBoardUserVisits werden durch ForumThreadUserUnread ersetzt.
Diese hat die Spalten UserId, ThreadId und BoardId.

Ist in der Tabelle ein Eintrag vorhanden, bedeutet es liegen ungelesene Post für einen Thread vor.

Wird ein neuer Post (oder auch neuer Thread) erstellt, wird in der Tabelle für alle User ein Eintrag erstellt, wenn noch nicht vorhanden.
Wird von einen User ein Thread aufgerufen, wird ein vorhandenen Eintrag wieder entfernt und man weiß dieser ist gelesen.

Ich greife mal das SQL Fragment von weiter oben auf

SELECT
   Id,
   BoardName,
   (SELECT COUNT(*) FROM ForumThreadUserUnread WHERE UserId = @UserId AND BoardId = Id) AS CountUnreadThread
FROM ForumBoards
JOIN (hier Permission Check)

Ich kann nicht sagen wie Performant das ist, aber nur so als Idee.

Das Umkehren von Gelesen auf Ungelesen schließe ich eigentlich aus, außer, mir erklärt jemand, wie das schneller sein soll.

Wenn wir den Ungelesen Status tracken würden, dann würde das bedeuten, dass wir bei jeder Registrierung sofort für 117.000 Themen Dateneinträge erzeugen müssen und gleiches bei neuem Thema für 30.000 User. Hinzu kommt, dass wir Stati erzeugen müssen, die womöglich bei vielen Usern nie wieder abgefragt werden (weil Konten inaktiv etc).
Ich weiß nicht, wie das schneller sein soll. Das ist ja gerade das, was wir nicht wollen: expotentielles Wachstum / Leistungsthema. Oder sehe ich das falsch?

Hinzu kommt, dass wir diese Konstellation ja nicht nur für "Thema gelesen" haben, sondern wir ja auch noch Themen Abos haben, also Benachrichtigungen, die der User wünscht (zB. neuer Beitrag).
Im Endeffekt ist das exakt der gleiche Aufbau (würde ich auch beides exakt gleich umsetzen).

Es ist klar, daß die bisherige Query nur Boards mit enthaltenen Themen liefert (da ja über alle Themen [Threads] selektiert wird).
Aber ist das wirklich ein Problem?

Im Endeffekt schon: wenn wir nen neuen Forenbereich haben, dann würden wir diesen nicht sehen, weil er keine Beiträge hat.
Dann müssten wir irgendwie einen Workaround schaffen, um das Initialthema zu erzeugen.

So erhältst du dann allerdings NULL in den zurückgegebenen Spalten für die Boards ohne Themen (anstatt z.B. 0).

Gut, das sollte ich ja mit einem ISNULL behandeln können, wenn ich den Query hin bekomme.

Und erst wenn die direkten SQL-Queries stimmen, dann würde ich es mit EF umsetzen (und auch wieder vergleichen).

Würde ich gerne, aber ich kann einer View keine Parameter übergeben (hier die User Id).
Daher war dieser Weg für mich aktuell einfacher, solange ich keine inidizierbare / querybare View habe.

Ich habe meinen letzten Beitrag noch mal editiert. 😉

dass wir bei jeder Registrierung sofort für 117.000 Themen Dateneinträge erzeugen müssen

Wenn für einen neuer User alle Themen als ungelesen gekennzeichnet sein soll, dann Ja.
Ich würde sagen bei einen neuen User ist für ihn alles als gelesen gekennzeichnet.
Und das Tracking beginnt erst mit der Registrierung.

und gleiches bei neuem Thema für 30.000 User. Hinzu kommt, dass wir Stati erzeugen müssen, die womöglich bei vielen Usern nie wieder abgefragt werden (weil Konten inaktiv etc).

Dem könnte man entgegen wirken, das nur User berücksichtigt werden deren letzte Aktivität nicht zu lange zurück liegt.

Ich weiß nicht, wie das schneller sein soll. Das ist ja gerade das, was wir nicht wollen: expotentielles Wachstum / Leistungsthema. Oder sehe ich das falsch?

Aber wenn ich das richtig sehe mit dem aktuellen Datenbankschema, spielt die Tabelle ForumBoardUserVisits keine Rolle um an die Informationen zu bekommen, ob alle Themen eines Boards gelesen sind.
Und anders müsste die Informationen aus mehreren Tabellen ermittelt werden, neben ForumThreadUserVisits müssten auch noch ForumPosts und ForumThreads berücksichtigt werden.
Bei meinen Vorschlag nur die eine Tabelle.

Weiterhin hat man damit auch die Möglichkeit auszuräumen, z.B. ältere Einträge aus der Tabelle löschen.
Und ich weiß nicht ob es gewünscht ist über die Jahre festzuhalten, wann was von jeden User gelesen wurde.
Ob das wirklich schneller, müsste man testen.

Aber wenn ich das richtig sehe mit dem aktuellen Datenbankschema, spielt die Tabelle ForumBoardUserVisits keine Rolle

Richtig. Wir haben ja schon heraus gefunden, dass diese Tabelle nicht mehr notwendig ist, weil die aktuelle Idee nicht funktioniert / zufriedenstellend ist.

Und anders müsste die Informationen aus mehreren Tabellen ermittelt werden, neben ForumThreadUserVisits müssten auch noch ForumPosts und ForumThreads berücksichtigt werden.

Nein, wir brauchen nur das Datum des letzen Posts und das Datum des letzten Visits.
Damit haben wir eine boolsche Information, die fast keine Last kostet. Ein neuer Forenpost würde damit nur einen einzige Schreiboperation auslösen.
Deine Variante hingegen würde für jeden Forenpost >30k Einträge schreiben / aktualisieren: eine immense Last.
Ich versteh nicht, wie diese Variante schneller / effizienter sein soll.

Wir suchen nun eine performante Möglichkeit, die es ermöglich das gewünschte Feature umzusetzen.

Dazu habe ich einen alternativen Vorschlag gemacht.
Aber mir persönlich ist dieses Feature relativ egal, da ich es nicht brauche.

Ich kenne nicht die Post pro Zeit und was so gelesen wird pro Zeit.
Ich kann mir nur vorstellen wenn die Forenübersicht aufgerufen wird, das es schneller geht da weniger Tabellen betroffen sind.
Aber dafür müsste man es testen.