Frage Aktualisieren redundanter / denormalisierter Daten automatisch in SQL Server


Verwenden Sie ein hohes Maß an redundanten, denormalisierten Daten in meinen DB-Designs, um die Leistung zu verbessern. Ich werde oft Daten speichern, die normalerweise verbunden oder berechnet werden müssten. Zum Beispiel, wenn ich a Benutzer Tisch und a Aufgabe Tisch, ich würde das speichern Nutzername und UserDisplayName redundant in jedem Aufgabe Aufzeichnung. Ein anderes Beispiel hierfür ist das Speichern von Aggregaten, z Aufgabenanzahl in dem Benutzer Tabelle.

  • Benutzer
    • Benutzeridentifikation
    • Nutzername
    • UserDisplayName
    • Aufgabenanzahl
  • Aufgabe
    • Aufgaben-ID
    • Aufgabennname
    • Benutzeridentifikation
    • Nutzername
    • UserDisplayName

Das ist großartig für die Leistung, da die App viel mehr Lesevorgänge als Einfügen, Aktualisieren oder Löschen Operationen und seit einige Werte wie Nutzername selten ändern. Der große Nachteil ist jedoch, dass die Integrität über den Anwendungscode oder die Auslöser durchgesetzt werden muss. Dies kann bei Updates sehr umständlich sein.

Meine Frage ist, kann dies automatisch in SQL Server 2005/2010 getan werden ... vielleicht über eine persistente / permanente Ansicht. Würde jemand eine andere mögliche Lösung oder Technologie empfehlen. Ich habe gehört, dass dokumentbasierte DBs wie CouchDB und MongoDB denormalisierte Daten effektiver verarbeiten können.


9
2018-01-25 01:22


Ursprung


Antworten:


Bevor Sie zu einer NoSQL-Lösung wechseln, sollten Sie zunächst eine indizierte Sicht ausprobieren:

http://msdn.microsoft.com/en-us/library/ms187864.aspx

und:

http://msdn.microsoft.com/en-us/library/ms191432.aspx

Wenn Sie eine indizierte Sicht verwenden, können Sie Ihre Basisdaten in ordnungsgemäß normalisierten Tabellen speichern und die Datenintegrität beibehalten, während Sie die denormalisierte "Ansicht" dieser Daten erhalten. Ich würde dies nicht für hochtransaktionale Tabellen empfehlen, aber Sie haben gesagt, dass es bei Lesevorgängen schwerer ist als bei Schreibvorgängen, sodass Sie vielleicht sehen wollen, ob dies für Sie funktioniert.

Basierend auf Ihren zwei Beispieltabellen ist eine Option:

1) Fügen Sie eine Spalte zur Benutzertabelle hinzu, die wie folgt definiert ist:

TaskCount INT NOT NULL DEFAULT (0)

2) Fügen Sie einen Trigger für die Task-Tabelle hinzu:

CREATE TRIGGER UpdateUserTaskCount
ON dbo.Task
AFTER INSERT, DELETE
AS

;WITH added AS
(
    SELECT  ins.UserID, COUNT(*) AS [NumTasks]
    FROM    INSERTED ins
    GROUP BY    ins.UserID
)
UPDATE  usr
SET     usr.TaskCount = (usr.TaskCount + added.NumTasks)
FROM    dbo.[User] usr
INNER JOIN  added
        ON  added.UserID = usr.UserID


;WITH removed AS
(
    SELECT  del.UserID, COUNT(*) AS [NumTasks]
    FROM    DELETED del
    GROUP BY    del.UserID
)
UPDATE  usr
SET     usr.TaskCount = (usr.TaskCount - removed.NumTasks)
FROM    dbo.[User] usr
INNER JOIN  removed
        ON  removed.UserID = usr.UserID
GO

3) Dann machen Sie eine Ansicht mit:

SELECT   u.UserID,
         u.Username,
         u.UserDisplayName,
         u.TaskCount,
         t.TaskID,
         t.TaskName
FROM     User u
INNER JOIN   Task t
        ON   t.UserID = u.UserID

Und folgen Sie dann den Empfehlungen aus den obigen Links (WITH SCHEMABINDING, Unique Clustered Index, etc.), um es "persistent" zu machen. Es ist zwar ineffizient, eine Aggregation in einer Unterabfrage in der SELECT-Anweisung durchzuführen, wie oben gezeigt, aber dieser spezielle Fall soll in einer Situation denormalisiert werden, die höhere Lesevorgänge als Schreibvorgänge aufweist. Wenn Sie also die indizierte Sicht verwenden, wird die gesamte Struktur einschließlich der Aggregation physisch gespeichert, so dass sie bei jedem Lesevorgang nicht neu berechnet werden.

Wenn jetzt ein LINKER JOIN benötigt wird, wenn einige Benutzer keine Aufgaben haben, funktioniert die indizierte Sicht aufgrund der 5000 Einschränkungen nicht. In diesem Fall können Sie eine reale Tabelle (UserTask) erstellen, bei der es sich um die denormalisierte Struktur handelt und die entweder über einen Trigger nur für die Benutzertabelle aufgefüllt wird (vorausgesetzt, Sie führen den Trigger I aus, der die Benutzertabelle basierend auf Änderungen in der Tabelle aktualisiert) Task-Tabelle) oder Sie können das TaskCount-Feld in der Benutzertabelle überspringen und nur Trigger in beiden Tabellen haben, um die UserTask-Tabelle aufzufüllen. Am Ende ist dies im Grunde, was eine indizierte Sicht nur tut, ohne dass Sie die Synchronisations-Trigger schreiben müssen.


10
2018-01-25 02:08